Need Help with some PLSQL code please

From: Chris \( Val \) <chrisval_at_bigpond.com.au>
Date: Sun, 14 Sep 2003 01:51:20 +1000
Message-ID: <bjvedq$mte60$1@ID-110726.news.uni-berlin.de>

Hi everyone, I'm still a newbie with Oracle and PLSQL, and if possible, I would appreciate your help.

I have written a procedure(Oracle 9i), and two helper functions, to retrieve some records from the database table.

I want to be able to trap all the errors that may occur due to bad input by the user, say foe example if a character was entered instead of a number, or a slip of the finger whilst typing in the date.

In the table DXFREQUEST, the PROCESSED field is a NUMBER type, and the DATE_REQUESTED field is a DATE field, so I would like to trap errors such as typing in:

EXEC FIND_DXF_ERRORS( r, '07-Jul-2003' ); Or... EXEC FIND_DXF_ERRORS( 5, '07-Jyl-2003' ); etc...

Why aren't all the errors getting caught by the 'WHEN OTHERS' exception block ? - I'm not sure how to handle this.

Here is the code I have so far(which sort of works, but I am not too sure with the way I also close the cursor in the exception area if an error occurs ?

Any advice on how to fix, improve or simplify it would be greatly appreciated.

CREATE OR REPLACE PROCEDURE FIND_DXF_ERRORS ( MyNumber DXFREQUEST.PROCESSED%TYPE, MyDate VARCHAR2 ) AS

  MyRow DXFREQUEST%ROWTYPE;

  BadDateEntry EXCEPTION;
  BadIdNumber EXCEPTION;

  CURSOR MyCursor IS SELECT REQUEST_ID, USER_ID, DISTRICT,

                            DATE_REQUESTED, MINX, MINY,
                            MAXX, MAXY, DESTINATION, FILENAME,
                            PROCESSED, REQUEST_TYPE
         FROM DXFREQUEST
         WHERE TRUNC(DATE_REQUESTED) = MyDate
         AND PROCESSED = MyNumber;

  TempID VARCHAR2( 50 )   := CHECK_PROCESS_ID( MyNumber ); -- Returns a VARCHAR2
  TempDate VARCHAR2( 50 ) := CHECK_MY_DATE( MyDate );      -- Returns a VARCHAR2

  MyFlag BOOLEAN := FALSE;
  Counter NUMBER( 5 ) := 0;

BEGIN   IF TempID = 'BAD_INPUT' OR TempID = 'OUT_OF_RANGE' THEN

     RAISE BadIdNumber;
  END IF;   IF TempDate = 'FALSE' THEN

     RAISE BadDateEntry;
  END IF;

     OPEN MyCursor;
     DBMS_OUTPUT.ENABLE( 1000000 );

     DBMS_OUTPUT.PUT_LINE( '-----------------------------------------------------' );
     DBMS_OUTPUT.PUT_LINE( RPAD( 'RECORD', 8, ' ' ) || RPAD( 'USER_ID', 12, ' ' ) ||
                           RPAD( 'DISTRICT', 12, ' ' ) || RPAD( 'MINX', 12, ' ' ) ||
                           RPAD( 'MINY', 12, ' ' ) || RPAD( 'MAXX', 12, ' ' ) ||
                           RPAD( 'MAXY', 12, ' ' ) );
     DBMS_OUTPUT.PUT_LINE( '-----------------------------------------------------' );

     LOOP

       IF MyNumber = MyRow.PROCESSED THEN
          DBMS_OUTPUT.PUT_LINE( RPAD( Counter, 8, ' ' ) ||
                                RPAD( MyRow.USER_ID, 12, ' ' ) ||
                                RPAD( SUBSTR(MyRow.DISTRICT, 0, 8 ), 12, ' ' ) ||
                                RPAD( SUBSTR(MyRow.MINX, 0, 8 ), 12, ' ' ) ||
                                RPAD( SUBSTR(MyRow.MINY, 0, 8 ), 12, ' ' ) ||
                                RPAD( SUBSTR(MyRow.MAXX, 0, 8 ), 12, ' ' ) ||
                                RPAD( SUBSTR(MyRow.MAXY, 0, 8 ), 12, ' ' ) );
       END IF;

       FETCH MyCursor INTO MyRow;

       IF MyCursor%NOTFOUND THEN
          MyFlag := TRUE;
          EXIT;
       END IF;

       Counter := Counter + 1;

     END LOOP;

   CLOSE MyCursor;

EXCEPTION
   WHEN BadDateEntry THEN

        DBMS_OUTPUT.PUT_LINE( '-----------------------------------------' );
        DBMS_OUTPUT.PUT_LINE( 'INCORRECT DATE FORMAT ENTERED:            ');
        DBMS_OUTPUT.PUT_LINE( '-----------------------------------------' );

   WHEN BadIdNumber THEN
        DBMS_OUTPUT.PUT_LINE( '-----------------------------------------' );
        DBMS_OUTPUT.PUT_LINE( 'INCORRECT ID NUMBER ENTERED: ' || TempID   );
        DBMS_OUTPUT.PUT_LINE( '-----------------------------------------' );

   WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( '-----------------------------------------' );
        DBMS_OUTPUT.PUT_LINE( 'Unknown Error Occured. ' );
        DBMS_OUTPUT.PUT_LINE( '-----------------------------------------' );

• Not sure if I should have this close statement here ?
• What is the best way to do this ? CLOSE MyCursor;

END;
/

CHECK_MY_DATE( MyDate IN VARCHAR2 ) RETURN VARCHAR2 IS

  TempDate DATE;

  BEGIN

     TempDate := TO_DATE( MyDate, 'DD-MON-YYYY' );
        RETURN 'TRUE';

  EXCEPTION
     WHEN OTHERS THEN    -- If any exception occurs during the
        RETURN 'FALSE';  -- date conversion, then return FALSE

  END;
/

CHECK_PROCESS_ID( MyProcessID IN VARCHAR2 ) RETURN VARCHAR2 IS

  BEGIN

     IF TO_NUMBER(MyProcessID) < 0 OR
        TO_NUMBER(MyProcessID) > 15 THEN
           RETURN 'OUT_OF_RANGE';
     END IF;

 RETURN MyTempNumber;

  EXCEPTION

     WHEN OTHERS THEN        -- If any exception occurs during the
        RETURN 'BAD_INPUT';  -- date conversion, then return BAD_INPUT

  END;
/

Thanks.
Chris Val Received on Sat Sep 13 2003 - 10:51:20 CDT