Truncate Table in PL/SQL : TRUNCTABLE

From: <darkplasma_at_hotmail.com>
Date: Wed, 17 Nov 1999 20:07:25 GMT
Message-ID: <80v1tq$bq2$1@nnrp1.deja.com>

I recommend you to use a function like this one :

FUNCTION TRUNCTABLE (t1 VARCHAR2) RETURN VARCHAR2 IS Cursor1 INTEGER;
  BEGIN

	Cursor1 := DBMS_SQL.OPEN_CURSOR ;
	DBMS_SQL.PARSE (Cursor1, 'TRUNCATE TABLE ' || t1, DBMS_SQL.V7) ;
	DBMS_SQL.CLOSE_CURSOR (Cursor1) ;
	RETURN ('OK') ;
  EXCEPTION
	WHEN OTHERS THEN
		DBMS_SQL.CLOSE_CURSOR (Cursor1) ;
		RETURN ('Erreur Fatale TRUNCTABLE (''' || t1

|| ''');' ) ;
  END ; It then allows to speed a lot the complete DELETE of tables.

Good use of this...

In article <XNBX3.50911$s17.526662_at_typhoon.libero.it>,   "Antonio Sant" <asant_at_iol.it> wrote:
>
> Igor A. Feoktistov ha scritto nel messaggio
> <80lvf2$8qn$1_at_dream.ins.dn.ua>...
> >dgmenace <dgmenace_at_netscape.net> wrote:
> >> TRUNCATE is a SQL*Plus command that can not be executed in PL/SQL
> >No, you are mistake. TRUNCATE is DDL command and can't be executed in
> PL/SQL
>
> You must use Dynamic-SQL if you want to use TRUNCATE TABLE!
>
> Something like:
>
> sql_string LONG :='TRUNCATE TABLE T1';
>
> dbms_sql.parse(my_cursor,sql_string,dbms_sql.native);
> ret_rows:=dbms_sql.execute(my_cursor);
>
> Look at the manual for more info!
>
> Bye!
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 17 1999 - 14:07:25 CST