Call a Procedure with parametes through DBMS_JOB.SUBMIT

Question

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?

score 3 · Accepted Answer · edited Mar 24 '15 at 11:45

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;

Note that just `'mypkg.get_clob;'` works fine. While `execute_dsql` is something custom and not public. — Vadzim, Oct 16 '14 at 15:15

score -1 · Answer 2 · answered Jun 01 '12 at 15:31

-1

you have a one single quote too much too much try :

WHAT => 'execute_dsql(' ||lrec_resultset(i).sql_txt || ');');

answered Jun 01 '12 at 15:31

schurik

7,798
2
23
29

1

Actually, not enough single quotes! Needs to be: `WHAT => 'execute_dsql(''' ||lrec_resultset(i).sql_txt || ''');');` – Tony Andrews Jun 01 '12 at 16:16

Call a Procedure with parametes through DBMS_JOB.SUBMIT

2 Answers2