Re: Truncate table inside a procedure?

From: Ahmad Mabsout <amabsout_at_sprint.ca>
Date: Sat, 31 Oct 1998 01:01:45 -0800
Message-ID: <5nA_1.1618$qi1.1330578@HME2.newscontent-01.sprint.ca>

Hi ,

You have received the error , 'Warning: Procedure created with compilation errors' because TRUNCARE is a DATA DEFINITION LANGUAGE (DDL) and not a DML.

If you want to use TRUNCATE command inside a database procedure you should use the DBMS_SQL package which is available with PL/SQL 2.1 and higher . You have to take into consideration TRUNCATE does not fire any DELETE triggers

Your procedure should look like

PROCEDURE cut_proc IS

   v_cursor         NUMBER;
   v_trunc_string   VARCHAR2(100);
   v_trunc_flag     INTEGER;

BEGIN
   /* Open the cursor for processing */    v_cursor := DBMS_SQL.OPEN_CURSOR;

   v_trunc_string := 'TRUNCATE TABLE temp';

   /* DBMS_SQL.V7 is a constant defined in the package header */    DBMS_SQL.PARSE(v_cursor,v_trunc_string,DBMS_SQL.V7);

   v_trunc_flag := DBMS_SQL.EXECUTE(v_cursor);

   DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
   WHEN OTHERS THEN

      DBMS_SQL.CLOSE_CURSOR(v_cursor);
      RAISE ;

END cut_proc;

Ahmad Mabsout

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 Sat Oct 31 1998 - 03:01:45 CST