This discussion is archived
6 Replies Latest reply: Mar 26, 2013 3:09 AM by 450248 RSS

Why named parameter can't be used multiple times in PL/SQL block in JDBC

450248 Newbie
Currently Being Moderated
with the following PL/SQL block, when I run int in JDBC, I get an error,
it says, The number of parameter names does not match the number of registered parameters.
if all named parameters are used only once, then my program works fine.
My old program uses Oracle Forms to run the attached PL/SQL block correctly, I just want to run them in JDBC without more efforts, I don't want to rewrite all PL/SQL blocks.

Does oracle driver support this case? why the PL/SQL block can work in Oracle Forms but failed in JDBC?
Can we have an another solutions to avoid rewriting the PL/SQL block to stored procedure?

if I use following SQL:
BEGIN
     if :q is null then 
          :q := 'X';
     else
          :q := 'Y';
     end if;
END;
, Using java program:
import java.sql.*;

public class RunPLSQLBlock {
     public static void main(String s[]) throws SQLException {
          String URL = "jdbc:oracle:thin:@192.168.11.199:1521:TIBSTEST";
          Connection con = null;
          try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               con = (Connection) DriverManager.getConnection(URL, "FBP1DEV", "FBP1DEV");

               String SQL = "BEGIN  if :q is null then  :q := 'X'; else :q := 'Y'; end if; END;";

               CallableStatement stmt = con.prepareCall(SQL);
               stmt.registerOutParameter("q", Types.VARCHAR);
               stmt.setString("q", "A");
               stmt.execute();

          } catch (Exception e) {
               e.printStackTrace();
          } finally {
               if (con != null) {
                    con.close();
               }
          }
     }
}
in the coding, only "q" registered, I got:
java.sql.SQLException: The number of parameter names does not match the number of registered praremeters
     at oracle.jdbc.driver.OracleSql.setNamedParameters(OracleSql.java:314)
     at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:10096)
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:5693)
     at RunPLSQLBlock.main(RunPLSQLBlock.java:28)
now, tried to register 3 indexes, changed fragments are below.
import java.sql.*;

public class RunPLSQLBlock {
     public static void main(String s[]) throws SQLException {
          String URL = "jdbc:oracle:thin:@192.168.11.199:1521:TIBSTEST";
          Connection con = null;
          try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               con = (Connection) DriverManager.getConnection(URL, "FBP1DEV", "FBP1DEV");

               String SQL = "BEGIN  if :q is null then  :q := 'X'; else :q := 'Y'; end if; END;";

               CallableStatement stmt = con.prepareCall(SQL);
               stmt.registerOutParameter(1, Types.VARCHAR);
               stmt.registerOutParameter(2, Types.VARCHAR);
               stmt.registerOutParameter(3, Types.VARCHAR);
               stmt.setString(1, "A");
               stmt.execute();

          } catch (Exception e) {
               e.printStackTrace();
          } finally {
               if (con != null) {
                    con.close();
               }
          }
     }
}
now error changed to:
java.sql.SQLException: ORA-01006: bind variable does not exist

     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)
     at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:926)
     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:200)
     at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:543)
     at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:208)
     at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1416)
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1757)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4372)
     at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4595)
     at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:10100)
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:5693)
     at RunPLSQLBlock.main(RunPLSQLBlock.java:26)
, now tried register only 1 position like below,
  CallableStatement stmt = con.prepareCall(SQL);
  stmt.registerOutParameter(1, Types.VARCHAR);
  stmt.setString(1, "A");
  stmt.execute();
, it says:
java.sql.SQLException: Missing IN or OUT parameter at index:: 2
     at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2177)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4356)
     at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4595)
     at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:10100)
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:5693)
     at RunPLSQLBlock.main(RunPLSQLBlock.java:26)
, now let try a OK case, which use all named parameters only once. coding like below, SQL and Java listed below.
BEGIN
     if :q is null then 
          :r := 'X';
     else
          :s := 'Y';
     end if;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
