Exception handling for dynamic SQL statement

From: earthlink <phil_at_amax.com>
Date: Thu, 25 Mar 1999 18:44:45 -0800
Message-ID: <7desjv$4tb$1@birch.prod.itd.earthlink.net>

Hi,

is anyone know how to make exception handling for dynamic PL/SQL statement?

it seems my exception handling cannot catch the error when my insert statement get error. It just leave as program success. But if I turn off the exception section, it will give me error report

ERROR at line 1:

ORA-01401: inserted value too large for column
ORA-06512: at "SYSADM.RU2", line 84
ORA-06512: at line 1
ORA-00001: unique constraint (SYSADM.PS_MESSAGE_LOG) violated

following is my code.

Thanks for your help!!

Phil

PROCEDURE RU2
(
...parameters...
)
IS
/* declaration */

/*--------------------------------------------------------*/
BEGIN


     /* open a cursor for this next object */


     cursor_handle := DBMS_SQL.OPEN_CURSOR;


     /* Construct the SQL statement and parse it. */


     DBMS_SQL.PARSE(
         cursor_handle,
          'INSERT INTO PS_MESSAGE_LOG (....) VALUES (...);
     DBMS_SQL.BIND_VARIABLE(...);
     .....
     ReturnCode := DBMS_SQL.EXECUTE(cursor_handle);
     DBMS_SQL.CLOSE_CURSOR(cursor_handle);
---------------------------------------------------

EXCEPTION

---

  WHEN OTHERS
  THEN

        DBMS_OUTPUT.ENABLE;
        DBMS_OUTPUT.PUT_LINE('some error message here');
        DBMS_OUTPUT.DISABLE;

END; --end of procedure RU2 Received on Thu Mar 25 1999 - 20:44:45 CST