Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Truncate within stored procedures

Truncate within stored procedures

From: <jflipse_at_spacestar.com>
Date: Mon, 05 Oct 1998 03:26:17 GMT
Message-ID: <6v9e8o$b31$1@nnrp1.dejanews.com>


I am able to truncate tables within a stored procedure by passing the table name (mytable) to a procedure which performs the truncate (truncate_table), as in

        truncate_table('mytable')

where procedure truncate_table is

        create or replace procedure truncate_table (tablename varchar2) as

	v_crsr := dbms_sql.open_cursor;
	dbms_sql.parse(v_crsr, 'truncate table ' || tablename, DBMS_SQL.V7);
	v_rtn := dbms_sql.execute(v_crsr);
	dbms_sql.close_cursor(v_crsr);
 	END truncate_table

I have no problems when the table name is explicitly called out, but run into problems when the table name is a synonym. I've tried to bind the synonym to a variable and pass this to the procedure like this:

	v_crsr := dbms_sql.open_cursor
	dbms_sql.bind_variable(v_crsr, ':v_tbl', 'mysynonym')
	truncate_table(v_tbl)
	dbms_sql.close_cursor(v_crsr);

but am not having much luck, in that I receive the following error:

        ORA-01003: no statement parsed

Any thoughts on how to get this to work? Thanks in advance!

John Flipse

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Oct 04 1998 - 22:26:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US