Have Pl/Sql Outputs in Real Time

Have PL/SQL Outputs in Real Time

I don't know if this is exactly what you want but I use dbms_application_info.set_module to see where my package is.

dbms_application_info.set_module(module_name => 'Conversion job',
                                 action_name => 'updating table_x'); 

A query on v$session will show you which part of the procedure is running.

Is there any way to flush output from PL/SQL in Oracle?

Not really. The way DBMS_OUTPUT works is this: Your PL/SQL block executes on the database server with no interaction with the client. So when you call PUT_LINE, it is just putting that text into a buffer in memory on the server. When your PL/SQL block completes, control is returned to the client (I'm assuming SQLPlus in this case); at that point the client gets the text out of the buffer by calling GET_LINE, and displays it.

So the only way you can make the output appear in the log file more frequently is to break up a large PL/SQL block into multiple smaller blocks, so control is returned to the client more often. This may not be practical depending on what your code is doing.

Other alternatives are to use UTL_FILE to write to a text file, which can be flushed whenever you like, or use an autonomous-transaction procedure to insert debug statements into a database table and commit after each one.

Oracle PL/SQL - tips for immediate output / console printing

You can have a procedure that writes messages to a table using an autonomous transaction something like:

procedure log (p_message)
is
    pragma autonomous_transaction;
begin
    insert into message_log (user, datetime, message)
    values (user, sysdate, p_message);
    commit;
end;

Then monitor the table from another Oracle session.

Output the result of a SELECT executed using dynamic PL/SQL

When you run the anonymous PL/SQL block in your question the block compiles and executes OK, but the dynamic query isn't actually being executed:

If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes.

If you were happy to just see the results in the script output window you could use the variable and print SQL Developer client commands and have your block open a ref cursor as a bind variable:

var rc refcursor

declare 
    MyStatement varchar(300) := 'SELECT * FROM MYTABLE';
begin
    open :rc for MyStatement;
end;
/

print rc

That bound cursor approach can be used from other clients, e.g. from a Java application using JDBC. And 12c adds a mechanism to simplify that a bit, but only from a stored procedure - not an anonymous block. You would still only see the results in the script output window though (assuming your version of SQL Developer supports this mechanism at all).

I don't think there's any way to get the result from a dynamic query run in an anonymous block into the SQL Developer query results windows, as SQL Developer isn't running the query - it's happening inside a PL/SQL context on the server.

You could potentially use a function instead, which returns a pipelined collection, and then query that function - but the collection type would have to be known in advance, which restricts how dynamic the query can really be as the column names and data types in the select list would have to match an object type.

It's hard to tell what to advise as your example is something that doesn't need to be dynamic. A real-world scenario might suggest other approaches. It still ins't quite the query result window you wanted, but if you did have a function like;

drop function myfunc;

create function myfunc return sys_refcursor
as
    MyStatement varchar(300) := 'SELECT * FROM MYTABLE';
    MyRefCursor sys_refcursor;
begin
    open MyRefCursor for MyStatement;
    return MyRefCursor;
end;
/

then you could open the function from the object browser (under the connection, in the pane on the left). From there you can click the green triange (or hit control-F10) to run the function. That gives you a window with a pre-populated anonymous block to call the function:

When you click OK the dynamic query results will end up in the 'output variables' section (using a query against the employees table in this case):

That output isn't as flexible as the query result window though you can't export it or sort it, for instance. But it is in a nice grid...

How to show output of dbms_output.put_line used in procedure in sqlplus?

The output is captured in your spoolfile, temp.lst. It's not displayed to the terminal due to

set termout off

In my test, temp.lst contained:

10:56:15 SQL> SET SERVEROUTPUT ON;
10:56:15 SQL>   BEGIN
10:56:15   2      CUSTOM_PKG.get_transaction_details(10);
10:56:15   3    END;
10:56:15   4  /
SESSION DETAILS :
----------------

PL/SQL procedure successfully completed.

10:56:15 SQL> SET ECHO OFF;

There are no actual results because all ids are 100 and you are passing 10.

How to display output from stored procedure?

There are a couple of solutions.

One is to write to a log of some description. You can use a file (writing out with UTL_FILE) or a table (using autonomous transactions so that the records are visible in another session without affecting the main transaction).

The other solution is to write to the V$SESSION_LONGOPS view using DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS. Find out more

I think logging is always a good idea with long-running background procedures. If something goes wrong your logs are the only source of info you have.

---

Related Topics