Passing parameter to callableSatatement - ORA-01008: not all variables bound

Question

I have a PL/SQL query with parameter:

plSqlQuery =    "declare "
              + "  num integer := 1000;"
              + "  myStr varchar2(100):= ?;"
              + "begin "
              + "  dbms_output.put_line('abc');"
              + "  dbms_output.put_line('hello');"
              + "  dbms_output.put_line(myStr);"
              + "end;"

My Java method is something like this:

public static void getData(String sqlQuery) throws SQLException, IOException{
    Statement s =conn.createStatement();
    try{
    s.executeUpdate("begin dbms_output.enable();end;);
    s.executeUpdate(sqlQuery);
    
    try{
    CallableStatement call = conn.prepareCall("declare num integer = 10000; begin dbms_output.get_lines(?, num); end;)
    }
    call.registerOutParameter(1,Types.ARRAY, "DBMSOUTPUT_LINESARRAY");
    call.execute();
    
     Array array = null;
                    try {
                        array = call.getArray(1);
                        System.out.println(Arrays.asList((Object[]) 
                 array.getArray()));
                    }
                    finally {
                        if (array != null)
                            array.free();
                    }

Having both above now, I would like to execute my getData method, but I don't know how to pass a parameter to it (myStr).

Can you please tell my where in my Java method I should set my string parameter?

Should it be something like

s.setString(x, "abcdefg");

or

call.setString(2, "abcdefg");

it gives me an Oracle error like

ORA-01008: not all variables bound

I tried both to be honest but did not succeed.

Hi @kofemann, I added call.setString(2, "abcdefg"); call.executeQuery(): but I'm getting an error like "ORA-01008: not all variables bound" — EdXX, Jul 04 '21 at 08:09
Refer to https://stackoverflow.com/questions/47830370/getting-output-from-dbms-output-get-lines-using-jdbc Maybe you should also refer to https://docs.oracle.com/javase/tutorial/jdbc/index.html — Abra, Jul 04 '21 at 08:29
I did, but there is no parameter which is passed to the oracle pl/sql. Without param it works fine, I agree — EdXX, Jul 04 '21 at 08:43
I have written code that does what (I believe) you want and it runs without error. However I can't think of a use-case where you would run PL/SQL code from JDBC that simply writes to standard output. Is that really all you want to do? — Abra, Jul 04 '21 at 09:11

score 0 · Answer 1 · answered Jul 04 '21 at 10:59

If you are passing your string plSqlQuery as the parameter to your getData() method (e.g. by calling getData(plSqlQuery)) then you will run into an ORA-01008 'not all variables bound' error, because you're not specifying a value for the ? placeholder.

You can't use an ordinary Statement with placeholders, you must use a PreparedStatement instead.

Try replacing the line

    s.executeUpdate(sqlQuery);

with

    try (PreparedStatement pstmt = conn.prepareStatement(sqlQuery)) {
        pstmt.setString(1, "Some Value Here");
        pstmt.execute();
    }

score 0 · Answer 2 · answered Jul 04 '21 at 18:34

I figured it out. It works with 3 different statements :(

Statement s = conn.createStatement();
s.executeUpdate("begin dbms_output.enable();end;);

PreparedStatement ps = conn.prepareStatement(sqlQuery);
ps.setString(1, "abcdefg");

 CallableStatement call = conn.prepareCall("declare num integer = 10000; begin dbms_output.get_lines(?, num); end;)
 call.registerOutParameter(1,Types.ARRAY, "DBMSOUTPUT_LINESARRAY");
 call.execute();

Passing parameter to callableSatatement - ORA-01008: not all variables bound

2 Answers2