print plsql variable value from anonymous block to Java

Question

I am trying to print the plsql variable value (l_console_message) in Java. However, this approach doesn't seem to be working. I belive that something goes wrong at ResultSet bit. I am missing something here. Any idea, what goes wrong?

     PreparedStatement statement = null;
     try {
        statement = connection.prepareStatement("\n" +
            " declare " + "\n" +
            " p_schema_name varchar2(400):= upper('" + schema + "'); " + "\n" + 
            " p_temp_table_name varchar2(400) := upper('temp_table_jobs_name'); " + "\n" +
            " l_result varchar2(400); " + "\n" +
            " l_owner varchar2(200); " + "\n" +
            " l_job_name varchar2(200); " + "\n" +
            " l_enabled varchar2(200); " + "\n" +
            " l_console_message varchar2(4000); " + "\n" +
            " cursor c1_temp_table_name is " + "\n" +
                " select " + "\n" +
                    " table_name " + "\n" +  
                " from all_tables " + "\n" +
                " where table_name = p_temp_table_name; " + "\n" +
            " begin " + "\n" +
                " open c1_temp_table_name; " + "\n" +
                " fetch c1_temp_table_name into l_result; " + "\n" +
                " if c1_temp_table_name %notfound then " + "\n" +
                    " execute immediate ' " + "\n" +
                        " create table '||p_schema_name||'.'||p_temp_table_name||' ( " + "\n" +
                            " schema_name varchar2 (1000), " + "\n" +
                            " job_name varchar2(1000), " + "\n" +
                            " status varchar2(100) " + "\n" +
                        " )'; " + "\n" +
                    " l_console_message := p_temp_table_name||' created.'; " + "\n" +
                    " dbms_output.put_line (l_console_message); " + "\n" +
                " end if; " + "\n" +
                " close c1_temp_table_name; " + "\n" +
            " exception when others then " + "\n" +
                " null; " + "\n" +
            " end;");
    ResultSet rs = statement.execute(); 
    while (rs.next()){
        System.out.println(rs.getString(l_console_message));
    }       
} 
catch (SQLException e) {
    System.out.println("ERROR: Unable to run SQL statements for schema " + schema + " in beforeMigrate: " + e.getMessage());
} 
finally {
    if (null != statement) {
        try {
            statement.close();
        } 
        catch (SQLException se) {
            System.out.println("ERROR: Unable to close statement in beforeMigrate: " + se.getMessage());
        }
    }
}

Thanks in advance :-)

Answer 1

The question linked to from a comment shows an example of what you need to do, but you seem to be struggling to translate that to your situation.

Your anonymous block doesn't (and can't) return a result set, so it shouldn't be executed as a query, and shouldn't be a prepared statement; you need to have a callable statement instead:

 CallableStatement statement = null;
 try {
    statement = connection.prepareStatement("\n" +
 ...

Then you either need to assign the value of your PL/SQL variable to a bind variable placeholder:

 ...
                " l_console_message := p_temp_table_name||' created.'; " + "\n" +
                " ? := l_console_message; " + "\n" +
            " end if; " + "\n" +
 ...

or don't have l_console_message at all (so it doesn't even need to be declared), just assign the string directly to a bind variable placeholder:

 ...
                " ? := p_temp_table_name||' created.'; " + "\n" +
            " end if; " + "\n" +
 ...

Either way the dbms_output call isn't useful here. (It is actually possibly you retrieve the dbms_output buffer from Java, but it's a lot more work).

Then you need to declare an OUT bind variable to receive the string, and call the statement with execute() rather than executeQuery():

    statement.registerOutParameter(1, Types.VARCHAR);
    statement.execute();

Then you can retrieve the string value that has been put in to the bind variable; e.g. to print it straight to console:

    System.out.println(statement.getString(1));

The ResultSet, rs and loop have gone completely.

Answer 2

Your example has following issues:

1. Anonymous PL/SQL block can return nothing.
2. Variables declared in a PL/SQL block can't escape the scope. In your example l_console_message variable is visible only in the anonymous PL/SQL block, not in your Java code.

If you want to return a value (or a result set) from a PL/SQL code then you need a stand-alone or package subprogram. Or use the idea linked by @mario-tank where your host environment (your Java code) binds output variables.

Internet and StackOverflow have plenty of examples how to call PL/SQL code from Java.