Solved

DBMS_JOB.SUBMIT question

Posted on 2003-11-17
34
12,842 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
  • 9
  • 6
  • 5
  • +7
34 Comments
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
that's because nJobNo is a PL/SQL variable, not a Host variable.
0
 
LVL 5

Expert Comment

by:DrJekyll
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
I agree, I know I posted a working example 14 messages ago.<g>
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mfarid1
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
Interesting. So we can never run jobs sequentially, right?
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
right, I think that 's what mfarid1 asked for...
0
 
LVL 8

Expert Comment

by:baonguyen1
Comment Utility
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
Comment Utility
This is BS.  I provided the COMPLETE, CORRECT solution long ago in this thread.
0
 
LVL 13

Expert Comment

by:anand_2000v
Comment Utility
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
Comment Utility
No matter but my first post was the first correct answer.
0
 
LVL 1

Expert Comment

by:jgilbertb
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

728 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

10 Experts available now in Live!

Get 1:1 Help Now