DYNAMIC SQL - Please mod request to trap error when insert fails

From: Rusnak, George A. <rusnakga_at_hqlee.deca.mil>
Date: Sun, 09 Sep 2001 10:32:54 -0700
Message-ID: <F001.0038799B.20010909102017@fatcity.com>

I CREATED PROCEDURE: CREATE OR REPLACE PROCEDURE PopScanContract (

     p_table IN   VARCHAR2,
     p_ffs   IN   VARCHAR2)  IS
  g_statement_txt      VARCHAR2(500);
  g_cursor_id_num      PLS_INTEGER;
  g_rows_inserted      PLS_INTEGER  := 0;

BEGIN
  g_cursor_id_num := DBMS_SQL.OPEN_CURSOR;

  g_statement_txt := 'INSERT INTO scan_contract ' ||


                     'SELECT CONTRACT_BEGIN_DATE, ' ||

                     'NSN, CONTRACT, CONTRACT_END_DATE, ' ||

                     'FUTURE_EFF_DATE, FUTURE_SELL_PRICE, ' ||

                     'SELL_PRICE, UPDATE_DATE, DODAAC, ' ||

                     p_ffs ||


                     ' FROM ' || p_table ;

  DBMS_SQL.PARSE(g_cursor_id_num, 
                 g_statement_txt,
                 DBMS_SQL.NATIVE);

  g_rows_inserted := DBMS_SQL.EXECUTE(g_cursor_id_num);  dbms_output.put_line ('ROWS INSERTED: ' || g_rows_inserted);   DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num); EXCEPTION
   WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(g_cursor_id_num) THEN

       DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num);     END IF;
    RAISE;
END PopScanContract;

I EXECUTE AS:
    exec PopScanContract('sm_contract_rge', 'RGE')

I GET:
    PL/SQL procedure successfully completed.

YET:
    The table 'scan_contract' still contains the same number of rows AFTER the procedure executes As BEFORE the procedure executed.

ANY HELP WILL BE GREATLY APPRECIATED !!!!!! TIA Al Rusnak
804-734-8453
rusnakga_at_hqlee.deca.mil

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rusnak, George A.
  INET: rusnakga_at_hqlee.deca.mil

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sun Sep 09 2001 - 12:32:54 CDT