error while truncating tables from oracle db

Question

I am doing something like this in a procedure to clear all data from all tables in my database.

LOOP
    dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
  END LOOP;
 .
 .
 .

LOOP
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || t.table_name ;
  END LOOP;

Now , this throws the following error :

ORA-03291: Invalid truncate option - missing STORAGE keyword
ORA-06512: at "MYSCHEMA.CLEAR_DATA", line 15
ORA-06512: at line 2
Process exited.
Disconnecting from the database MYDB.

1. Why is a storage keyword mandatory? I thought DROP STORAGE was the default.

2. Even specifying storage close, as in,

   EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || t.table_name || 'DROP STORAGE';

   doesn't help. The error is the same.

3. I thought it might have something to do with foreign constraints on some tables. Hence, the 'disable constraint' earlier in the script

Answer 1

I would suggest that you build the command you are executing in a string variable, output it using dbms_output, then execute it. This way you will see exactly what it is trying to execute that is generating the error.

One thing that could be causing this error is if you have a table name with a space in it (yes, it's possible). The solution if that is the case is to wrap the table name in double quotes.

dev> create table "dave exp" (x number);

Table created.

dev> truncate table dave exp;
truncate table dave exp
                    *
ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword

dev> truncate table "dave exp";

Table truncated.

Answer 2

Change your program:

1. put your truncate command in a PL/SQL variable prior to execution
2. add an exception handler that outputs the truncate statements via dbms_output or utl_file (fflush after each one) when you encounter an exception:

LOOP 
  BEGIN
      ...
    v_sql := 'TRUNCATE TABLE ' || t.table_name ;
    EXECUTE IMMEDIATE v_sql;
  EXCEPTION
    WHEN OTHERS THEN
       dbms_output.put_line(SQLERRM);
       dbms_output.put_line(v_sql);
  END;
END LOOP;

This should show you the statement causing the issue.