Re: Oracle MVS - Passing return codes from a procedure

stevec_at_zimmer.CSUFresno.EDU (Steve Cosner) wrote:
> (RM Fischer) writes:
>>Any users of Oracle on MVS - would like to know a "simple" way to pass a
>>return code to MVS in the event of an error from a stored procedure and/or
>>SQL*Loader script, to result in a non-zero condition code.
>
>There is no simple way, as far as I can see. We have developed a
>number of loader jobs and SQL/Plus jobs used in a conversion, and
>explored the same questions you have. But we found nothing to help
>us. We just have to scan the job output to see if it worked each
>time.

Oracle Stored Procedures don't return to MVS. For practical purposes they can be viewed as returning to the invoking application, and they can set an error code in the "user defined" range (-20000 to -20999) by calling a standard procedure (raise_application_error, I think). This number is passed back to the application just like any SQL error.

The next question is: What can SQL*Loader or SQL*Plus do with such an error? I believe with SQL*Plus 3.1 or higher you can use the EXIT statement to return a value of your choice (including a value in a SQL*Plus variable) to the caller, and this value appears as the MVS return code.

With SQL*Loader you do not have that flexibility. In MVS, a failed SQL*Loader run should return a 4 or an 8 depending on the error circumstances. But this is enough to tell whether a load succeeded or not.

/b

-- 
Bill Manry  -  Mainframe and Integration Technologies  -  Oracle Corporation
Standard disclaimer applies.