Skip to Main Content
  • Questions
  • DBMS_OUTPUT.PUT_LINE prints only after the stored procedure competes.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Tauseef.

Asked: February 04, 2016 - 7:28 am UTC

Last updated: February 04, 2016 - 5:49 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

I have a stored procedure that collects information for around 4-5 million records( on a monthly basis).
The way I have used is as below.

create or replace procedure monthly_report(report_Date date) is
v_start_date date;
v_end_date date;
C_LIMIT PLS_INTEGER := 100000;
type monthly_record is RECORD
(
--declarations here for the columns.
);
type tab_month_rec is table of monthly_record;
v_tab_month_rec tab_month_rec;
i number;
INDX number;
cursor example_cursor(start_date date, end_date date) is
select c1,c2,c3 from
t1,t2,t3,t4
where
date_of_record> start_date and date_of_record < end_date
--and all the join conditions here
;

begin
dbms_output.put_line('Monthly Report Execution starts');
-- logic for calculating start_date and end_date
i := 0;
open example_cursor(v_start_date,v_end_Date);
LOOP
i:=i+1;
DBMS_OUTPUT.PUT_LINE('Loop no:'||I);

FETCH example_cursor
BULK COLLECT INTO v_tab_month_rec
LIMIT C_LIMIT;
EXIT WHEN v_tab_month_rec.COUNT = 0;

FORALL INDX IN 1 .. V_TAB_MORT_LOANS.COUNT
--DML statement here
END LOOP;
COMMIT;
end monthly_report;

the above procedure as expected takes around 1 hour to complete.
but the problem is that the dbms_output.put_line messages come only after the stored procedure completes. I mean i get all the output only after 1 hour. Is there any way to see how much of the stored procedure is complete or rather get the dbms_output.put_line message as soon as it gets executed.

and Chris said...

DBMS_output.put_line places the strings in the buffer. These are only displayed when control returns to the client. i.e. the procedure finishes:

The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.


http://docs.oracle.com/database/121/ARPLS/d_output.htm#ARPLS67327

A better approach to log progress to a table. The open source utility logger is great for this:

https://github.com/OraOpenSource/Logger

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

other options

gazza, February 04, 2016 - 10:25 am UTC

Store logging data in a table (autonomous transaction)
or consider DBMS_PIPE?
Connor McDonald
February 04, 2016 - 11:55 am UTC

I prefer the former, and not so much the latter :-)

Cheers,
Connor

Thanks for the answer

Tauseef, February 04, 2016 - 10:31 am UTC

Thanks for the answer. I was disturbing myself and going through so many links on internet if this is possible. Thanks for the answer.
Connor McDonald
February 04, 2016 - 11:56 am UTC

Glad we could help

dbms_application_info

Rajeshwaran, Jeyabal, February 04, 2016 - 12:26 pm UTC

Having calls to dbms_application_info.set_client_info (like below), will show in v$session.client_info about how long we processed so far.

open example_cursor(v_start_date,v_end_Date); 
LOOP 
DBMS_OUTPUT.PUT_LINE('Loop no:'||I); 

FETCH example_cursor 
BULK COLLECT INTO v_tab_month_rec 
LIMIT C_LIMIT; 
EXIT WHEN v_tab_month_rec.COUNT = 0; 

i = i+ v_tab_month_rec.count;
dbms_application_info.set_client_info('count ='||i);

FORALL INDX IN 1 .. V_TAB_MORT_LOANS.COUNT 
--DML statement here 
END LOOP; 
COMMIT; 

Chris Saxon
February 04, 2016 - 5:49 pm UTC

Yes, though if you only want the info at runtime (and no historic record), DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS as suggested in the comment below is better.

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS

Anton, February 04, 2016 - 12:26 pm UTC

If you only need to check for progress using DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS is the way to go.
Chris Saxon
February 04, 2016 - 5:49 pm UTC

Yes - assuming you don't want to keep the stats for later analysis.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library