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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
