Sunday 21 July 2013

Double bind reference now work

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: