I have an Anonymous PL/SQL Block with bind variables that I want to run via JDBC.
PL/SQL block example:
variable v_value number declare v_return varchar2(30); begin :v_value:=300; select ename into v_return from emp where empno=:v_value; end;
The corresponding Java code would make use of the escape syntax with "?" to set the variables. So this block would look like this(correct me if I'm wrong):
String block = "declare v_return varchar2(30);" + "begin" + "? := 300;" + "select ename into v_return from emp where empno = ?;" + "end;"
Now, assuming that my variable is an INPUT parameter, I'll have to set the parameter like this:
// omitting the CallableStatement and conn declarations cs = conn.prepareCall(block); cs.setInt(parameterIndex, parameterValue);
The PROBLEM is that in my block I have two "?" used to replace the bound parameter :v_value. This means that when using the escape syntax only the 1'st "?" will be set. The 2'nd "?" will be left "hanging".
In such cases, when the same bind variable(s) is used multiple times in a PL/SQL block, how should I proceed with translating this in JDBC escape syntax?
I found this question on SO that is related to my problem. What I understand from it is that I'll have to REWRITE all Anonymous PL/SQL Blocks that make use of multiple bind variable instances in the same block. Is there ANY workaround for this? Or this is it… game over… it's the way JDBC works and I'll have to make due.
Looking forward for an answer… searched for this for 2 hours with no results.