Solved

How to use dbms_job.submit with insert sql statement?

Posted on 2011-03-14
4
2,310 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

752 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