Home » SQL & PL/SQL » SQL & PL/SQL » Oracle dbms_job.submit (Oracle 9.2)
Oracle dbms_job.submit [message #284417] Thu, 29 November 2007 10:51 Go to next message
rkdnc9
Messages: 2
Registered: November 2007
Junior Member
Hi All,

I am a Java dev and a newbie to pl/sql and oracle. What I am basically trying to achieve is, my java program is calling a stored procedure which takes hours. Inorder to speed it up I found one of the ways to do it is to run independent tasks in the SP to run parallely. So I set myself out to explore stored-procedures and dbms_job package. Following are 2 stored procedures. One the actual procedure and two is the procedure that has the call to dbms_job.submit(). So that I can call SP 2 which inturn calls dbms_job.submit(). I chose this way because I don't know if I can call dbms_job.submit() from java itself. Somehow the following code does'nt work on whole. I would greatly appreciate if someone can help me with this.

SP 1:

create or replace procedure temp_sp is
begin
dbms_output.put_line('Hi..');
end;

SP 2:

create or replace procedure temp_sp2 is
jobnum number;
begin
dbms_job.submit(jobnum,'temp_sp;');
commit;
end;

Thanks in advance,
-R
Re: Oracle dbms_job.submit [message #284425 is a reply to message #284417] Thu, 29 November 2007 11:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

What does mean "does'nt work on whole"? Explain.
Be sure your parameter job_queue_processes is greater than 0.

Regards
Michel
Re: Oracle dbms_job.submit [message #284426 is a reply to message #284425] Thu, 29 November 2007 11:38 Go to previous messageGo to next message
rkdnc9
Messages: 2
Registered: November 2007
Junior Member
Doesn't work on whole means, I am successful enough in running SP1. But together SP2 and SP1 are not working.. My fault, should have been more clear in explaining the problem.

Anyways thanks for the suggestion.

-R
Re: Oracle dbms_job.submit [message #284431 is a reply to message #284426] Thu, 29 November 2007 11:45 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

together SP2 and SP1 are not working

Still don't know what this mean.
Error? or other thing?

Regards
Michel
Previous Topic: SQL join 3 tables
Next Topic: Calling procedure from application that only supports SQL
Goto Forum:
  


Current Time: Mon Feb 10 10:43:43 CST 2025