Home » SQL & PL/SQL » SQL & PL/SQL » Maintaining log table (Oracle 11g)
Maintaining log table [message #575331] Tue, 22 January 2013 05:16 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,
The below procedure is scheduled using DBMS_JOB.
I want to maintain log table for the actions performed by this procedure
and as well as the job information.

CREATE OR REPLACE PROCEDURE load_proc (P_ID IN NUMBER)
IS
   CURSOR C
   IS
      SELECT   GROUP_ID, tablename, target_table
        FROM   temp_table
       WHERE   GROUP_ID = p_id;
BEGIN
   FOR I IN C
   LOOP
      EXECUTE IMMEDIATE   'INSERT INTO '
                       || I.target_table
                       || '(SELECT * FROM '
                       || I.tablename
                       || ' WHERE '
                       || 'last_dt<=SYSDATE-500'
                       || ')';
     EXECUTE IMMEDIATE   'DELETE FROM '
                       || I.tablename
                       || ' WHERE '
                       || 'last_dt<=SYSDATE-500';
      COMMIT;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM
      );
END load_proc;
/



DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'ARP.load_proc(1);'
   ,next_date => to_date('20/01/2013 00:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(SYSDATE+1)'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/


I want to capture the below information in the log table.

SNO RECORDS_INSERTED               RECORDS_DELETED              JOBID   JOB_START_TIME         JOB_END_TIME          JOB_LAST_RAN_ON       JOB_STATUS   
1  2000 records inserted into     2000 records deleted from      1     20/01/2013 00:00:00    20/01/2013 02:30:00  20/01/2013 02:30:00   completed successfully 
   table WEDB_EMPLOYEE            table WEDB_EMPLOYEE
2							         1     21/01/2013 00:00:00     21/01/2013 01:00:00		         Failed: error message
3          
1 NULL

If the job is not started on the particular day NULL value has to be inserted in the JOB_LAST_RAN_ON column.
If the job is not failed in the middle on a particular day FAILED: ERROR MESSAGE value has to be inserted in the JOB_STATUS column.

Script for log table creation.

CREATE TABLE log_load_proc
(sno NUMBER,
records_inserted VARCHAR2(4000),
records_deleted VARCHAR2(4000),
jobid NUMBER,
job_start_time TIMESTAMP,
job_end_time TIMESTAMP,
job_last_ran_on TIMESTAMP,
job_status VARCHAR2(4000));


Please help me on this.

Thanks in advance.
Re: Maintaining log table [message #575337 is a reply to message #575331] Tue, 22 January 2013 05:48 Go to previous message
Michel Cadot
Messages: 57638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want to capture the below information in the log table.


You have to do it in your procedure.

Quote:
If the job is not started on the particular day NULL value has to be inserted in the JOB_LAST_RAN_ON column.


If nothing is executed then nothing can do something.
You have to manage it in your way (for instance from another job which tests if in the previous day(s) the first job was executed or not).

Regards
Michel
Previous Topic: insert mp3 file into BLOB
Next Topic: checking a record exists
Goto Forum:
  


Current Time: Wed Apr 23 10:54:00 CDT 2014

Total time taken to generate the page: 0.24304 seconds