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

Question

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.

Did you also go to the second page of results in Google? I don't believe you're truly desperate until you go there! — Möoz
– Möoz, Commented Apr 30, 2014 at 5:48

Boris Chlopkov · Accepted Answer · 2014-08-22 10:28:40Z

3

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.

answered Aug 22, 2014 at 10:28

Boris Chlopkov

764 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sebster Over a year ago

Thanks for the effort, I didn't know you can do that so +1 for that... but it doesn't help my case. Please reread my question(it's in BOLD).

sebster Over a year ago

The idea is that there are cases when you need to run a PL/SQL block that only declares IN parameters, then some other operations are made and later on you retreive the results(sometimes there's no need to retreive them at all). Sounds strange, I know, but from a DBAs perspective it's not(BTW, I'm not a DBA).

Boris Chlopkov Over a year ago

I see your problem now and yeah... I don't know how to make jdbc aware about mutating bind variable. It is generally a bad idea to write your callable statements like this so rewriting your plsql code is your best option. Or, less efficient way is creating multiple db calls: one is to find out what value your bind variable will get (300 in you example) and the other is to bind it to the where clause

sebster Over a year ago

Yeap, that's exactly what I ended up(actually, it was the DBA) doing.

krokodilko · Accepted Answer · 2013-10-28 22:19:43Z

2

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

answered Oct 28, 2013 at 22:19

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

3 Comments

sebster Over a year ago

I have read the Oracle docs and they don't help in my case. Your example is for 2 bound variables(IN and OUT). The example I gave uses a PL/SQL block with one IN bind variable that is used twice. Only in the case of OUT or INOUT we need to register parameters.

krokodilko Over a year ago

Please explain, mayby I missed something - why do you want to run SELECT query, but don't want to retrieve is't result ? It does not make sense for me - if I don't want to get a result of a query, I just ... don't run it at all.

sebster Over a year ago

It was just an example... it doesn't really matter if I retrieve the results or not. The main point is: How do I use JDBC placeholders in the case of multiple instances of the SAME bind variable in a PL/SQL block? Your solution with registering the parameter doesn't help with the question.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related