Forum Stats

  • 3,827,381 Users
  • 2,260,767 Discussions
  • 7,897,222 Comments

Discussions

Categories

How to execute statement with duplicate parameters?

610683
610683 Member Posts: 6
edited Oct 15, 2014 5:34PM in Database Connectivity Discussions
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.
Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • jschellSomeoneStoleMyAlias
    jschellSomeoneStoleMyAlias Member Posts: 24,877 Gold Badge
    Answer ✓
    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.)
    · Share on TwitterShare on Facebook

Answers

  • jschellSomeoneStoleMyAlias
    jschellSomeoneStoleMyAlias Member Posts: 24,877 Gold Badge
    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;";
    · Share on TwitterShare on Facebook
  • Unknown
    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.
    · Share on TwitterShare on Facebook
  • 610683
    610683 Member Posts: 6
    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
    · Share on TwitterShare on Facebook
  • 610683
    610683 Member Posts: 6
    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
    · Share on TwitterShare on Facebook
  • Unknown
    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.
    · Share on TwitterShare on Facebook
  • 610683
    610683 Member Posts: 6
    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.
    · Share on TwitterShare on Facebook
  • Unknown
    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.
    · Share on TwitterShare on Facebook
  • jschellSomeoneStoleMyAlias
    jschellSomeoneStoleMyAlias Member Posts: 24,877 Gold Badge
    Answer ✓
    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.)
    · Share on TwitterShare on Facebook
This discussion has been closed.