Re: Pl/Sql and Win NT

From: <esiyuri_at_my-dejanews.com>
Date: 1998/12/03
Message-ID: <745rov$kqs$1@nnrp1.dejanews.com>#1/1

In article <01be1e01$9db46b40$ef5795c1_at_roark>,   "Jim" <lermontovb_at_hotmail.com> wrote:
> I believe the version of Oracle server you're using has a built in package
> called UTL_File which allows Oracle to write to the file system...
> > How do I pass a return code from PL/SQL to the operating system ?
> >
> > Other than using Pro*C, Pro*..., is there any technique to let the O/S
> > know about the results of executing a given stored procedure ?
> >
> > My O/S is Win NT 4 and Oracle ver 7.3.4

You could use the UTL_FILE package as suggested, but I think that it would be easier to just check the operating system return code provided by sql*plus.

On UNIX using "ksh" this is the "$?" variable, on NT (DOS) it is the "%ERRORLEVEL%" variable. The following scripts illustrate it's usage...

TEST.BAT:

------------8<-----------------8<----------------8<-----------------

@ECHO OFF
sqlplus SCOTT/TIGER_at_ORCL @test.sql
ECHO ERRORLEVEL = %ERRORLEVEL%
ECHO X========= TEST.LST =========X
TYPE TEST.LST

ECHO X============================X
------------8<-----------------8<----------------8<-----------------

TEST.SQL:

------------8<-----------------8<----------------8<-----------------

whenever sqlerror exit 1
set serverout on
spool test

DECLARE
  v_seconds NUMBER;
  e_DUMMY EXCEPTION;
BEGIN
  SELECT TO_NUMBER(TO_CHAR(SYSDATE,'SS'))   INTO v_seconds
  FROM DUAL;   DBMS_OUTPUT.PUT_LINE('v_seconds='||TO_CHAR(v_seconds));

  IF v_seconds > 30 THEN
    RAISE e_DUMMY;
  END IF;   DBMS_OUTPUT.PUT_LINE('TEST: FINISHED OK'); EXCEPTION
  WHEN e_DUMMY THEN
    RAISE_APPLICATION_ERROR(-20001, 'TEST: DUMMY EXCEPTION DETECTED'); END;
/

spool off
exit 0

------------8<-----------------8<----------------8<-----------------

OUTPUT RUN 1 (SUCCESS):
C:\>test
ERRORLEVEL = 0
X========= TEST.LST =========X
v_seconds=22
TEST: FINISHED OK
PL/SQL procedure successfully completed.

X============================X

OUTPUT RUN 2 (UNHANDLED EXCEPTION):
C:\>test
ERRORLEVEL = 1
X========= TEST.LST =========X
v_seconds=31
DECLARE
*
ERROR at line 1:
ORA-20001: TEST: DUMMY EXCEPTION DETECTED ORA-06512: at line 19

X============================X

DESCRIPTION: The TEST.SQL script is called from the TEST.BAT batch script. The script checks the system time and fails if the number of seconds past the current minute is more than 30 - ie: it will fail about half the times that it is called. By using "whenever sqlerror exit 1" we ensure that SQL*PLUS returns an error code of 1 if there is an error. The default error code (success) is 0.

I hope this helps.

--
Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    

Received on Thu Dec 03 1998 - 00:00:00 CST