1 2 Previous Next 15 Replies Latest reply on Mar 18, 2014 10:02 AM by GregV

    Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS

    Nag Aswadhati

      Hi Experts,

       

         Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS? If not I want to do some action while inserting a record in this view "USER_SCHEDULER_JOB_RUN_DETAILS".

      Please advice me

       

      I tried with below code

       

      create or replace TRIGGER T02_JOB_RUN

      INSTEAD OF INSERT  ON USER_SCHEDULER_JOB_RUN_DETAILS

      FOR EACH ROW

      BEGIN

          DBMS_OUTPUT.put_line('Testing');

      END;

       

      But I got the below error report

       

      Error report -

      ORA-04089: cannot create triggers on objects owned by SYS

      04089. 00000 -  "cannot create triggers on objects owned by SYS"

      *Cause:    An attempt was made to create a trigger on an object owned by SYS.

      *Action:   Do not create triggers on objects owned by SYS.

        • 1. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
          Ramin Hashimzadeh

          Hi Nag.

          What is the purpose of your trigger? What do you want to do?

           

           

          ----

          Ramin Hashimzade

          • 2. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
            Nag Aswadhati

            Hi Ramin,

                      I want remove a workfile which is in oracle directory when the job runs. For each run scheduler will insert a record in USER_SCHEDULER_JOB_RUN_DETAILS.

            If I write a trigger on that view I can remove workfile from the oracle directory.

             

            Purpose: I am writing workfile when the job has strated and I need to remove the workfile when job runs a schedule.

             

            Please let me know if I am not clear with my explanation

            • 3. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
              Ramin Hashimzadeh

              What is the workfile?

              In which JOB you need to delete it?

              Did you owner of the JOB? if yes why you do not delete it on end of the JOB?

               

              -----

              Ramin Hashimzade

              • 4. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                SKP

                You are dealing with a file outside of database.

                Can you just tell us ,so far what you have done?

                • 5. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                  Nag Aswadhati

                  I need to schedule a COBOL program.

                  I am executing that COBOL program from a shell script.

                  I am writing inputs for the COBOL program into the workfile. Shell script will read that file and provide inputs to COBOL Program.

                   

                  I have created a daily job which runs 10 times from 18-Mar-2014 to 28-Mar-2014.

                  On 18-Mar-2014 the first schedule will start, I need to write workfile at that time,until shell script is in sleep mode. after I write inputs shell script will read the workfile.

                  After completion of first schedule Immediately I need to remove the workfile due to maintenance.

                   

                  Yes, i am the owner of the job, But I need to remove the workfile for each schedule.

                  • 6. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                    SKP

                    Here Oracle is doing the scheduling job.

                    Your Shell script Read data from database and creating the workfile.

                    Let the shell script remove the workfile at start of the shell script.

                    At the begining of the shell  script

                    if [  -f  workfile.txt  ]

                    then

                       rm -f  workfile.txt

                    fi

                    continue the same shell script

                    • 7. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                      Ramin Hashimzadeh

                      OK. Then remove the file on the shell script as shown by SKP

                       

                       

                      ----

                      Ramin Hashimzade

                      • 8. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                        Nag Aswadhati

                        Hi,

                         

                        Oracle 11G release 2, LINUX environment.

                        -------------------------------------------------------------

                         

                        Oracle procedure is reading the table and writing into a workfile. Shell script is reading that workfile for inputs.

                        Can I write a shell sctipt using UTL_FILE and is there any possibility to execute that shell script from oracle.

                        Shell script can not remove the workfile, Because the workfile is writing with oracle user credentials.

                         

                          SELECT JOBID, WORKFILEPATH, program_name

                                  INTO l_jobid, l_filename, l_programname

                                FROM jobs

                                WHERE upper(jobname) = upper(l_jobname);

                                

                                SELECT xmltype(params), scheduleid, exclude_flag

                                INTO l_xmldoc, l_scheduleid, l_excludeflag

                                FROM (SELECT params, scheduleid, exclude_flag FROM JOB_PARAMETERS WHERE JOBID = l_jobid AND RUN_FLAG = 'N' ORDER BY scheduledatetime)

                                WHERE ROWNUM = 1;

                          

                                SELECT  extractValue(VALUE(P), '//Parameter/Value') argvalue   

                                BULK COLLECT INTO l_arg_tab

                                FROM TABLE(XMLSEQUENCE(EXTRACT(l_xmlDoc, '//Parameter'))) P;     

                         

                        -- OPEN FILE FOR WRITING

                              input_file :=  Utl_File.FOpen('CMPLUS_WORKFILE' , l_filename , 'W');

                            

                              IF l_excludeflag = 0 THEN

                                  -- Writing into file  

                                  UTL_FILE.PUT_LINE(input_file, 'export jobnum=J'||l_jobid);

                                  UTL_FILE.PUT_LINE(input_file,'run'||' '||'pub/'||l_programname||'<<:EOD:');

                                  FOR i IN 1..l_arg_tab.COUNT LOOP

                                      UTL_FILE.PUT_LINE(input_file, CASE WHEN l_arg_tab(i) = '$' THEN '' ELSE l_arg_tab(i) END);

                                  END LOOP;

                                  UTL_FILE.PUT_LINE(input_file, ':EOD:');

                              

                                  UPDATE job_parameters SET run_flag = 'Y' WHERE scheduleid = l_scheduleid;

                                  COMMIT;

                              ELSIF l_excludeflag = 1 THEN

                                  UTL_FILE.PUT_LINE(input_file, 'echo "Excluded schedule." 1>' || chr(38) || '2');

                                  UTL_FILE.PUT_LINE(input_file, 'exit 3');

                              END IF;

                            

                              -- CLOSE FILE

                              UTL_FILE.FCLOSE(input_file);

                        • 9. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                          GregV

                          Hi,

                           

                          As the message say, you can't create trigger on sys owned objects.

                          Instead of a trigger you could have an event-based to do that for you.

                          • 10. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                            Nag Aswadhati

                            Hi Greg,

                             

                            I have created a job and raising a JOB_STARTED event and creating event based job to wrilte inputs into workfile by reading a table.

                            Do I need to create another event based job to remove the workfile. (Sorry If not understand your context correctly ).

                             

                            DBMS_SCHEDULER.CREATE_JOB

                                                            (  job_name             => p_job_name           

                                                             , job_type             => p_jobaction 

                                                           --  , job_action           => '/cmplus/pub/runtimeargs.sh'                  

                                                             --, job_action           => '/usr/sedc/commands/schdulerscrpt.sh'      

                                                             , job_action           => '/home/upncommn/pub/finalruntime.sh'      

                                                             , number_of_arguments  => p_number_of_arguments

                                                             , start_date           => p_startdate

                                                             , repeat_interval      => p_repeat_interval

                                                             , end_date             => p_enddate

                                                             , enabled              => FALSE

                                                             , job_class            => 'DEFAULT_JOB_CLASS'

                                                             , auto_drop            => FALSE

                                                             , comments             => p_comments

                                                             , credential_name      => '"upn_common"'

                                                             , destination_name     => NULL

                                                            ); 

                             

                             

                                    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(name => p_job_name, attribute => 'raise_events', value => '1');

                             

                             

                             

                                    BEGIN

                                      l_job_name := p_job_name||'_$';

                                      p_jobaction := 'declare  l_inputxml CLOB;  l_outputxml CLOB;  l_status  PLS_INTEGER;

                                                          begin

                                                          l_inputxml := ''<InputParameters><HpUserId>2079</HpUserId><UserName>NAGARAJUA</UserName><UserPass></UserPass><ClientName></ClientName><ParamSet><flag>BACKEND</flag><jobname>'||p_job_name||'</jobname></ParamSet></InputParameters>'';

                                                          JOB_CALENDAR.WRITE_FILE(l_inputxml,l_outputxml,l_status); end;';

                                                         

                                       sys.DBMS_SCHEDULER.create_job (

                                          job_name          => l_job_name,

                                          job_type          => 'PLSQL_BLOCK',

                                          job_action        => p_jobaction,

                                          event_condition   => 'tab.user_data.event_type IN (''JOB_STARTED'') and tab.user_data.object_name = '''||upper(p_job_name)||''' ',

                                          queue_spec        => 'sys.scheduler$_event_queue,queue_agent',

                                          enabled           => TRUE);

                                      END;

                             

                            The below code is in WRITE_FILE procedure

                             

                            SELECT JOBID, WORKFILEPATH, program_name

                                      INTO l_jobid, l_filename, l_programname

                                    FROM jobs

                                    WHERE upper(jobname) = upper(l_jobname);

                                   

                                    SELECT xmltype(params), scheduleid, exclude_flag

                                    INTO l_xmldoc, l_scheduleid, l_excludeflag

                                    FROM (SELECT params, scheduleid, exclude_flag FROM JOB_PARAMETERS WHERE JOBID = l_jobid AND RUN_FLAG = 'N' ORDER BY scheduledatetime)

                                    WHERE ROWNUM = 1;

                             

                                    SELECT  extractValue(VALUE(P), '//Parameter/Value') argvalue  

                                    BULK COLLECT INTO l_arg_tab

                                    FROM TABLE(XMLSEQUENCE(EXTRACT(l_xmlDoc, '//Parameter'))) P;    

                             

                            -- OPEN FILE FOR WRITING

                                  input_file :=  Utl_File.FOpen('CMPLUS_WORKFILE' , l_filename , 'W');

                               

                                  IF l_excludeflag = 0 THEN

                                      -- Writing into file 

                                      UTL_FILE.PUT_LINE(input_file, 'export jobnum=J'||l_jobid);

                                      UTL_FILE.PUT_LINE(input_file,'run'||' '||'pub/'||l_programname||'<<:EOD:');

                                      FOR i IN 1..l_arg_tab.COUNT LOOP

                                          UTL_FILE.PUT_LINE(input_file, CASE WHEN l_arg_tab(i) = '$' THEN '' ELSE l_arg_tab(i) END);

                                      END LOOP;

                                      UTL_FILE.PUT_LINE(input_file, ':EOD:');

                                 

                                      UPDATE job_parameters SET run_flag = 'Y' WHERE scheduleid = l_scheduleid;

                                      COMMIT;

                                  ELSIF l_excludeflag = 1 THEN

                                      UTL_FILE.PUT_LINE(input_file, 'echo "Excluded schedule." 1>' || chr(38) || '2');

                                      UTL_FILE.PUT_LINE(input_file, 'exit 3');

                                  END IF;

                               

                                  -- CLOSE FILE

                                  UTL_FILE.FCLOSE(input_file); 

                            • 12. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                              Nag Aswadhati

                              Already I am executing a shell script from the PL/SQL procedure

                               

                              DBMS_SCHEDULER.CREATE_JOB

                                                              (  job_name             => p_job_name          

                                                               , job_type             => p_jobaction

                                                             --  , job_action           => '/cmplus/pub/runtimeargs.sh'                 

                                                               --, job_action           => '/usr/sedc/commands/schdulerscrpt.sh'     

                                                               , job_action           => '/home/upncommn/pub/finalruntime.sh'     

                                                               , number_of_arguments  => p_number_of_arguments

                                                               , start_date           => p_startdate

                                                               , repeat_interval      => p_repeat_interval

                                                               , end_date             => p_enddate

                                                               , enabled              => FALSE

                                                               , job_class            => 'DEFAULT_JOB_CLASS'

                                                               , auto_drop            => FALSE

                                                               , comments             => p_comments

                                                               , credential_name      => '"upn_common"'

                                                               , destination_name     => NULL

                                                              );

                              • 13. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                                GregV

                                Why don't you use the UTL_FILE.FREMOVE procedure to remove the file once it has been processed?

                                • 14. Re: Can I create a trigger on USER_SCHEDULER_JOB_RUN_DETAILS
                                  Nag Aswadhati

                                  I am using below code for removing the files. I am storing all the workfile names in a table.

                                  I need to remove all the workfiles expect those jobs which are in RUNNING state.

                                   

                                  "DECLARE

                                  TYPE file_rec IS RECORD(jobname VARCHAR2(30), inputfile VARCHAR2(50));

                                  TYPE tab_file IS TABLE OF file_rec INDEX BY BINARY_INTEGER;

                                  l_filetab  tab_file;

                                   

                                   

                                  BEGIN

                                      SELECT jobname, workfilepath

                                      BULK COLLECT INTO l_filetab

                                      FROM JOBS WHERE

                                      EXISTS(SELECT 1 FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = jobname AND status <> 'RUNNING');

                                     

                                      FOR i IN 1..l_filetab.COUNT LOOP

                                     

                                      UTL_FILE.FREMOVE('CMPLUS_WORKFILE',

                                                        ''||l_filetab(i).inputfile||'');

                                     

                                      END LOOP;

                                   

                                   

                                  END;

                                  /"

                                  1 2 Previous Next