why can't i use truncate in pl/sql

[mokesql]

hi!
i`m using t.o.a.d and i want to use the function truncate table in a procedure but the compiler doesent accept this in the usal form. the source code is: truncate table tablename; is there any reason for it or maybe another way to solve the problem or do i have to use delete in t.o.a.d? thnx for the help

kemo

 

[rcurva]

Dynamic SQL is the answer!

If you are using 8i, issue the following command in your procedure:

EXECUTE IMMEDIATE('truncate table table1');

If not 8i, you have to bear with a few more lines:

-- declare the following cariables
V_CURSORID INTEGER;
V_DDLSTRING VARCHAR2(1999);
V_NUMROWS NUMBER;

-- add these lines to your procedure body

V_CURSORID := DBMS_SQL.OPEN_CURSOR;
V_DDLSTRING := 'TRUNCATE TABLE table1';
DBMS_SQL.PARSE(V_CURSORID, V_DDLSTRING, DBMS_SQL.NATIVE);
V_NUMROWS := DBMS_SQL.EXECUTE(V_CURSORID);
DBMS_SQL.CLOSE_CURSOR(V_CURSORID);
COMMIT;

Answer to your question is, TRUNCATE TABLE is a DDL statement, much like the CREATE TABLE statement.

 

[jcote]

I think you need the right to drop a table to be able to truncate it. Have you tried to do the truncate from a simple SQLPLUS session to make sure you have all the permissions you need?