Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to execute statement with duplicate parameters?

610683May 7 2013 — edited Oct 15 2014
Hello.

Is it possible to execute PL\SQL with contains two or more duplicate name parameters?

Example: begin select *:v* ,*:v* into :v1, :v2 from dual; end;

I tried to execute it using follow code:

String plsql = "begin select :v ,:v into :v1, :v2 from dual; end;";
OracleCallableStatement callableStatement = (OracleCallableStatement) connection_.prepareCall(plsql);
callableStatement.setInt(1, 1);
callableStatement.setInt(2, 2);
callableStatement.registerOutParameter(3, Types.INTEGER);
callableStatement.registerOutParameter(4, Types.INTEGER);
callableStatement.execute();

result of executing it, is error: java.sql.SQLException: ORA-01036: illegal variable name/number

If I register parameters by name:

String plsql = "begin select :v ,:v into :v1, :v2 from dual; end;";
OracleCallableStatement callableStatement = (OracleCallableStatement) connection_.prepareCall(plsql);
callableStatement.setIntAtName("v1", 0);
callableStatement.setIntAtName("v", 1);
callableStatement.setIntAtName("v2", 0);
callableStatement.execute();

, i have same error: java.sql.SQLException: ORA-01036: illegal variable name/number

I'm porting Delphi application to Java, and can not change PL\SQL code. In Delphi this pl\sql execute successfully.
This post has been answered by jschellSomeoneStoleMyAlias on May 8 2013
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 5 2013
Added on May 7 2013
8 comments
980 views