Truncating Tables using DBMS_SQL

I've been trying to run the following 'TRUNCATE TABLE' table procedure but have run into some
runtime errors. I'm passing the table name 'CDDS_CLRV' to the procedure throught the parameter
'table_name_in'. This procedure works if I hardcode the table name, but if I pass it in our define it
locally I get the same error. Any assistance would be greatly . -Richelle

• Stored Procedure Script

  ---

  CREATE OR REPLACE PROCEDURE trunctabs (table_name_in IN VARCHAR2) AS cid INTEGER; BEGIN
  /* Open new cursor and return cursor ID. */
  cid := DBMS_SQL.OPEN_CURSOR;
  /* Parse and immediately execute dynamic SQL statement built by
  concatenating table name to TRUNCATE TABLE command */ DBMS_SQL.PARSE(cid, 'TRUNCATE TABLE ' || table_name_in, DBMS_SQL.NATIVE);
  /* Close cursor. */
  DBMS_SQL.CLOSE_CURSOR(cid); EXCEPTION
  /* If an exception is raised, close cursor before exiting */
  WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cid); RAISE; END trunctabs; /
  • Results ********************** Enter value for 1: old 1: begin trunctabs(&1); end;

new 1: begin trunctabs(CDDS_CLRV); end;

begin trunctabs(CDDS_CLRV); end;

*
ERROR at line 1:
ORA-06550: line 1, column 17:
PLS-00357: Table,View Or Sequence reference 'CDDS_CLRV' not allowed in this context
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Elapsed: 00:00:00.16 Received on Mon Jul 05 1999 - 13:06:40 CDT