DBMS_JOB.SUBMIT question

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.
mfarid1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

seazodiacCommented:
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
DrJekyllCommented:
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
mfarid1Author Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

seazodiacCommented:
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
DanielztCommented:

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
mfarid1Author Commented:
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
mfarid1Author Commented:
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
seazodiacCommented:
you should not use :nJobNo

try this:


declare nJobNo number;
begin
   dbms_job.submit(nJobNo, 'MyDatabase.MyProcedure','17-NOV-2003', NULL)
end;
0
seazodiacCommented:
that's because nJobNo is a PL/SQL variable, not a Host variable.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrJekyllCommented:
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
jgilbertbCommented:
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
baonguyen1Commented:
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
anand_2000vCommented:
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
grim_toasterCommented:
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
alokguptCommented:
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
Mark GeerlingsDatabase AdministratorCommented:
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
DrJekyllCommented:
I agree, I know I posted a working example 14 messages ago.<g>
0
mfarid1Author Commented:
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
seazodiacCommented:
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
Mark GeerlingsDatabase AdministratorCommented:
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
mfarid1Author Commented:
Interesting. So we can never run jobs sequentially, right?
0
seazodiacCommented:
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
seazodiacCommented:
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
Mark GeerlingsDatabase AdministratorCommented:
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
mfarid1Author Commented:
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
seazodiacCommented:
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
Mark GeerlingsDatabase AdministratorCommented:
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
seazodiacCommented:
right, I think that 's what mfarid1 asked for...
0
baonguyen1Commented:
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
DrJekyllCommented:
This is BS.  I provided the COMPLETE, CORRECT solution long ago in this thread.
0
anand_2000vCommented:
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
DrJekyllCommented:
No matter but my first post was the first correct answer.
0
jgilbertbCommented:
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
jgilbertbCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.