Exception Handling

From: Greg Akins <gakinsNOgaSPAM_at_gatewayhealthplan.com.invalid>
Date: Tue, 23 Nov 1999 04:45:56 -0800
Message-ID: <000b8d9b.8202f849@usw-ex0101-008.remarq.com>

I have a function which includes two blocks enclosed in a main block.

I thought any error in the first block would be handled, and then the second block would execute. However, my program gets an error in the first block (and returns the appropriate message). Then passes the error to the main block....

Here is the code. It seems to me that I'm misunderstanding what it means to "handle" an error. But I've scoured the Oracle Press _PL/SQL Programming_ book and haven't found an answer.

Any help would be appreciated.

-greg

---

create or replace function ER_VISIT_TEST (startdate in date, enddate in date) return number as
begin /* ER_VISIT_REFRESH */
/*-- Returns 1 for success, 0 for failure*/ Declare
  ddlString varchar2(200) ;
  v_cursor number ;
  v_errorMsg varchar2(200) ;

BEGIN /* Drop Table ER_INITIAL*/

   v_cursor := dbms_sql.open_cursor ;
   ddlString := 'DROP TABLE GAK.er_visit_log CASCADE CONSTRAINTS' ;    dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;    dbms_sql.close_cursor(v_cursor) ;
exception

   when others then

        dbms_output.put_line(sqlcode||' : '||sqlerrm) ; end ;

Declare
  ddlString varchar2(200) ;
  v_cursor number ;
BEGIN /* Drop Table ER_INITIAL*/

   v_cursor := dbms_sql.open_cursor ;

   ddlString := 'CREATE TABLE gak.er_visit_log ( '||chr(10)||


                'app_msg                    VARCHAR2(100), '||chr(10)||


                'app_date                   DATE) '||chr(10)||


                'TABLESPACE master_data PCTUSED 60 PCTFREE 10 ' ;


   dbms_sql.parse(v_cursor, ddlstring, dbms_sql.v7) ;
   dbms_sql.close_cursor(v_cursor) ;
exception
   when others then
      dbms_output.put_line(sqlcode||' : '||sqlerrm) ;

end ;
exception
  when others then
    dbms_output.put_line(sqlcode||' : '||sqlerrm) ;     return 0 ;
end er_visit_test ;

---

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Tue Nov 23 1999 - 06:45:56 CST