Double bind now works see below:
begin
:v_status := 1234;
raise_application_error(-20000, 'account past due.');
exception when others then
:v_status := -1;
end;
/
The double bind variable :v_status confused SQLDeveloper and JDBC works now.
Note this fancy rewrite only happens when necessary - so issues with this approach only affect double bind code.
The internal details are:
The workaround (internal Oracle Developer) applied to apex has now
been automated within sqldeveloper. If (SQLDeveloper user) uses multiple binds
sqldeveloper will rewrite:
begin
:v_status := 1234;
raise_application_error(-20000, 'account past due.');
exception when others then
:v_status := -1;
end;
/
to:
1/setup:
2/rewritten plsql:
3/get output variables:
1/setup:
in java :v_statusinit = java bind v_status
2/rewritten plsql:
declare
myvstatus number:=:v_statusinit;
begin
begin
myvstatus := 1234;
raise_application_error(-20000, 'account past due.');
exception when others then
myvstatus := -1;
end;
:v_status := myvstatus;
end;
/
Note - additional code is actually written with no newlines, so debug/error line numbers are not affected.
3/get output variables:
in java java bind v_status = :v_status
The only nasty side effect is if you put a varchar2(20) into a
varchar2(10) bind it will error out (rather than truncate) at
the "myvstatus number:=:v_statusinitmy;" stage. Could possibly code for this - but would need to handle for example varchar2(20 byte) and varchar2(20 chat) code paths for the rewritten PL/SQL.
No comments:
Post a Comment