8 Replies Latest reply: Feb 3, 2013 6:40 AM by Purvesh K RSS

    Maintaining log table

    976208
      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
          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
            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
              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
                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
                  How do I ask a question on the forums?
                  SQL and PL/SQL FAQ
                  • 6. Re: Maintaining log table
                    976208
                    Sorry for not following the guidelines.

                    Please help me.

                    Thanks.
                    • 7. Re: Maintaining log table
                      sb92075
                      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
                        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.