Solved

How to use dbms_job.submit with insert sql statement?

Posted on 2011-03-14
4
2,268 Views
Last Modified: 2012-05-11
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
Comment
Question by:jl66
  • 2
4 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35135354
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 35135356
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35137275
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
 

Author Closing Comment

by:jl66
ID: 35138508
Thanks for everyone's effort.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now