Oracle Stored Procedure Log File

Question

I have a oracle procedure which selects data and insert into another table. Bellow is the code.

CREATE OR REPLACE PROCEDURE "CUSTOMER_INCREMENTAL" (
     IS   
BEGIN
  INSERT INTO NDB_AML_CUSTOMER 
    (ID, TITLE,...)
  SELECT ID, TITLE,... 
    FROM NDB_CUSTOMER_NEW
    WHERE DATE_TIME > (SELECT RUN_DATE FROM CHECK_POINT WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW');

  UPDATE CHECK_POINT SET RUN_DATE = SYSDATE WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW';

  COMMIT;
END;
/

I want to know how to output the events into a table. Like Process Start Time Stamp, Process End Time Stamp & in an exception what error msg. So there will be two columns like Date and Message in the Log Table. Any suggestions?

What exactly do you want the log to contain? Do you want a new file every time it's executed, or a cummulative log? How will you run the procedure, and do you want the log on the server or your client machine(s)? — Alex Poole, May 11 '16 at 07:13
If its for server side then you can use utl_file. Google with this word and you will enough information. @Alex Poole I m curious to know how procedure logs will be generated at client machine? — Pravin Satav, May 11 '16 at 07:20
There is no way to redirect DBMS_OUTPUT to a file. Oracle does not have built in (application) logging. So if you want to do this you will need to write your own implementation or, if you would be happy logging to a table rather than a file, down [the Logger package from Github](https://github.com/OraOpenSource/Logger) — APC, May 11 '16 at 07:28
@PravinSatav - spool, script output redirection... not saying that's sensible or safe, even if the procedure is always expected to be called a certain way - just trying to figure out what the OP is aiming for. At the moment not sure what they want to log. — Alex Poole, May 11 '16 at 07:40
@Alex Poole - thanks for the info. Got an idea how it can be done.... — Pravin Satav, May 11 '16 at 09:28

user1283565 · Answer 1 · 2016-05-11T13:10:25.713

1

create or replace procedure my_log (action in varchar2, message in varchar2 )
is

begin
  Insert into my_log_table (ACTION, MESSAGE, EVENT_DATE)
  values (action, message, sysdate);
  commit;
end;
/
CREATE OR REPLACE PROCEDURE "CUSTOMER_INCREMENTAL" ()
IS   
   err_num NUMBER;
   err_msg VARCHAR2(4000);
BEGIN
    my_log ('Start','My message');
  INSERT INTO NDB_AML_CUSTOMER 
(ID, TITLE,...)
  SELECT ID, TITLE,... 
FROM NDB_CUSTOMER_NEW
WHERE DATE_TIME > (SELECT RUN_DATE FROM CHECK_POINT WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW');

  UPDATE CHECK_POINT SET RUN_DATE = SYSDATE WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW';

  COMMIT;
  my_log ('End','My message');
  EXCEPTION
  WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SQLERRM;
my_log ('Error' , errnum ||' - ' || err_msg);
 END;
/

edited May 11 '16 at 13:10

answered May 11 '16 at 10:07

user1283565

31
3

CREATE OR REPLACE PROCEDURE "CUSTOMER_INCREMENTAL" ( where is the close bracket? – DeE DEe May 11 '16 at 11:22
Procedure won't compile. – DeE DEe May 30 '16 at 08:28
There should be an autonomous commit directive on my_log – Andrew Spencer May 09 '18 at 14:41

score 0 · Answer 2 · answered May 09 '18 at 14:44

It's much easier to log to a table, as suggested in the other answer, but rather than writing your own logging procedure, use LOGGER which is well documented and tested.

Here's an example taken from the documentation:

begin
  logger.log('This is a debug message. (level = DEBUG)');
  logger.log_information('This is an informational message. (level = INFORMATION)');
  logger.log_warning('This is a warning message. (level = WARNING)');
  logger.log_error('This is an error message (level = ERROR)');
  logger.log_permanent('This is a permanent message, good for upgrades and milestones. (level = PERMANENT)');
end;
/

select id, logger_level, text
from logger_logs_5_min
order by id;

  ID LOGGER_LEVEL TEXT
---- ------------ ------------------------------------------------------------------------------------------
  10       16     This is a debug message. (level = DEBUG)
  11        8     This is an informational message. (level = INFORMATION)
  12        4     This is a warning message. (level = WARNING)
  13        2     This is an error message (level = ERROR)
  14        1     This is a permanent message, good for upgrades and milestones. (level = PERMANENT)

Oracle Stored Procedure Log File

2 Answers2