asked on

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.

seazodiac

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.

DrJekyll

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;

mfarid1

ASKER

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.

seazodiac

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;

Danielzt

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;

mfarid1

ASKER

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.

mfarid1

ASKER

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?

seazodiac

you should not use :nJobNo

try this:

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

ASKER CERTIFIED SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrJekyll

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;

jgilbertb

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.

baonguyen1

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.

anand_2000v

The second comment by DrJykell contained the answer...
instead of

declare njobno number;
use
variable njobno number;

or

do not use :njobno in submit.

grim_toaster

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.

alokgupt

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

Mark Geerlings

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.

DrJekyll

I agree, I know I posted a working example 14 messages ago.<g>

mfarid1

ASKER

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.

seazodiac

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.

Mark Geerlings

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.

mfarid1

ASKER

Interesting. So we can never run jobs sequentially, right?

seazodiac

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....

seazodiac

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.

Mark Geerlings

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.

mfarid1

ASKER

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?

seazodiac

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.

Mark Geerlings

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.

seazodiac

right, I think that 's what mfarid1 asked for...

baonguyen1

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

DrJekyll

This is BS. I provided the COMPLETE, CORRECT solution long ago in this thread.

anand_2000v

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.

DrJekyll

No matter but my first post was the first correct answer.

jgilbertb

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.

jgilbertb

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!