Home » SQL & PL/SQL » SQL & PL/SQL » The "What" parameter in DBMS_JOB.SUBMIT
The "What" parameter in DBMS_JOB.SUBMIT [message #201131] Thu, 02 November 2006 13:32 Go to next message
pmdorafaqs
Messages: 3
Registered: November 2006
Location: Multimax, Inc. Dayton Ohi...
Junior Member
I am unable to submit a job (??) using dbms_job.submit.

I had written a program called:
my_program.sql

and it is located on the server in a directory folder called TEST PROGRAMS. The program executes fine. I then wrote another program in order to submit the job to the Oracle job queue via DBMS_JOB.SUBMIT.

When using DBMS_JOB.SUBMIT, I place the following for the "what" parameter:

'BEGIN my_program; END;'

The error I get (I'm using Oracle 9.2 and TOAD 8.6.1.0) is :

PLS-00201: identifier 'my_program' must be declared

Please help me understand why it is thinking that I have not declared it. I also do not understand the concept of "stored procedure" in relation to the "what" parameter. I've also tried using 'BEGIN my_program.sql; END;' But it really hiccups over the .sql !!!

Thanks!
Phil DelTurco
Re: The "What" parameter in DBMS_JOB.SUBMIT [message #201145 is a reply to message #201131] Thu, 02 November 2006 15:52 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, you have MY_PROGRAM.SQL, a file on a disk. Submitting a job worked fine.

Now you want to submit a stored procedure whose name is 'my_program'. Do you have it (I mean, does a procedure 'my_program' exists in your database)? If so, it shouldn't be too difficult to submit it. The only catch is that the procedure name must end with a semi-colon, for example

WHAT => 'my_program;'
Re: The "What" parameter in DBMS_JOB.SUBMIT [message #201148 is a reply to message #201145] Thu, 02 November 2006 16:11 Go to previous messageGo to next message
pmdorafaqs
Messages: 3
Registered: November 2006
Location: Multimax, Inc. Dayton Ohi...
Junior Member
I think the problem is that my_program is not a procedure.... it's a program. In fact when I check the database, only the other procedures I compiled and executed are in the database schema. my_program is not. The only other thing I know to do is to convert the my_program program into a my_program procedure. That way it will have it's own name and can be placed in the schema under that name. Let me know if you agree or not.

Thanks!

Phil DelTurco
LOGTEC Contractor
System Engineer
Technical Support
Re: The "What" parameter in DBMS_JOB.SUBMIT [message #201168 is a reply to message #201148] Thu, 02 November 2006 19:22 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Yes, you will need to create a procedure that contains the logic in your .sql file - and then refer to the procedure in the dbms_job call.
Re: The "What" parameter in DBMS_JOB.SUBMIT [message #201298 is a reply to message #201168] Fri, 03 November 2006 07:18 Go to previous message
pmdorafaqs
Messages: 3
Registered: November 2006
Location: Multimax, Inc. Dayton Ohi...
Junior Member
Ok, thanks for your help. My gut feeling was that I would have to change it into a procedure.

Phil DelTurco
Previous Topic: sysdate
Next Topic: Using WHEN OTHERS in Trigger
Goto Forum:
  


Current Time: Sun Dec 04 06:40:30 CST 2016

Total time taken to generate the page: 0.09323 seconds