Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> DYNAMIC SQL - Please mod request to trap error when insert fails
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_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);
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