Re: Truncating Tables using DBMS_SQL

A copy of this was sent to "Richelle Hutchinson" <rhutchin_at_prodigy.net> (if that email address didn't require changing) On Mon, 5 Jul 1999 14:06:40 -0400, you wrote:

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

You have to pass a STRING to the procedure. try:

begin trunctable( '&1' ); end;

note the quotes....

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jul 05 1999 - 13:21:30 CDT