0

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

  • No. There is no such option. The output will be displayed only when your procedure completes. – Nick Krasnov Aug 24 '16 at 10:21
  • 3
    Possible duplicate of [Is there any way to flush output from PL/SQL in Oracle?](http://stackoverflow.com/questions/1472587/is-there-any-way-to-flush-output-from-pl-sql-in-oracle) – user272735 Aug 24 '16 at 11:22
  • It's also a duplicate of [How to make dbms_output.put_line to print line by line when called in every iteration](http://stackoverflow.com/questions/39099366/how-to-make-dbms-output-put-line-to-print-line-by-line-when-called-in-every-it) and was created because the first one was closed as a duplicate. – William Robertson Aug 24 '16 at 11:37
  • @Деян Добромиров - dbms_output does not work this way, so what you are looking for is an alternative to dbms_output for monitoring long-running procedures. – William Robertson Aug 24 '16 at 11:41
  • I haven't tried this, but if you used `dbms_application_info.set_client_info` then you could poll `v$session` in another session using a pipelined function like Prashant's to show only changed statuses. Or you could write something similar using `dbms_pipe`. – William Robertson Aug 25 '16 at 15:10

1 Answers1

2
    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);
Prashant Mishra
  • 619
  • 9
  • 25
  • 1
    This is not working. Check again if this is working in your system. It throws compilation error " PIPE statement cannot be used in non-piped functions". My oracle version - 11gr2 – XING Aug 24 '16 at 08:17
  • check now..I am writing it on my phone boss :-( Currently, I don't have connectivity to system. – Prashant Mishra Aug 24 '16 at 08:19
  • Thats ok,,I was expecting you to add "Pipelined" ..so now its ok. Also make sure you test it before posting it as an answer. – XING Aug 24 '16 at 08:20
  • 1
    if that helped you then please mark it answer, upvote it and help me getting some reputations.. Happy coding. – Prashant Mishra Aug 24 '16 at 08:21
  • Its not my question..Let the user evaluate. I just helped it correcting it – XING Aug 24 '16 at 08:30
  • ah..I thought I was interacting with actual user who posted this query. Did not see the name while replying. – Prashant Mishra Aug 24 '16 at 08:32
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/121711/discussion-between-prashant-mishra-and-raj-te). – Prashant Mishra Aug 24 '16 at 10:07
  • 1
    mark my solution "answer" ..upvote it and help me building some reputations :-) – Prashant Mishra Aug 24 '16 at 12:38