OraFAQ Forum: SQL & PL/SQL » The "What" parameter in DBMS

Home » SQL & PL/SQL » SQL & PL/SQL » The "What" parameter in DBMS_JOB.SUBMIT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

The "What" parameter in DBMS_JOB.SUBMIT [message #201131]

Thu, 02 November 2006 13:32

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

Report message to a moderator

Re: The "What" parameter in DBMS_JOB.SUBMIT [message #201145 is a reply to message #201131]

Thu, 02 November 2006 15:52

Littlefoot
Messages: 21823
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;'

Report message to a moderator

Re: The "What" parameter in DBMS_JOB.SUBMIT [message #201148 is a reply to message #201145]

Thu, 02 November 2006 16:11

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

Report message to a moderator

Re: The "What" parameter in DBMS_JOB.SUBMIT [message #201168 is a reply to message #201148]

Thu, 02 November 2006 19:22

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.

Report message to a moderator

Re: The "What" parameter in DBMS_JOB.SUBMIT [message #201298 is a reply to message #201168]

Fri, 03 November 2006 07:18

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

Report message to a moderator

Previous Topic:	sysdate
Next Topic:	Using WHEN OTHERS in Trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 04:40:14 CST 2024