CODEWITHSUNDEEP
oracle-databaseplsqldbms-output
Деян Добромиров
Деян Добромиров

Reputation: 902

How to find a dbms_output.put_line() alternative to print contents line by line every time when it gets called in every iteration?

I want to view the output as the program goes while processing some records. Reading the line will not help, as it just retrieves is from the buffer and nothing else. For example:

DECLARE
  CURSOR cEmploee IS SELECT * FROM g_emploees;
  iTotal INTEGER := 0;
  iCount INTEGER := 0;
BEGIN
  SELECT COUNT(*) FROM g_emploees INTO iTotal;
  FOR rLine IN cEmploee loop
    dbms_output.put_line('Porcessed['||rLine.id||']: '|| ((iCount/iTotal)*100) || '%')
    iCount := iCount + 1;
  END LOOP;
END;
  1. I cannot use dbms_output.get_line(), So stop marking it answered !
  2. I cannot pipe the output to a file for read-only reasons !

Is there a command/setting for DBMS that I can use in order to view the processed % and print the line for processed in EVERY ITERATION and not at the end as a whole bunch of lines persisting in the buffer (The line printed must show every and exact time in PL/SQL when "dbms_output.put_line" is called not like 500 lines at the end of the execution) ??

Upvotes: 0

Views: 2139

Answers (1)

Prashant Mishra
Prashant Mishra

Reputation: 647

    CREATE OR REPLACE FUNCTION test_pipe 
    RETURN sys.DBMS_DEBUG_VC2COLL 
    pipelined
    as
    CURSOR cEmploee IS 
    SELECT * FROM g_emploees; 
    iTotal INTEGER := 0; 
    iCount INTEGER := 0; 
    BEGIN 
    SELECT COUNT(*) 
    INTO iTotal
    FROM g_emploees ; 
    FOR rLine IN cEmploee loop 
    PIPE row('Porcessed['||rLine.id||']: '|| ((iCount/iTotal)*100) || '%'); 
    iCount := iCount + 1; 
    END LOOP; 
    END;
/

--execute below statements ON command window : 

SQL >set arraysize 1 

SQL > SELECT * FROM TABLE(test_pipe);

Upvotes: 2

Related Questions