END;
import java.sql.*;

public class RunPLSQLBlock {
     public static void main(String s[]) throws SQLException {
          String URL = "jdbc:oracle:thin:@192.168.11.199:1521:TIBSTEST";
          Connection con = null;
          try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               con = (Connection) DriverManager.getConnection(URL, "FBP1DEV", "FBP1DEV");

               String SQL = "BEGIN  if :q is null then  :r := 'X'; else :s := 'Y'; end if; END;";

               CallableStatement stmt = con.prepareCall(SQL);
               stmt.registerOutParameter("q", Types.VARCHAR);
               stmt.registerOutParameter("r", Types.VARCHAR);
               stmt.registerOutParameter("s", Types.VARCHAR);
               stmt.setString("q", "A");
               stmt.execute();
               System.out.println("Q :" + stmt.getString("q"));
               System.out.println("R :" + stmt.getString("r"));
               System.out.println("S :" + stmt.getString("s"));

          } catch (Exception e) {
               e.printStackTrace();
          } finally {
               if (con != null) {
                    con.close();
               }
          }
     }
}
, the case give us the following output:
Q :A
R :null
S :Y
2nd part, I also tried another scheme, to use 'execute immediate', test code attached below, it also have errors.
begin
     execute immediate 'begin if :q is null then :q := ''X''; else :q := ''Y''; :r := ''Z''; end if; end;' 
     using in out :q, out :r;
end;
, Java Code:
import java.sql.*;
public class RunDynamicSQL {
     public static void main(String s[]) throws SQLException {
          String URL = "jdbc:oracle:thin:@192.168.11.199:1521:TIBSTEST";
          Connection con = null;
          try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               con = (Connection) DriverManager.getConnection(URL, "FBP1DEV", "FBP1DEV");
               String SQL ="begin execute immediate 'begin if :q is null then :q := ''X''; else :q := ''Y''; :r := ''Z''; end if; end;' using in out :q, out :r; end;";
               CallableStatement stmt = con.prepareCall(SQL);
               stmt.registerOutParameter("q", Types.VARCHAR);
               stmt.registerOutParameter("r", Types.VARCHAR);
               stmt.setString("q", "A");
               stmt.execute();
               System.out.println("Q :" + stmt.getString("q"));
               System.out.println("R :" + stmt.getString("r"));
          } catch (Exception e) {
               e.printStackTrace();
          } finally {
               if (con != null) {
                    con.close();
               }
          }
     }
}
, the output is, we can find when parameter 'q' is IN OUT mode, we can't get its final value:
Q :null
R :Z
, now I tried my workaround, it works fine by using a temporary variable, now my named parameter is split to 2 roles, one is for IN, another is for OUT, now I can get final out value.
declare     
     q clob;
     r clob;
begin
     q := ?;
     r := ?;
     execute immediate 'begin if :q is null then :q := ''X''; else :q := ''Y''; :r := ''Z''; end if; end;' using in out q, out r;
     ? := q;
     ? := r;
end;
, my test java code,
import java.sql.*;
public class RunDynamicSQL {
     public static void main(String s[]) throws SQLException {
          String URL = "jdbc:oracle:thin:@192.168.11.199:1521:TIBSTEST";
          Connection con = null;
          try {
               Class.forName("oracle.jdbc.driver.OracleDriver");
               con = (Connection) DriverManager.getConnection(URL, "FBP1DEV", "FBP1DEV");
               String SQL ="declare q clob;r clob; begin q := ?; r := ?; execute immediate 'begin if :q is null then :q := ''X''; else :q := ''Y''; :r := ''Z''; end if; end;' using in out q, out r; ? := q; ? := r; end;";
               CallableStatement stmt = con.prepareCall(SQL);
               stmt.registerOutParameter(3, Types.VARCHAR);
               stmt.registerOutParameter(4, Types.VARCHAR);
               stmt.setString(1, "A");
               stmt.setString(2, "A");
               stmt.execute();
               System.out.println("Q :" + stmt.getString(3));
               System.out.println("R :" + stmt.getString(4));
          } catch (Exception e) {
               e.printStackTrace();
          } finally {
               if (con != null) {
                    con.close();
               }
          }
     }
}
, the output is expected,
Q :Y
R :Z
Database:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

