RE: DYNAMIC SQL - Please mod request to trap error when insert fa

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 10 Sep 2001 05:26:14 -0700
Message-ID: <F001.00387E22.20010910045535@fatcity.com>

Al,

did you issue a "set serveroutput on" before you tried executing the procedure to see if your proc worked ok?

your procedure looks ok, the only other thing I would check is to run the select statement outside of the proc to be sure that records are selected ok.

one other thing - depending on what version of Oracle you are running, you could try and run the new version of dynamic sql.

you could change your statement to:

BEGIN

  execute immediate '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 ;

g_rows_inserted := sql%rowcount;
dbms_output.put_line ('ROWS INSERTED: ' || g_rows_inserted);

EXCEPTION
WHEN OTHERS THEN
RAISE;
END PopScanContract;

hope this helps

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Sunday, September 09, 2001 2:20 PM To: Multiple recipients of list ORACLE-L 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_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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------