Home » SQL & PL/SQL » SQL & PL/SQL » dbms_jobs failed with ORA-06550
dbms_jobs failed with ORA-06550 [message #302145] Sun, 24 February 2008 02:50 Go to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,
I'm trying to schedule a job with the following:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job => X
,what => 'PREP.CREATE_SPL_BED;'
,next_date => to_date('02/25/2008 01:00:00','mm/dd/yyyy hh24:mi:ss')
,no_parse => FALSE
);
:JobNumber := to_char(X);
END;

and it failed with:

DECLARE
*
ERROR at line 1:
ORA-06550: line 1, column 98:
PLS-00302: component 'CREATE_SPLBED' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 4
Re: dbms_jobs failed with ORA-06550 [message #302147 is a reply to message #302145] Sun, 24 February 2008 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must have direct execute privilege on the procedure (which must exist).

Regards
Michel
Re: dbms_jobs failed with ORA-06550 [message #302206 is a reply to message #302145] Sun, 24 February 2008 21:56 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,
I did grant exectute on that procedure to sys and tried to schedule the job but sill getting the same error.
Re: dbms_jobs failed with ORA-06550 [message #302209 is a reply to message #302145] Sun, 24 February 2008 22:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Too bad you can't spell

CREATE_SPL_BED<>CREATE_SPLBED

Should there be 1 or 2 underscore characters
Re: dbms_jobs failed with ORA-06550 [message #302245 is a reply to message #302145] Mon, 25 February 2008 00:55 Go to previous messageGo to next message
caprikar
Messages: 226
Registered: March 2007
Senior Member
Hi,
That was typo, it was
DECLARE
*
ERROR at line 1:
ORA-06550: line 1, column 98:
PLS-00302: component 'CREATE_SPL_BED' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 4 
Re: dbms_jobs failed with ORA-06550 [message #302247 is a reply to message #302206] Mon, 25 February 2008 00:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
caprikar wrote on Mon, 25 February 2008 04:56
Hi,
I did grant exectute on that procedure to sys and tried to schedule the job but sill getting the same error.

There really is no need to grant anything to sys, as
- sys already has grants on anything
- you should never ever use sys.

Typo??
Do you mean you retyped the complete error-message? Why did you not copy-paste it?

[Updated on: Mon, 25 February 2008 01:00]

Report message to a moderator

Re: dbms_jobs failed with ORA-06550 [message #302249 is a reply to message #302206] Mon, 25 February 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I did grant exectute on that procedure to sys

Do you mean you create your job as SYS? Very bad idea.

Regards
Michel
Re: dbms_jobs failed with ORA-06550 [message #302328 is a reply to message #302249] Mon, 25 February 2008 04:40 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I did grant execute on that procedure to sys

Moreover, SYS doesn't need your tiny GRANT to execute the procedure. It is almighty:
SQL> conn scott@ora10
Enter password:
Connected.
SQL> create or replace procedure prc_test is
  2  begin
  3    dbms_output.put_line('This is a test');
  4  end;
  5  /

Procedure created.

SQL> conn sys@ora10 as sysdba
Enter password:
Connected.
SQL> set serveroutput on
SQL> exec scott.prc_test
This is a test

PL/SQL procedure successfully completed.

SQL>
Re: dbms_jobs failed with ORA-06550 [message #302376 is a reply to message #302247] Mon, 25 February 2008 07:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Mon, 25 February 2008 07:59

There really is no need to grant anything to sys, as
- sys already has grants on anything
...


Well spotted, LF Razz
Re: dbms_jobs failed with ORA-06550 [message #302438 is a reply to message #302376] Mon, 25 February 2008 12:06 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, yes, you are right (sort of), but my words are more ... poetic, so - if OP's sensitive heart feels these gentle vibrations, perhaps he'll melt like icecream on a hot tin roof and better understand what we meant. ./fa/1597/0/

The truth is: I didn't read the thread carefully and didn't see what you've said. I'm sorry ./fa/1637/0/

Re: dbms_jobs failed with ORA-06550 [message #302504 is a reply to message #302438] Mon, 25 February 2008 23:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Littlefoot wrote on Mon, 25 February 2008 19:06
Well, yes, you are right (sort of), but my words are more ... poetic, so - if OP's sensitive heart feels these gentle vibrations, perhaps he'll melt like icecream on a hot tin roof and better understand what we meant.


So true, so true. Sorry for my rude intervention of this masterpiece in poetry. I was but too blind to see the majestic pattersn, too deaf to hear those gorgeous tones, too unworthy to recognize the sheer class of it all...
(bow) (bow) (bow)

**crawling back under rock
Re: dbms_jobs failed with ORA-06550 [message #302552 is a reply to message #302504] Tue, 26 February 2008 02:23 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LOL!!!
Previous Topic: HELP IN NOT LIKE / INSTEAD OF NOT LIKE
Next Topic: How to reduce the column length (alternative way) ?
Goto Forum:
  


Current Time: Sun Dec 04 16:34:45 CST 2016

Total time taken to generate the page: 0.07611 seconds