Re: Truncate table call in procedure

You need to use the dbms_sql package:

sql_curs := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(sql_curs, 'truncate table theowner.thetable',DBMS_SQL.NATIVE);
li_rc := DBMS_SQL.EXECUTE(sql_curs);
DBMS_SQL.CLOSE_CURSOR(sql_curs);

--
Terry Dykstra
Canadian Forest Oil Ltd.
Check out Sybase Developer's Network: http://www.sybase.com/sdn

Ron Lamb <rlamb_at_columbus.rr.com> wrote in message news:867rcj$6cf$1_at_ssauraab-i-1.production.compuserve.com...
> 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 - 14:35:29 CST