Re: Truncate table call in procedure

Hi,

 U need to Use dynamic SQL inside your PL/SQL to execute DDL commands.Before writing SQL, Ur DBA should explicitly GRANT TRUNCATE ANY to tabe to that particular user. Here are the steps :

sqlplus system/pass
grant drop any table,truncate any table to SCOTT;

sqlplus scott/tiger

declare
v_cursor NUMBER;
begin
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,'TRUNCATE TABLE Detail',dbms_sql.v7); dbms_sql.close_cursor(v_cursor);
end;

This must work.

Mohamed Buhari
ORACLE DBA Ron Lamb wrote:

> I am having trouble creating a stored procedure in Oracle 7.3.4
>
> The procedure runs once a month and summarizes records in a detail
> table into a summary table and then clears the detail table.
>
> Everything works except that Oracle doesn't seem to allow the truncate
> command in a procedure. I took all the code out except the truncate
> and found that the following procedure definition won't compile
>
> procedure new_month is
> begin
> truncate table Detail;
> end;
>
> but the following will
>
> procedure new_month is
> begin
> delete Detail;
> end;
>
> I would prefer to truncate the table rather than delete the records.
> Can this be done?
>
> Ron
Received on Thu Jan 20 2000 - 15:41:10 CST