How to use dbms_job.submit with insert sql statement?
Posted on 2011-03-14
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:
dbms_job.submit(JOB=>jobno, what=>'insert into T values (''Test'' || to_char( sysdate,''yymmddHH24MiSS''));', next_date=>sysdate, interval=>'sysdate+1');
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
1) what privileges does S2 still need?
2) How to make it work?
Any gurus' advice is highly appreciated.