Solved

DBMS_JOB.SUBMIT question

Posted on 2003-11-17
34
12,898 Views
Last Modified: 2007-12-19
I am trying to run a stored procedure using DBMS_JOB.SUBMIT. Here is the what I am trying in SQL Plus:

declare njobno number;
begin
      dbms_job.submit(:njobno, 'MyDatabase.MyProcedure', '17-NOV-2003', NULL);
      commit;
end;

I get the following error message:

declare njobno number;
*
ERROR at line 1:
ORA-06550: line 1, column 127:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
:= . ( @ % ;
The symbol ";" was substituted for "END" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 131
ORA-06512: at line 3

I have seen numerous examples of this and I followed the examples. Another way I tried is:

declare njobno number;
begin
 dbms_job.submit(:njobno, 'begin MyDatabase.MyProcedure; end;', '17-NOV-2003', NULL);
 commit;
end;

This gave the following error message:

declare njobno number;
*
ERROR at line 1:
ORA-06550: line 1, column 99:
PLS-00201: identifier 'MyDatabase.MyProcedure' must be declared
ORA-06550: line 1, column 99:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 131
ORA-06512: at line 3

I have tried almost everything possible. Does anybody have any idea as to what I am doing wrong? I would like the procedure to execute immdiately now. I know I can use DBMS_RUN but to do that I have to have the job number available in the user queue first.
0
Comment
Question by:mfarid1
[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
  • 9
  • 6
  • 5
  • +7
34 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9766379
you could use either:

declare njobno number;
begin
dbms_job.submit(:njobno, 'execute MyDatabase.MyProcedure;', '17-NOV-2003', NULL);
commit;
end;


OR


declare njobno number;
begin
dbms_job.submit(:njobno, 'begin MyDatabase.MyProcedure; end;', '17-NOV-2003', NULL);
commit;
end;


But you have to log in as the owner of this package, and grant the execute privilege on this package to the user who is running job.
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9766636
This one adds a job to run every Sunday at 9:00 pm.
You do not need begin...end

variable jobno number;
begin
dbms_job.submit(:jobno,'MyProcedure;',
NEXT_DAY(TRUNC(SYSDATE),'SUNDAY') + 21/24,
'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'') + 21/24');
end;
/
print jobno;
0
 

Author Comment

by:mfarid1
ID: 9766637
seazodiac, I tried your first technique. I get the following error:

declare njobno number;
*
ERROR at line 1:
ORA-06550: line 1, column 101:
PLS-00103: Encountered the symbol "MyDatabase" when expecting one of the
following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "MyDatabase" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 131
ORA-06512: at line 3


I have already tried your second technique before I posted the question. Look at my first post's second DBMS_JOB listing. I have posted the error that I am getting there.
0
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!

 
LVL 23

Expert Comment

by:seazodiac
ID: 9766739
sorry, try this instead:
can you also make sure that the user who execute this command has the EXECUTE privs on this package?

declare njobno number;
begin
dbms_job.submit(:njobno, 'MyDatabase.MyProcedure;', '17-NOV-2003', NULL);
commit;
end;
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 9766779

SUBMIT Procedure
This procedure submits a new job. It chooses the job from the sequence sys.jobseq.

Syntax
DBMS_JOB.SUBMIT (
   job       OUT BINARY_INTEGER,
   what      IN  VARCHAR2,
   next_date IN  DATE DEFAULT sysdate,
   interval  IN  VARCHAR2 DEFAULT 'null',
   no_parse  IN  BOOLEAN DEFAULT FALSE,
   instance  IN  BINARY_INTEGER DEFAULT any_instance,
   force     IN  BOOLEAN DEFAULT FALSE);

Example:

VARIABLE jobno number;
BEGIN
   DBMS_JOB.SUBMIT(:jobno,'PL/SQL procedure or anonymous PL/SQL block'  , SYSDATE, 'SYSDATE + 1');
   commit;
END;

Procdeure:

VARIABLE jobno number;
BEGIN
 DBMS_JOB.SUBMIT(jobno,'schema.package_name.procedure_name;', sysdate+2/1440,'SYSDATE+ 20/1440');
 COMMIT;
END;

PL/SQL block:

VARIABLE jobno number;
BEGIN
 DBMS_JOB.SUBMIT(jobno,'begin .............  end;', sysdate+2/1440,'SYSDATE+ 20/1440');
 COMMIT;
END;
0
 

Author Comment

by:mfarid1
ID: 9766987
seazodiac, you 2nd solution is my 1st run in my very first posting. Please take a look at it. They are exactly the same and I also posted the error that I got. I do have access to the jobs I submit.

Danielzt, I have tried the solutions you have metioned before. They are already available elsewhere on the Internet. PL/SQL does not understand:

VARIABLE jobno number

but it does understand

DECLARE jobno number

Anyway, I have pretty much tried everything mentioned here and elsewhere on the Internet. I found out from my DBA that I do have access to DBMS_JOB.SUBMIT to my procedures and jobs so I am OK but I think I have a syntax problem.
0
 

Author Comment

by:mfarid1
ID: 9767033
I am sorry, I was using the wrong ID.
Here is my code:

declare nJobNo number;
begin
    dbms_job.submit(:nJobNo, 'MyDatabase.MyProcedure','17-NOV-2003', NULL)
end;

However, I still get the following error:

SP2-0552: Bind variable "NJOBNO" not declared.

You can see that the variable is very clearly declared. I am running this from SQLPlus. Any ideas?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9767045
you should not use :nJobNo

try this:


declare nJobNo number;
begin
   dbms_job.submit(nJobNo, 'MyDatabase.MyProcedure','17-NOV-2003', NULL)
end;
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 186 total points
ID: 9767083
that's because nJobNo is a PL/SQL variable, not a Host variable.
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9767257
Have you tried anyone else solution.  This works as posted earlier.
This one adds a job to run every Sunday at 9:00 pm.
You do not need begin...end

variable jobno number;
begin
dbms_job.submit(:jobno,'MyProcedure;',
NEXT_DAY(TRUNC(SYSDATE),'SUNDAY') + 21/24,
'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'') + 21/24');
end;
/
print jobno;
0
 
LVL 1

Expert Comment

by:jgilbertb
ID: 9768345
The problem is that you cannot use the "declare nJobNo number;" in SQL*PLUS, only in PL/SQL.
SQL*PLUS want to see "variable nJobNo number;"  try using the second format suggested by DrJekyll and also by Danielzt above.
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9768446
This is run OK from my SQL*PLus:

SQL> ed
Wrote file afiedt.buf

  1  declare njobno number;
  2  begin
  3        dbms_job.submit(njobno, 'TEST_PRO;', '17-NOV-2003', NULL);
  4        commit;
  5* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>

TEST_PRO is my stored procedure. Find the schedule in dba_jobs:

SQL> /

       JOB  LOG_USER                       WHAT                 NEXT_DATE
---------- ------------------------------ -------------------- ---------
         1   TUSER                         TEST_PRO;              17-NOV-03

1 rows selected.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9768881
The second comment by DrJykell contained the answer...
instead of

declare  njobno number;
use
variable  njobno number;

or

do not use :njobno in submit.

0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 9769859
I had the exact same problem a couple of days ago!

declare
njobno number;
begin
      dbms_job.submit(njobno, 'MyDatabase.MyProcedure;', '17-NOV-2003', NULL);
      commit;
end;

The solution to my problem was to stick a ; at the end of my procedure call, as in 'MyDatabase.MyProcedure;' above.
0
 

Expert Comment

by:alokgupt
ID: 9770395
TRY this...

will work for sure

declare njobno binary_integer;
begin
dbms_job.submit(njobno, 'sproc_alok;', '18-NOV-2003', NULL);
commit;
end;
/

or
declare njobno binary_integer;
begin
dbms_job.submit(njobno, 'sproc_alok;', sysdate, NULL);
commit;
end;
/


alok
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 9770834
I think you have the solution by now, but I want to add two comments about your syntax.

1. You *DO NOT* need the "MyDatabase." syntax.  You may optionally add the schema name there (like: "schema.procedure_name;") but you only need this if the procedure is in a different schema and there is no private or public synonym for it.

2. You definitely *DO* need a semi-colon ";" at the end of the procedure name and inside of the single quotes, as grim_toaster mentioned.
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9770933
I agree, I know I posted a working example 14 messages ago.<g>
0
 

Author Comment

by:mfarid1
ID: 9772656
Actually, seazodiac's solution worked. I used nJobNumber.

Does anyone know that if I do this:

dbms_job.run(job1);
dbms_job.run(job2);
dbms_job.run(job3);

(I did not give the complete syntax but I already know the correct syntax.)

Is this going to start Job1 and NOT WAIT for Job1 to finish and start Job2 and NOT WAIT for Job2 to finish and start Job3 and so on                  OR

is it going to start Job1, wait for Job1 to finish, start Job2, wait for Job2 to finish and so on?

Thanks.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9772743
it depends on the value of your job_queue_processes

if you set that parameter to a value greater than 1,
then your jobs will run asynchrosnously

but if you set job_queue_processes = 1

you jobs will run one after another.

0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 9773538
My experience has been that even with job_queue_processes > 1, if these three commands are all issued from one SQL*Plus session, the jobs will sequentially (only one at a time), not concurrently.
0
 

Author Comment

by:mfarid1
ID: 9780718
Interesting. So we can never run jobs sequentially, right?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9780741
I think you mean concurrently, right?

sure you can. as Markgeer suggested, you need to run the jobs from different sqlplus session, then your jobs will run concurrently....
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9780837
in fact, if your job is a long -running one, I think you can also achieve the concurrency even by issuing the commands in one sqlplus session.

say if each of these jobs runs for 10 mins

dbms_job.run(job1);
dbms_job.run(job2);
dbms_job.run(job3);

I think you will get some overlapping period that all the three jobs are running...
you can verify this by querying DBA_JOBS_RUNNING.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 9781382
If your "job_queue_processes > 1" then you can have as many jobs running at the same time as the value for this parameter (assuming that they are all scheduled for the same or nearly the same time).  I don't think you can get them to run at the same time by starting them manually from one SQL*Plus session, at least I've never managed to.
0
 

Author Comment

by:mfarid1
ID: 9781978
markgeer, thank you very much for your response. I am little bit saddened by the fact that I cannot run multiple jobs asynchronously from the same SQL Plus session.  What if a job takes a long time to run? So what you are saying is that, the second job will not start till the first job is finished? However, from another sql script, what if I start multiple SQL Plus sessions and run 1 job through each session? I should be able to do that and in that case the jobs should run simultaneously, right? Also, each of the jobs would be hitting the same table(s). Would that be a performance hinderance? The job_queue_processes parameter, is it per session or for the Oracle instance?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9782125
IMHO, markgeer is incorrect about this:

I just checked and tested in my environment, I kicked off two jobs that are scheduled at exactly the same time, they are running cocurrently indeed.

There are good explanation for this in oracle documentation:

but first to answer your Q, JOB_QUEUE_PROCESSES specifies the number of job queue processes per instance (J000, ... J999).To execute a job, the process creates a separate session to run the job. so they are not interfering with each other at all. Each job is executed in its own PGA.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 9782425
What I said (and still believe to be true) was that it is not possible to *START* multiple jobs at the same time from one SQL*Plus session by running a script like this:
exec dbms_job.run(job1);
exec dbms_job.run(job2);
exec dbms_job.run(job3);

It certainly is possible to have multiple jobs running at the same time *IF* job_queue_processes > 1.  Just set them up to start at the same time, and they will.


0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9782448
right, I think that 's what mfarid1 asked for...
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 9784165
I agree , you have to wait for the previous job completed then run the next if you just have one SQL*Plus session. By scheduling and job_queue_processes > 1 will allow multiple jobs running even start at the same time
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9817662
This is BS.  I provided the COMPLETE, CORRECT solution long ago in this thread.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 9819276
DrJekyll, The asker mentioned that
"PL/SQL does not understand:

VARIABLE jobno number

but it does understand

DECLARE jobno number"

So obviously your answer was not considered the correct answer. Even though I did concur with you earlier
"Comment from anand_2000v
Date: 11/18/2003 09:58AM IST
 Your Comment  

The second comment by DrJykell contained the answer..."

The first *correct* answer for the asker was the one by seazodiac.
0
 
LVL 5

Expert Comment

by:DrJekyll
ID: 9819467
No matter but my first post was the first correct answer.
0
 
LVL 1

Expert Comment

by:jgilbertb
ID: 9820644
I still say DrJekyll had the right solution long ago;

There are to solutions

1. I SQL*PLUS You can say;
variable  njobno number;
then in the dbms_job.submit you prefix njobno with a ":" as in ":njobno"

or

2. in PL/SQL you say;

declare njobno number;
then in the dbms_job.submit you DO NOT prefix njobno with a ":" as in "njobno"

Remember! if you say

Declare...

begin
dbms_job.submit(:njobno, 'execute MyDatabase.MyProcedure;', '17-NOV-2003', NULL);
commit;
end;


do not use :njobno in submit.

0
 
LVL 1

Expert Comment

by:jgilbertb
ID: 9820701
Sorry I hit return befor I finished what I was saying...

Remember! if you say

Declare...
   begin
     .....
   end
at the command promt it is still a PL/SQL block!

So!
 1.  variable  njobno number
     begin
       dbms_job.submit(:njobno, 'execute MyDatabase.MyProcedure;', '17-NOV-2003', NULL);
      commit;
     end;

will work and...

 2.    declare njobno number;
      begin
       dbms_job.submit(njobno, 'execute MyDatabase.MyProcedure;', '17-NOV-2003', NULL);
      commit;
     end;

will also work!

This is why I said in the beginning  the DrJekyll had the solution!

0

Featured Post

Industry Leaders: 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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 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