Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Exception handling for dynamic SQL statement

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US