CODEWITHSUNDEEP
javaoracle-databasejdbcplsql
sebster
sebster

Reputation: 1320

Writing an anonymous PL/SQL block with bind variables using JDBC

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?

EDIT:

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.

Upvotes: 1

Views: 8084

Answers (2)

Boris Chlopkov
Boris Chlopkov

Reputation: 76

Take a look at this doc.

Basicaly, you can bind the same variable as Input and Output like this:

CallableStatement call = conn.prepareCall(
    "{CALL doubleMyInt(?)}");
// for inout parameters, it is good practice to
// register the outparameter before setting the input value
call.registerOutParameter(1, Types.INTEGER);
call.setInt(1,10);

I hope it helps.

Upvotes: 3

krokodilko
krokodilko

Reputation: 36127

Try this query:

String block = "declare " +
               "   v_return varchar2(30);" +
               "begin" + 
               "   select ename into v_return from emp where empno = ?;" +
               "   ? := v_return; " +
               "end;";
CallableStatement cs = connection.prepareCall( block );
cs.setInt( 1, v_value );
cs.registerOutParameter( 2, java.SQL.VARCHAR );
cs.executeUpdate();
String result = cs.getString( 2 );

See below links for more details and examples:
http://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#JJDBC28075
http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraint.htm#JJDBC28168

Upvotes: 2

Related Questions