This discussion is archived
8 Replies Latest reply: Feb 3, 2013 4:40 AM by Purvesh K RSS

Maintaining log table

976208 Explorer
Currently Being Moderated
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.
  • 1. Re: Maintaining log table
    Purvesh K Guru
    Currently Being Moderated
    973205 wrote:
    >
    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.

    Thanks for posting the Procedure, Job and Table Details.

    How do you want to represent the data in Log table, if you are processing Multiple Tables in a Single Job Run?

    Certainly, to comply with your First requirement, you will have to setup another job that does the Logging for you.
    Or, Calling of Job procedure has be modified like below
    DECLARE
    X NUMBER;
    BEGIN
    SYS.DBMS_JOB.SUBMIT
    ( job       => X 
    ,what      => 'ARP.some_procedure(1);'           ----------> some_procedure will call Log Procedure, followed by load_proc(1) and further followed by Log procedure to indicate success/failure of Job;
    ,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;
    /
    Below is a sample code, which you can modify according to your requriements:
    create or replace procedure log_load_job
    (
      p_rec_inserted                varchar2,
      p_rec_deleted                 varchar2,
      p_job_id                      number,
      p_start_date                  date,
      p_end_date                    date,
      p_job_status                  varchar2
    )
    is
    declare
      pragma autonomous_transaction;
    begin
      insert into log_load_job (records_inserted, records_deleted, job_id, start_date, end_date, last_run_date, job_status)
      values (p_rec_inserted, p_rec_deleted, p_job_id, p_start_date, p_end_date, decode(instr(p_job_status, 'ERROR'), 0, p_end_date, null), p_job_status);
                                                               --> Decode to check if Job Status is Error, do not enter Last Run Date
      
      commit;
    
    exception
      when others then
        null;             ---> Failure of LOGGER must not interrupt the normal execution.
    end log_load_job;
    
    
    CREATE OR REPLACE PROCEDURE load_proc (P_ID IN NUMBER)
    is
      v_inserted        varchar2(4000);
      v_deleted         varchar2(4000);
      v_start_date      date;
       CURSOR C
       IS
          SELECT   GROUP_ID, tablename, target_table
            FROM   temp_table
           WHERE   GROUP_ID = p_id;
    begin
      v_start_date := sysdate;
      
       FOR I IN C
       LOOP
          EXECUTE IMMEDIATE   'INSERT INTO '
                           || I.target_table
                           || '(SELECT * FROM '
                           || I.tablename
                           || ' WHERE '
                           || 'last_dt<=SYSDATE-500'
                           || ')';
          v_inserted := sql%rowcount || ' records inserted into ' || i.tablename;
         EXECUTE IMMEDIATE   'DELETE FROM '
                           || I.tablename
                           || ' WHERE '
                           || 'last_dt<=SYSDATE-500';
          v_deleted := sql%rowcount || ' records deleted from ' || i.tablename;
          
          log_load_job(v_inserted, v_deleted, p_id, v_start_date, sysdate, 'Completed');
          --COMMIT;
          v_inserted := null;
          v_deleted := null;
       end loop;
       
       commit;                ---> Commit After Entire Tables have been Deleted and Inserted.
    EXCEPTION
       WHEN OTHERS
       then
        log_load_job(v_inserted, v_deleted, p_id, v_start_date, sysdate, 'Error :: ' || sqlerrm);
          ROLLBACK;
    end load_proc;
    /
    Untested piece of code.
  • 2. Re: Maintaining log table
    976208 Explorer
    Currently Being Moderated
    Thank you very much for your reply.

    I want to insert jobid (or) jobname into log_table.
    Let us say in our DB 10 to 15 jobs are running.So that user wants to know which job has loaded the data.
    In your earlier code you have inserted the P_ID ,the P_ID is a value passing from job to procedure that is not the job id.
    Please help me how to insert jobid (or) jobname value into the log_table.

    Thanks.
  • 3. Re: Maintaining log table
    Purvesh K Guru
    Currently Being Moderated
    973205 wrote:
    Thank you very much for your reply.

    I want to insert jobid (or) jobname into log_table.
    Let us say in our DB 10 to 15 jobs are running.So that user wants to know which job has loaded the data.
    In your earlier code you have inserted the P_ID ,the P_ID is a value passing from job to procedure that is not the job id.
    Please help me how to insert jobid (or) jobname value into the log_table.

    Thanks.
    I will suggest you to migrate to DBMS_SCHEDULER instead of DBMS_JOB, which will spare you from maintaining the JOBID's. Also, DBMS_SCHEDULER replacing DBMS_JOB since Oracle 10g, it is recommended to use the same.
    More information on DBMS_SCHEDULER.

    Moreover, when you are performing the Log operation using this procedure itself, and assuming you have only one Job that is executing the procedure, why do you need to maintain the job details?

    If you still require, you may Hard code the Job Name, passing it as a parameter to the Load procedure and further cascading it to the Log procedure. So, when anybody queries data from the Log table, the Job Name shall also be displayed along with other details.
  • 4. Re: Maintaining log table
    976208 Explorer
    Currently Being Moderated
    As you suggested I have created the procedure and it's working fine.

    However some discrapency is there for log table information and USER_JOBS information.

    If my main procedure executes fine without going to exception block
    the entry in the STATUS column of LOG_LOAD_JOB table is making as 'Completed' and the
    FAILURES column of USER_JOBS is showing "0" this scenario is absolutly fine.

    If my main procedure going to exception block
    the entry in the STATUS column of LOG_LOAD_JOB table is making as 'Failed:errormsg' and the
    FAILURES column of USER_JOBS is showing "0" this scenario is wrong.

    Whenever the control goes to exception block of the procedure
    the entry in the STATUS column of LOG_LOAD_JOB table is making as 'Failed:errormsg' and the
    FAILURES column of USER_JOBS is should show "1"

    I.e.Whenever the exception block of the procedure is executed then the job should fail
    and the entry for STATUS column of LOG_LOAD_JOB table is making as 'Failed:errormsg'

    Please help me thanks in advance.
  • 5. Re: Maintaining log table
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 6. Re: Maintaining log table
    976208 Explorer
    Currently Being Moderated
    Sorry for not following the guidelines.

    Please help me.

    Thanks.
  • 7. Re: Maintaining log table
    sb92075 Guru
    Currently Being Moderated
    973205 wrote:
    Sorry for not following the guidelines.

    Please help me.

    Thanks.
    Handle:     973205
    Status Level:     Newbie
    Registered:     Nov 26, 2012
    Total Posts:     37
    Total Questions:     9 (9 unresolved)


    why do you waste time here when you NEVER get any answer for your questions?
  • 8. Re: Maintaining log table
    Purvesh K Guru
    Currently Being Moderated
    973205 wrote:
    As you suggested I have created the procedure and it's working fine.

    However some discrapency is there for log table information and USER_JOBS information.

    If my main procedure going to exception block
    the entry in the STATUS column of LOG_LOAD_JOB table is making as 'Failed:errormsg' and the
    FAILURES column of USER_JOBS is showing "0" this scenario is wrong.

    Whenever the control goes to exception block of the procedure
    the entry in the STATUS column of LOG_LOAD_JOB table is making as 'Failed:errormsg' and the
    FAILURES column of USER_JOBS is should show "1"

    I.e.Whenever the exception block of the procedure is executed then the job should fail
    and the entry for STATUS column of LOG_LOAD_JOB table is making as 'Failed:errormsg'
    Following the ROLLBACK with a RAISE. This will cause the Job to be reported as Failed and you will get an increase in Failure count.

    You have not shared the Schedule of your Job, hence, not sure if Status column would be of much help. Because if you have a successful run of Job after a Failure, the status column would be displayed with status of last run.

    I hope this should help you.

    And yes, I think you should heed to the advice offered by SB about marking your Questions/Threads as Answered once your problem is solved, marking posts as Helpful/Answered as appropriate to you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points