3 Replies Latest reply: Jun 2, 2014 3:36 PM by 999847 RSS

    Dbma_scheduler job executing procedure that loops through all client schemas in the database rolls back transaction incase of exception

    999847

      Hi,

       

      Needed your inputs on approach to implement a job using dbms_scheduler.

       

      We have around 2000 schemas. Each schema has a package with 2 procedures.

      The requirement is to create a single job in SYS that would loop through each schema and run the procedures at a specific time ( once a day) and send email notification on success or failure.

       

      Job script:

      BEGIN

          dbms_scheduler.create_job( job_name=> 'LOAD_EACH_SCHEMA_AUDIT_DATA',

                                     job_type=>'PLSQL_BLOCK',

                                     job_action=>'BEGIN  sys.p_loadaudit;     

                                      END;',

                                     start_date=>systimestamp,

                                     repeat_interval=>'FREQ=MINUTELY;INTERVAL=1',

                                     number_of_arguments=>0,

                                     enabled=> true,

                                     comments=>'Job repeat interval is every 5 mins' );

                                     END;

      /

      Note: for testing purpose i have set repeat interval to minutely.

       

      Procedure job will be executing:

      Procedure sys.p_loadaudit:

      CREATE OR REPLACE

      PROCEDURE p_loadaudit

      AS

          v_count          NUMBER:= 0;

          lv_error_message VARCHAR2(4000);

          vstmt            VARCHAR2(4000);

      BEGIN

          FOR i IN

          ( SELECT username FROM dba_users WHERE username LIKE 'ABCFIRM%'

          )

          LOOP

              vstmt:= 'begin ' || i.username || '.pkg_audit_info.p_load_coa; end;';

              EXECUTE immediate vstmt;

              vstmt:= 'begin ' || i.username || '.pkg_audit_info.p_load_am; end;';

              EXECUTE immediate vstmt;

          END LOOP;

      EXCEPTION

      WHEN OTHERS THEN

          lv_error_message := 'Error in procedure p_loadaudit: ' || SQLCODE || ' -ERROR- ' || SUBSTR(

          sqlerrm,1,300) || '*' || dbms_utility.format_error_backtrace;

          raise_application_error(-20002,lv_error_message);

      END p_loadaudit;

      /

       

      Example of one schema: SCHEMA_01

      create or replace

      PACKAGE pkg_audit_info

      AS

      type cursortype

      IS

          ref

          CURSOR;

              PROCEDURE p_load_COA;

              PROCEDURE p_load_AM;

         END pkg_audit_info;

      /

      create or replace

      PACKAGE body pkg_audit_info

      AS

       

      PROCEDURE p_load_COA

      AS

      BEGIN

      INSERT INTO TABLE1();

      EXCEPTION

      WHEN OTHERS THEN

          lv_error_message := 'Error in procedure pkg_audit_info.p_load_COA: ' || SQLCODE

          || ' -ERROR- ' || SUBSTR(SQLERRM,1,300) || '*' || dbms_utility.format_error_backtrace;

          RAISE_application_error(-20002,lv_error_message);

      END p_load_COA;

      PROCEDURE p_load_AM

      AS

       

      BEGIN

      INSERT INTO TABLE2();

      EXCEPTION

      WHEN OTHERS THEN

          lv_error_message := 'Error in procedure pkg_audit_info.p_load_AM: ' || SQLCODE ||

          ' -ERROR- ' || SUBSTR(SQLERRM,1,300) || '*' || dbms_utility.format_error_backtrace;

          RAISE_application_error(-20002,lv_error_message);

      END p_load_AM;

       

      END pkg_audit_info;

      /

      Table1 and table1 exist in schema_01.

      All 2000 schemas have same package.procedures.

      Due to security reasons i have removed the actual code.

       

      I was able to execute the job successfully. However, when a schema procedure (SCHEMA_01.pkg_audit_info.p_load_COA) throws an exception, the job fails and all transaction is rolled back.

      Is there a way to loop through each schema and execute the related procedures. Even if exception happens, it should rollback only for that schema and continue the other schemas in the loop?

       

      Please let me know if there is a better way to achieve this. Is the way i am handling exceptions in the job/procedure correct?

       

      Thanks