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!

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

Wesley D-Oracle
Answer

Oracle 12cR1 to 19c is a major release jump, as such it would probably be wise to perform full regression testing.  However, it's your database and you can choose whatever level of testing and/or validations you deem fit.

Marked as Answer by User516490-OC · Sep 27 2020

Hi,

I agree with Wesley on the testing part. With regards to testing performance and ensuring performance stability I encourage you to look at the slides from a presentation my fellow Product Manager, Mike Dietrich gave a little while ago:

https://mikedietrichde.com/slides/

Search for:

Seminar 3 – Performance Stability, Tips and Tricks and Underscores

Regards,

Daniel

1 - 2
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
999 views