Menage return of object from procedure called throgh DBMS_SCHEDULER.create_job

Question

I call a procedure with DBMS_SCHEDULER.create_job. My procedure as output parameter has one object. How can i manage this object?

DBMS_SCHEDULER.create_job (
     job_name     => 'EXAMPLE_JOB',
     job_type     => 'PLSQL_BLOCK',
     job_action   => 'BEGIN DBMS_SCHEDULER.run_program
                     (program_name =>''EXAMPLE_PROG ''); END;',
     start_date   => SYSTIMESTAMP,
     enabled      => TRUE
)
         
             

The procedure EXAMPLE_PROG has one object as output parameter. How can i manage the object of return after the DBMS_SCHEDULER.create_job? I need to insert the resul on one table

Answer 1

You have to do exactly what is described in the answer to the question you pointed (Oracle DBMS_SCHEDULER.create_job with OUT parameter call). Your PLSQL block should be a very usual complete PLSQL block:

• declare local variables
• call the EXAMPLE_PROG with the in and out parameters
• use the out parameters to insert a row in your table and COMMIT

You can write it and test it independently from the scheduling part.

Once it works as expected, then use this PLSQL block to create the job: put it into the job_action.