CODEWITHSUNDEEP
oracle-databaseplsql
user2268668
user2268668

Reputation: 31

How to manually generate a log file the same as SQL*Plus spool results

I need to generate a log file or write to a log table for a stored PL/SQL procedure. If I understand correctly, I can't use spool in a stored procedure. I know I can write to a log file with UTL_FILE, but I'm trying to figure out how to get some of the information I want to write. When spooling, you get some kind of result string after each statement is executed, like "PL/SQL procedure successfully completed" or "5 rows selected." Is there a way to get that output, or something similar, so that I can put them in my logs?

Upvotes: 2

Views: 495

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

Not directly. That output is generated by SQL*Plus, not by anything in the database. If you want to generate output after each SQL statement in your procedure you'll have to put code in your procedure to output whatever it is you want.

"PL/SQL procedure successfully completed" could be generated by surrounding your procedure call in a BEGIN..END block with an exception handler. If the exception handler isn't invoked print out Successfully completed. If it is invoked, use a WHEN OTHERS to dump out whatever debugging information you want.

BEGIN
  INVOKE_YOUR_PROCEDURE_HERE;
  UTL_FILE.PUT_LINE(fileHandle, 'PL/SQL procedure successfully completed');
EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.PUT_LINE(fileHandle, 'Procedure failed: ' || SQLCODE || ' - ' || SQLERRM);
END;

"5 rows selected" could be done by printing out SQL%ROWCOUNT after your SELECT statement executes, as in

UTL_FILE.PUT_LINE(fileHandle, SQL%ROWCOUNT || ' rows affected');

Best of luck.

Upvotes: 6

Related Questions