Print stored procedure message directly to Java console

Question

I have a stored procedure where I am using

dbms_output.put_line(' output' || outVar);

I want to print same output directly in Java console (directly from stored proc) without using below code to get outVar :

callableStatement.registerOutParameter(1, String);
system.out.println(callableStatement.getString());

Answer 1

Change your procedure to a function as:

FUNCTION SOME_FUNCTION
RETURN VARCHAR2
IS
BEGIN
 -- Do some processing here....
 RETURN 'output:' || ....;
END;

Back in java land:

CallableStatement cs = connection.prepareCall("{? = call SOME_FUNCTION}");
cs.registerOutParameter(1, String);
cs.executeUpdate();
system.out.println(cs.getString(1));

There is no other way. You got ORACLE, you got the IDE console. There is no relationship between the two. To get data back from the database, you have to use a function to return a value, you could argue that returning values as out parameters is the same thing, and while both WILL get a value back, your code will be much more intuitive to others. It says, this call returns something.

That said, you can call the method in SQL developer and print as follows:

DECLARE
    result varchar2;
BEGIN
    result := SOME_FUNCTION;
    DBMS_OUTPUT.PUTLINE(result);
END;