Re: Exception handling for dynamic SQL statement

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 26 Mar 1999 12:35:53 GMT
Message-ID: <37017eaf.6204241@192.86.155.100>

A copy of this was sent to "earthlink" <phil_at_amax.com> (if that email address didn't require changing) On Thu, 25 Mar 1999 18:44:45 -0800, you wrote:

>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
>
>

It is not *succeeding* -- its just not printing anything cause you disabled output in the exception handler. Lose the dbms_output.disable in the exception block. Here is an example:

SQL> create or replace procedure execute_immediate( sql_stmt in varchar2 )   2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5  begin
  6      dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
  7      rows_processed := dbms_sql.execute(exec_cursor);
  8      dbms_sql.close_cursor( exec_cursor );

  9 end;
 10 /

Procedure created.

SQL>
SQL> drop table t;

Table dropped.

SQL> create table t ( x int primary key );

Table created.

SQL>
SQL> begin

  2          execute_immediate( 'insert into t values ( 1 )' );
  3          execute_immediate( 'insert into t values ( 1 )' );
  4  exception
  5          when others
  6          then
  7                  dbms_output.enable;
  8                  dbms_output.put_line( 'Caught: ' || sqlerrm );

  9 end;
 10 /
Caught: ORA-00001: unique constraint (TKYTE.SYS_C0040224) violated

PL/SQL procedure successfully completed.

>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
>
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Mar 26 1999 - 06:35:53 CST