JDBC Driver, extracted from ojdbc6_g.jar/META-INF/MANIFEST.MF :
Created-By: 1.5.0_30-b03 (Sun Microsystems Inc.)
Implementation-Vendor: Oracle Corporation
Implementation-Title: JDBC debug
Implementation-Version: 11.2.0.3.0
Repository-Id: JAVAVM_11.2.0.3.0_LINUX_110823
Specification-Vendor: Sun Microsystems Inc.
Specification-Title: JDBC
Specification-Version: 4.0
Main-Class: oracle.jdbc.OracleDriver

JDK:
java version "1.7.0"
Java(TM) SE Runtime Environment (build 1.7.0-b147)
Java HotSpot(TM) Client VM (build 21.0-b17, mixed mode, sharing)

Edited by: jamxval on 2013-3-22 2:01PM (UTC+08:00), Give full test java program and SQL, added environment/API level; Attached another problem.

Edited by: jamxval on 2013-3-26 17:57 (UTC +08), Adjust code style
  • 1. Re: Why named parameter can't be used multiple times in PL/SQL block in JDBC
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    For JDBC issues you need to provide:

    1. The database and full version you are using
    2. The JDK full version you are using
    3. The OJDBC jar file name and version you are using.
    >
    with the following PL/SQL block, when I run int in JDBC, I get an error,
    it says, The number of parameter names does not match the number of registered parameters.
    if all named parameters are used only once, then my program works fine.
    >
    When you post don't TELL us what the error says: post the EXACT ERROR message and stacktrace.

    You could NOT have run what you posted in Java since all you posted is some mix of Java statements and PL/SQL statements. That can't be executed to try to reproduce your issue.

    Please edit your post to include the actual Java code you are trying to execute as well as the information ask for above.

    If you have sample code that supports your statement 'if all named parameters are used only once, then my program works fine' post that also.
  • 2. Re: Why named parameter can't be used multiple times in PL/SQL block in JDBC
    450248 Newbie
    Currently Being Moderated
    Hi,
    thanks for you hints,
    now I updated the post and attached source code, SQL fragment and database version,JDK version and Oracle Driver version. After googled, I tried "execute immediate", it can export my named parameters to 'use only once', but when the parameter is IN OUT mode, I can't get its final value, need some patch yet.

    My final target is to migrate all Oracle Forms 6i 's screens to Java, and we expect PL/SQL block in all Oracle Forms triggers are not required to rewrite to stored procedures, so program required to translate the PL/SQL block to JDBC style. Oracle Forms can run the PL/SQL block directly (excludes some UI related functions), so if Oracle Forms run the PL/SQL block on Oracle DB side (I'm not sure), then I suspect the JDBC driver layer (bug or unsupported feature in plan/specification).
  • 3. Re: Why named parameter can't be used multiple times in PL/SQL block in JDBC
    Tolls Journeyer
    Currently Being Moderated
    You can't bind by named placeholders using the basic CallableStatement.
    The 'parameterName' parameter for the setXXX method you are using is the name of a parameter (eg the name in the procedure signature) and not the placeholder name (eg ':q' in your first example).

    Oracle's implementation of CallableStatement, OracleCallableStatement, has a setXXXAtName method which allows you to bind using the placeholder name.
  • 4. Re: Why named parameter can't be used multiple times in PL/SQL block in JDBC
    450248 Newbie
    Currently Being Moderated
    Hi, thanks for your response, now I see, the named parameter is for stored procedure only, for PL/SQL block we name it placeholder name.
    After cast my java.sql.CallableStatement to oracle.jdbc.OracleCallableStatement, I can find setStringAtName,
    now, I have only one question:I can't find corresponding methods for registerOutputParameter, how we fetch output value?
    I tried to callableStatement.getString("q"); it reports errors, but there are no ordinal binding in my source code, does placeholder names doesn't support OUT mode?

    Java:
    CallableStatement stmt = con.prepareCall("BEGIN  if :q is null then  :r := 'X'; else :s := 'Y'; end if; END;");
    oracle.jdbc.OracleCallableStatement call = (oracle.jdbc.OracleCallableStatement) stmt;
    call.registerOutParameter("q", Types.VARCHAR);
    call.registerOutParameter("r", Types.VARCHAR);
    call.registerOutParameter("s", Types.VARCHAR);
    call.setStringAtName("q", "A");
    call.setStringAtName("r", "A");
    call.setStringAtName("s", "A");
    call.execute();
    System.out.println("Q :" + call.getString("q"));
    </Java>
    
    <output>
    java.sql.SQLException: 不允许的操作: Ordinal binding and Named binding cannot be combined!
         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
         at oracle.jdbc.driver.OracleCallableStatement.getString(OracleCallableStatement.java:2834)
         at RunPLSQLBlock.main(RunPLSQLBlock.java:33)
    </output>
    by the way, in my below-mentioned SQL 'problematic', when my code uses 'execute immediate' and use placeholder names in IN OUT mode, we always get NULL value (i.e. ':q'), but we can get final value of ':r' when ':r' is OUT mode only; now I get a workaround attached in below-mentioned 'my workaround' block, which split the IN OUT roles to 2 parts, it can work now;
    It seems that the difference between 'problematic' and 'my workaround' imply that there are something work unexpectedly when the driver process the placeholder names, because 'my workaround' and ':r in problematic case' make sure the 'execute immediate' returned output values correctly, unluckly driver layer can't get return values.
    <SQL name = 'problematic'>
    begin
         execute immediate 'begin if :q is null then :q := ''X''; else :q := ''Y''; :r := ''Z''; end if; end;' 
         using in out :q, out :r;
    end;
    </SQL>
    
    <SQL name='my workaround'>
    declare     
         q clob;
         r clob;
    begin
         q := ?;
         r := ?;
         execute immediate 'begin if :q is null then :q := ''X''; else :q := ''Y''; :r := ''Z''; end if; end;' using in out q, out r;
         ? := q;
         ? := r;
    end;
    Edited by: EJP on 26/03/2013 14:14
  • 5. Re: Why named parameter can't be used multiple times in PL/SQL block in JDBC
    rp0428 Guru
    Currently Being Moderated
    Parameters (e.g. OUT parameters) are a property of functions and procedures. Your 'placeholders' are substitution variables and provide a value TO the code much like an IN parameter would. There is no OUT equivalent.
  • 6. Re: Why named parameter can't be used multiple times in PL/SQL block in JDBC
    450248 Newbie
    Currently Being Moderated
    OK, Now I see both the JDBC named parameters and placeholder names are not designed to cover my case, therefore I keep using my workaround first, it can work for my program to run raw PL/SQL block, then I don't need to understand what logic may program do, just quote them with some generated prefix and suffix, this will be automatically formatted:
    declare     
         q clob;
         r clob;
    begin
         q := ?;
         r := ?;
         execute immediate 'begin if :q is null then :q := ''X''; else :q := ''Y''; :r := ''Z''; end if; end;' using in out q, out r;
         ? := q;
         ? := r;
    end;
    , java
    CallableStatement stmt = con.prepareCall(SQL);
    stmt.registerOutParameter(3, Types.VARCHAR);
    stmt.registerOutParameter(4, Types.VARCHAR);
    stmt.setString(1, "A");
    stmt.setString(2, "A");
    stmt.execute();
    System.out.println("Q :" + stmt.getString(3));
    System.out.println("R :" + stmt.getString(4));
    , output is expected:
    Q :Y
    R :Z

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points