4 Replies Latest reply: Feb 10, 2013 2:58 AM by 980490 RSS

    DBMS_JOBS Issue

    980490
      Hi all,

      I'm trying to run some procedures in parallel using dbms_jobs but i'm having some issues doing it.
      when trying to run below code, i'm getting this error

      20:28:16 Info: Job #16 could not be executed. ORA-12011: execution of 1 jobs failed
      ORA-06512: at "SYS.DBMS_IJOB", line 469
      ORA-06512: at "SYS.DBMS_JOB", line 282
      ORA-06512: at line 1
      declare 
        ln_dummy number; 
        p_stdate CONSTANT DATE := '01-MAY-2012'; 
        p_edate  CONSTANT DATE := '31-MAY-2012'; 
        p_cdate CONSTANT DATE := '09-FEB-2013'; 
        p_key CONSTANT INTEGER:= 0; 
        p_ercode  INTEGER; 
        p_erdesc  VARCHAR2(200); 
      begin 
        COMMIT; 
        DBMS_JOB.SUBMIT(ln_dummy,'MY_PROC_1('''|| p_stdate ||''','''|| p_edate ||''','''|| p_cdate||''','''|| p_key ||''', :p_ercode, :p_erdesc: );'); 
        COMMIT; 
      end; 
      / 
      p_ercode and p_erdesc is an out parameter in MY_PROC_1. If i try to comment it out the job runs without an issue.
      My question is how can i run the job without commenting out p_ercode and p_erdesc in MY_PROC_1.

      Also, is there a way to know which job is running and which job is already done? something like an alert?

      Appreciate your inputs. Thank you very much
        • 1. Re: DBMS_JOBS Issue
          sb92075
          977487 wrote:
          Hi all,

          I'm trying to run some procedures in parallel using dbms_jobs but i'm having some issues doing it.
          when trying to run below code, i'm getting this error

          20:28:16 Info: Job #16 could not be executed. ORA-12011: execution of 1 jobs failed
          ORA-06512: at "SYS.DBMS_IJOB", line 469
          ORA-06512: at "SYS.DBMS_JOB", line 282
          ORA-06512: at line 1
          declare 
          ln_dummy number; 
          p_stdate CONSTANT DATE := '01-MAY-2012'; 
          p_edate  CONSTANT DATE := '31-MAY-2012'; 
          p_cdate CONSTANT DATE := '09-FEB-2013'; 
          p_key CONSTANT INTEGER:= 0; 
          p_ercode  INTEGER; 
          p_erdesc  VARCHAR2(200); 
          begin 
          COMMIT; 
          DBMS_JOB.SUBMIT(ln_dummy,'MY_PROC_1('''|| p_stdate ||''','''|| p_edate ||''','''|| p_cdate||''','''|| p_key ||''', :p_ercode, :p_erdesc: );'); 
          COMMIT; 
          end; 
          / 
          p_ercode and p_erdesc is an out parameter in MY_PROC_1. If i try to comment it out the job runs without an issue.
          My question is how can i run the job without commenting out p_ercode and p_erdesc in MY_PROC_1.

          Also, is there a way to know which job is running and which job is already done? something like an alert?

          Appreciate your inputs. Thank you very much
          query DBA_JOBS_RUNNING

          The purpose of TO_DATE is to convert string datatype to DATE datatype.
          With Oracle characters between single quote marks are STRINGS!
          'This is a string, 2009-12-31, not a date'
          When a DATE datatype is desired, then use TO_DATE() function including format.

          '10-11-12'
          Which is correct DATE below for string above?
          Oct. 11 2012
          Nov. 10 2012
          Nov. 12 2010
          Dec. 11 2010
          Oct. 12 2011
          Dec. 10 2011
          I'll give you 6 guesses, since the first 5 will be incorrect.
          • 2. Re: DBMS_JOBS Issue
            980490
            Hi thanks for your reply.

            You mean i just have to use to_date() function to fix the issue?

            Edit: I tried running the same code but I commented out p_ercode and p_erdesc and it ran without any error. So i'm guessing it doesn't have anything to do with the dates?

            Edited by: 977487 on Feb 9, 2013 11:06 AM
            • 3. Re: DBMS_JOBS Issue
              sb92075
              977487 wrote:
              Hi thanks for your reply.

              You mean i just have to use to_date() function to fix the issue?
              no
              PROCEDURE SUBMIT
               Argument Name                  Type                    In/Out Default?
               ------------------------------ ----------------------- ------ --------
               JOB                            BINARY_INTEGER          OUT
               WHAT                           VARCHAR2                IN
               NEXT_DATE                      DATE                    IN     DEFAULT
               INTERVAL                       VARCHAR2                IN     DEFAULT
               NO_PARSE                       BOOLEAN                 IN     DEFAULT
               INSTANCE                       BINARY_INTEGER          IN     DEFAULT
               FORCE                          BOOLEAN                 IN     DEFAULT
              consider constructing a single VARCHAR2 variable that contains valid string to comprise the "WHAT" value
              • 4. Re: DBMS_JOBS Issue
                980490
                Sorry but i still don't get it.

                My code works if i comment out p_ercode and p_erdesc. I just don't get why it wont work if i dont comment it out.

                declare
                ln_dummy number;
                p_stdate CONSTANT DATE := '01-MAY-2012';
                p_edate CONSTANT DATE := '31-MAY-2012';
                p_cdate CONSTANT DATE := '09-FEB-2013';
                p_key CONSTANT INTEGER:= 0;
                -- p_ercode INTEGER;
                -- p_erdesc VARCHAR2(200);
                begin
                COMMIT;
                DBMS_JOB.SUBMIT(ln_dummy,'MY_PROC_1('''|| p_stdate ||''','''|| p_edate ||''','''|| p_cdate||''','''|| p_key ||''');');
                COMMIT;
                end;
                /