How to use truncate table in PL/SQL?(p26)

A one-line stored procedure:

CREATE OR REPLACE PROCEDURE x1 as
begin
truncate table tablex1;
end;

got this error, 'PLS-00103: Encountered the symbol "TABLE" when
expecting one of the following:   := . ( @ % ;The symbol ":= was
inserted before "TABLE" to continue.'

While connected as the same user, I have no problem to execute this
truncate command in SQL worksheet.
 

Hi Chan


Ans:

Truncate is a DDL statement. DDL needs to be done by using dbms_sql (Oracle
7,8) or dbms_utility.execute_ddl_statement (Oracle 8).
Example (v7)
declare
cur_handle integer;
begin
cur_handle := dbms_sql.open_cursor;
dbms_sql.parse(cur_handle, 'truncate table tablex1', dbms_sql.v7);
dbms_sql.close_cursor(cur_handle);
end;

Hth,
Sybrand Bakker, Oracle DBA
 

Hosted by www.Geocities.ws

1