Re: DBMS_JOB: Getting error return status from submit()?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 29 Jun 1998 17:11:12 GMT
Message-ID: <3598ca71.4923189@192.86.155.100>

A copy of this was sent to pooe_at_data.fls.dk (Poul Oestergaard/PoOe) (if that email address didn't require changing) On Mon, 29 Jun 1998 14:58:35 GMT, you wrote:

>The submit() procedure in DBMS_JOB returns no error code.
>
>If, e. g., a non-existing procedure is specified as the
>second parameter (the 'what' parameter) to submit(), no
>error return code is available even if submit() fails.
>How is it possible to handle such an error situation,
>e. g. if submit() is called from a package?
>An exception handler won't catch it.
>
>Regards
>Poul Ostergaard
>EDS Denmark
>E-mail (remove XYZ): pooeXYZ_at_eds.dk

Are you setting NO_PARSE (an input to dbms_job.submit) to TRUE? If so, set it to false and dbms_job will parse the input string to validate it at submission time. It WILL through an exception and the exception contains the sql error code. For example:

SQL> declare
  2 junk number;
  3 begin
  4 dbms_job.submit( junk, 'non_existent_routine;' );   5 end;
  6 /
declare
*
ERROR at line 1:

ORA-06550: line 1, column 93:
PLS-00201: identifier 'NON_EXISTENT_ROUTINE' must be declared
ORA-06550: line 1, column 93:

PL/SQL: Statement ignored

ORA-06512: at "SYS.DBMS_JOB", line 71
ORA-06512: at "SYS.DBMS_JOB", line 121
ORA-06512: at line 4

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jun 29 1998 - 12:11:12 CDT