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 :-)

thanks. That example have refrence cursor. Now, in my case. I don't have it at all. I did try to do some changes, and I start to get "Invalid column index" error. — Jaanna
– Jaanna, Commented Apr 15, 2016 at 10:38
That example does have a reference cursor, but it also has a string, so it has two OUT bind variables, as well as an IN bind variable. You only need a single OUT variable, so your register/get index will only be 1. — Alex Poole
– Alex Poole, Commented Apr 15, 2016 at 12:02

Alex Poole · Accepted Answer · 2016-04-15 12:00:46Z

2

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.

answered Apr 15, 2016 at 12:00

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jaanna Over a year ago

Thank you Alex. The variable binding in Java is new thing for me. Now how can I execute in a loop. Would I need to do statement.registerOutParameter(1, Types.CURSOR); ?

Alex Poole Over a year ago

Execute what in a loop? You want to execute the same block multiple times, with different schema values? Each execution would still get a single string back.

Community · Accepted Answer · 2017-05-23 10:33:55Z

0

Your example has following issues:

Anonymous PL/SQL block can return nothing.
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.

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Apr 15, 2016 at 11:29

user272735

10.6k9 gold badges72 silver badges100 bronze badges

Collectives™ on Stack Overflow

print plsql variable value from anonymous block to Java

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related