• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2415
  • Last Modified:

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.
0
jl66
Asked:
jl66
  • 2
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
/
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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;
/
0
 
slightwv (䄆 Netminder) Commented:
When running PL/SQL permissions cannot be granted through ROLES.  DBA is a role.  You need to explicitly grant permissions.

As S1:
grant insert into T to S2;
0
 
jl66Author Commented:
Thanks for everyone's effort.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now