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?
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;