Lance,
Use the in_tab_name not the table you want to truncate. You specify table to truncate when you call the procedure. See example below using PLAN_TABLE as the table to truncate.
1 CREATE OR REPLACE procedure primus_temp_dump_test(in_tab_name in varchar2)
2 as
3 cursor_id integer;
4 execute_return_value integer;
5 last_function_code integer;
6 Begin
7 cursor_id := dbms_sql.open_cursor;
8 dbms_sql.parse (cursor_id,'TRUNCATE TABLE '||in_tab_name,dbms_sql.v7);
9 execute_return_value := dbms_sql.execute(cursor_id);
10 last_function_code := dbms_sql.last_sql_function_code;
11 dbms_sql.close_cursor(cursor_id);
12* End;
SQL> /
Procedure created.
SQL> exec primus_temp_dump_test('PLAN_TABLE');
PL/SQL procedure successfully completed.
SQL> select count(*) from plan_table;
COUNT(*)
---------
0
-----Original Message-----
From: Lance Prais [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 13, 2002 7:38 PM
To: Multiple recipients of list ORACLE-L
Subject: PLEASE HELP WITH STATEMENT.
I am using the following statement in Oracle 8.0.6 and getting an error I
and I do not understand why it is giving it to me.
CODE:
CREATE OR REPLACE procedure primus_temp_dump_test(in_tab_name in varchar2)
as
cursor_id integer;
execute_return_value integer;
last_function_code integer;
Begin
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse (cursor_id,'TRUNCATE TABLE
'||UPPER(PRIMUS_TEMP_DUMP),dbms_sql.v7);
execute_return_value := dbms_sql.execute(cursor_id);
last_function_code := dbms_sql.last_sql_function_code;
dbms_sql.close_cursor(cursor_id);
End;
/
Error:
*
ERROR at line 1:
ORA-06550: line 1, column 29:
PLS-00357: Table,View Or Sequence reference 'PRIMUS_TEMP_DUMP' not allowed
in
this context
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Thank you in advance.
Lance
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lance Prais
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).
