oracle-databaseplsqldbms-job

Call a Procedure with parametes through DBMS_JOB.SUBMIT


I am working on Oracle 11gR1

I have to call a procedure which accepts a CLOB as input parameter through a DBMS_JOB.SUBMIT procedure.

Here is my code for the same:

FOR i IN 1 .. lrec_resultset.COUNT LOOP

                    DBMS_JOB.SUBMIT (
                    JOB => job_num,
                    WHAT => 'execute_dsql('' ||lrec_resultset(i).sql_txt || '');');
COMMIT:
END LOOP;

The call to execute_dsql is not being made. I am not even getting any error message when I execute my PL/SQL block which contains this DBMS_JOB call.

Can someone point me in the right direction?


Solution

  • I'd do something like this:

    for i in 1 .. lrec_resultset.count loop
    
          dbms_job.submit (job  => job_num,
                           what => 'execute_dsql(mypkg.get_clob);');
    
          insert into mytbl values (job_num, lrec_resultset(i).sql_txt);
    
          commit;
    
    end loop;
    
    create package body mypkg
    as
    begin
    
      function get_clob
        return clob
      as
    
        v_clob clob;
        v_job  number;
    
      begin
    
         select sys_context('userenv','bg_job_id') into v_job from dual;
    
         select myclob
           into v_clob
           from mytbl
          where job_id = v_job;
    
         return v_clob;
    
      end get_clob;
    
    end mypkg;