Re: Need Help with some PLSQL code please

An EXCEPTION raised in the declaration section of a PL/SQL block is not handled by the block's own exception handler. Like these:

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

TempDate VARCHAR2( 50 ) := CHECK_MY_DATE( MyDate ); -- Returns a VARCHAR2 Move the assignments into the body if you want to handle them yourself.

Other comments:

1. Rather than concern yourself with the closing of cursors in the exception handler, why not use a simple FOR loop?

FOR myRow IN MyCursor LOOP

..

END LOOP; (No need to declare MyRow in that case either).

2) I don't like this:

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE( '-----------------------------------------' );

DBMS_OUTPUT.PUT_LINE( 'Unknown Error Occured. ' );

DBMS_OUTPUT.PUT_LINE( '-----------------------------------------' );

The only reason the error is "unknown" is because you've prevented Oracle from telling you what it is! Why not just use RAISE here?

3) Your CHECK_PROCESS_ID function has 2 different return values, but

   your main program doesn't care about the difference.

--
Posted via http://dbforums.com