"Chris ( Val )" <chrisval_at_bigpond.com.au> wrote in message
news:bjvedq$mte60$1_at_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: ' ||
mpID );
> 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
>
>
Don't use character for date, use date for date. Then you do not have to
check if the string looks like a date. Also the dbms_output will not show
up for the user. It might show up for you in sqlplus, but not the user.
Also use implicit cursors, much easier to code, faster. (see
asktom.oracle.com and look up implicit cursors)
Jim
Received on Sat Sep 13 2003 - 11:04:30 CDT
