Re: Truncating Tables using DBMS_SQL

The procedure is okay, the error is in the call. SQL*Plus needs to see quotes around the tablename as it is substituted, i.e.

begin trunctabs('CDDS_CLRV'); end;

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Richelle Hutchinson wrote in message
<7lqs8q$369u$1_at_newssvr03-int.news.prodigy.com>...
>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:11:39 CDT