Re: DYNAMIC SQL

From: Scott Shafer <sknd100_at_yahoo.com>
Date: Sun, 09 Sep 2001 18:02:29 -0700
Message-ID: <F001.00387AD8.20010909181018@fatcity.com>

Try commiting (COMMIT;) before your exception clause.

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, September 09, 2001 1:10 PM

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

Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Shafer
  INET: sknd100_at_yahoo.com

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 - 20:02:29 CDT