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.

Answer 1

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();
    }

Answer 2

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();