Re: Truncate table inside a procedure?

From: Petre Alexandrescu <Petre.Alexandrescu_at_mci.com>
Date: Fri, 30 Oct 1998 12:14:20 -0500
Message-ID: <cqm_1.836$347.3603@news.cwix.com>

You can't use any DDL (truncate, create, drop, alter ...) in PL/SQL directly. If you wont to do so use dbms_sql package for dinamic SQL calls.

Example:

declare

dynam_cursor INTEGER;
dynam_string VARCHAR2(32767);
dynam_status INTEGER;

begin

dynam_string := 'TRUNCATE TABLE TABLE_NAME';

dynam_cursor := DBMS_SQL.open_cursor;
dbms_sql.parse(dynam_cursor, dynam_string, DBMS_SQL.NATIVE);
dynam_status := DBMS_SQL.execute(dynam_cursor);

dbms_sql.close_cursor(dynam_cursor);

end;

Steinar Orset wrote in message <3639E739.58C61893_at_quasar.no>...
>Hello,
>Why can't I truncate a table inside a procedure?
>(However 'delete from temp' works, but it is said to be slower than
>truncate)
>
>SQL> create or replace procedure cut_proc
> 2 is
> 3 begin
> 4 truncate table temp;
> 5 end;
> 6 /
>
>Warning: Procedure created with compilation errors.
>
>Regards Steinar Orset
>
Received on Fri Oct 30 1998 - 11:14:20 CST