How to execute statement with duplicate parameters?

610683

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.

jschellSomeoneStoleMyAlias

Darrin2 wrote:
We thought about this workaround, but hoping that exists a better way. Replace the name with a question mark add limitation on PL\SQL
begin
:v := :v + 1;
:v := :v + 1;
end;

If it helps any...if you have blocks like that then you CANNOT use jdbc parameter substitution. Regardless of how you do it. Jdbc only works with data - not schema.

So if you need to solve the above then you would need to
1. Create your own parser that processes the blocks. It must do schema replacement. It could also do data replacement.
2. Use the output of that as input to JDBC.

The complexity of 1 depends on what the blocks might be. But you might be looking at a full SQL parser. You can google for existing ones (they exist for java.)

jschellSomeoneStoleMyAlias

And what happens when you try the following two statements (with appropriate adjustments to java code)


String plsql = "begin select :v into :v1 from dual; end;";
String plsql = "begin select 1 into :v1 from dual; end;";

Unknown

>
Is it possible to execute PL\SQL with contains two or more duplicate name parameters?
. . .
I'm porting Delphi application to Java, and can not change PL\SQL code. In Delphi this pl\sql execute successfully.
>
Please explain why you can't change the Java string for the PL/SQL call

String plsql = "begin select :v ,:v into :v1, :v2 from dual; end;";

There must be something you aren't telling us because that line is JAVA code and if you are doing the port you could easily change that string to this and it would work:

String plsql = "begin select :v ,:v1 into :v1, :v2 from dual; end;";

It doesn't make sense to have two parameters with the same name.

610683

jschell wrote:
And what happens when you try the following two statements (with appropriate adjustments to java code)

String plsql = "begin select :v into :v1 from dual; end;";
String plsql = "begin select 1 into :v1 from dual; end;";

In both cases executing successfull.
1. :v1 will have value equals :v
2. :v1 will have value 1

610683

rp0428 wrote:
Please explain why you can't change the Java string for the PL/SQL call

My team developing framework for building ERP application. We have many working applications witch written on PL\SQL (Business logic in Oracle types/packages) + Delphi (Thin descktop client, whitch loading meta-data from DataBase). Now we are developing web-based java-application witch use same meta-data. Meta-data contains many PL\SQL block's. These PL\SQL block was written by hundreds of business logic developers, and our Java-frаmework must execute it. We can't change this block's because we have not access to them before executing.

rp0428 wrote:
There must be something you aren't telling us because that line is JAVA code and if you are doing the port you could easily change that string to this and it would work:

String plsql = "begin select :v ,:v1 into :v1, :v2 from dual; end;";

It doesn't make sense to have two parameters with the same name.

String plsql = "begin select :v ,:v1 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

PL\SQL code sample witch I show you, is just an example. It's illilustrate exception witch occure when executing PL\SQL code with two or more parameters with same name.
If I excute plain SQL whitch have two or more parameters with same name, all works correct

String plsql = "select :v as v1 ,:v as v2 from dual";
OracleCallableStatement callableStatement = (OracleCallableStatement) connection_.prepareCall(plsql);
callableStatement.setInt(1, 1);
callableStatement.setInt(2, 2);
ResultSet resultSet = callableStatement.executeQuery();

Fields v1 and v2 will have values 1 and 2 respectively.


Oracle Client 11.2.0.1 or 11.2.0.3

Unknown

>
Now we are developing web-based java-application witch use same meta-data. Meta-data contains many PL\SQL block's. These PL\SQL block was written by hundreds of business logic developers, and our Java-frаmework must execute it. We can't change this block's because we have not access to them before executing.
>
Then you will NOT be able to use JDBC to execute those blocks.

Binding by name is NOT supported; you need to bind the old-fashioned way. This works:

        String plsql = "begin select ? ,? into ?, ? from dual; end;";

See 'Binding Named Parameters' in the JDBC Dev Guide
http://docs.oracle.com/cd/B28359_01/java.111/b31224/apxref.htm#BABEJDBH
>
Binding Named Parameters

Binding by name is not supported when using the setXXX methods. Under certain circumstances, previous versions of Oracle JDBC drivers have allowed binding statement variables by name when using the setXXX methods. In the following statement, the named variable EmpId would be bound to the integer 314159.

PreparedStatement p = conn.prepareStatement
("SELECT name FROM emp WHERE id = :EmpId");
p.setInt(1, 314159);

This capability to bind by name using the setXXX methods is not part of the JDBC specification, and Oracle does not support it. The JDBC drivers can throw a SQLException or produce unexpected results. Starting from Oracle Database 10g JDBC drivers, bind by name is supported using the setXXXAtName methods.
>
You may be using one of those 'previous versions' of the driver that appeared to support binding by name but such bindings are NOT supported so you should not try to use them.

There are no workarounds. You will either need to modify those blocks at the source or will need to find the named bind variables (':abcde') and replace the name with a question mark.

610683

rp0428 wrote:
There are no workarounds. You will either need to modify those blocks at the source or will need to find the named bind variables (':abcde') and replace the name with a question mark.

We thought about this workaround, but hoping that exists a better way. Replace the name with a question mark add limitation on PL\SQL

begin
   :v := :v + 1;
   :v := :v + 1;
end;

is not same

begin
   ? := ? + 1;
   ? := ? + 1;
end;

Thank you, rp0428.

Unknown

>
We thought about this workaround, but hoping that exists a better way. Replace the name with a question mark add limitation on PL\SQL

begin
:v := :v + 1;
:v := :v + 1;
end;
>
Those are not SQL statements and are not the same as the examples you have been posting.

This is a sql statement in a block

String plsql = "begin select ? ,? into ?, ? from dual; end;";

Now you know why we can't help you if you don't actually post the code that you are using.

Regardless neither JDBC nor Oracle support the use of named bind variables so not much else to say.

jschellSomeoneStoleMyAlias

Darrin2 wrote:
We thought about this workaround, but hoping that exists a better way. Replace the name with a question mark add limitation on PL\SQL
begin
:v := :v + 1;
:v := :v + 1;
end;

If it helps any...if you have blocks like that then you CANNOT use jdbc parameter substitution. Regardless of how you do it. Jdbc only works with data - not schema.

So if you need to solve the above then you would need to
1. Create your own parser that processes the blocks. It must do schema replacement. It could also do data replacement.
2. Use the output of that as input to JDBC.

The complexity of 1 depends on what the blocks might be. But you might be looking at a full SQL parser. You can google for existing ones (they exist for java.)