asked on

How to use dbms_job.submit with insert sql statement?

Have a table T in schema S1 with one column ID varchar2(20). Public synonym was created for T.
Have a user S2 with DBA privilege in schema S2.
Now login as S2 and want to setup dbms job to insert records into T as follows:
declare
jobno BINARY_INTEGER;
begin
dbms_job.submit(JOB=>jobno, what=>'insert into T values (''Test'' || to_char( sysdate,''yymmddHH24MiSS''));', next_date=>sysdate, interval=>'sysdate+1');
end;
/
After creating it without errors, when I executed it, I got the following errors:

SQL> exec dbms_job.run(29);
BEGIN dbms_job.run(29); END;

*
ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 548
ORA-06512: at "SYS.DBMS_JOB", line 278
ORA-06512: at line 1
----------------
Questions:
1) what privileges does S2 still need?
2) How to make it work?
Any gurus' advice is highly appreciated.

Naveen Kumar

can you try this : ( no need to run it as it will automatically run in the first run ). But you need a commit there.

declare
jobno BINARY_INTEGER;
begin
dbms_job.submit(JOB=>jobno, what=>'insert into T values (''Test'' || to_char( sysdate,''yymmddHH24MiSS''));', next_date=>sysdate, interval=>'sysdate+1');
commit;
end;
/

Naveen Kumar

Also the best thing is to have a commit for that insert as well enclosed in a block:

declare
jobno BINARY_INTEGER;
begin
dbms_job.submit(JOB=>jobno, what=>'begin insert into T values (''Test'' || to_char( sysdate,''yymmddHH24MiSS'')); commit;end;', next_date=>sysdate, interval=>'sysdate+1');
commit;
end;
/

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jl66

ASKER

Thanks for everyone's effort.