Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Truncate Table Not Allowed in PL/SQL?

165 views
Skip to first unread message

Hi

unread,
May 11, 1999, 3:00:00 AM5/11/99
to
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


--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---

Sybrand Bakker

unread,
May 11, 1999, 3:00:00 AM5/11/99
to
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


Hi wrote in message <7ha3gm$itb$1...@nnrp1.deja.com>...

sganesh

unread,
May 12, 1999, 3:00:00 AM5/12/99
to

No way!! You cannot directly execute a ddl from any stored proc. You can use wither dbms_sql package to fire dynamic sqls to do this or try using execute_ddl()
Sanjay

Big Bear

unread,
May 13, 1999, 3:00:00 AM5/13/99
to
yeah same here. they say you can't use DDL in store proc. see replies
to my post dt 10th may

AleX

unread,
May 13, 1999, 3:00:00 AM5/13/99
to
In article <926455979.23362....@news.demon.nl>,

"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote:
> 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);
Here there needs to be a line added:

num_rows:= dbms_sql.execute (cur_handle);
-- num_rows needs to be declared as a number

> dbms_sql.close_cursor(cur_handle);
> end;
>
> Hth,
> Sybrand Bakker, Oracle DBA
>
> Hi wrote in message <7ha3gm$itb$1...@nnrp1.deja.com>...

> >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
> >
> >
> >--== Sent via Deja.com http://www.deja.com/ ==--
> >---Share what you know. Learn what you don't.---
>
>

--
Alex Shterenberg
"I hate people. I think they should suffer as much as
possible, and therefore I'm into those old communist
dictatorships." - Euronymous, Mayhem

Jonathan Lewis

unread,
May 14, 1999, 3:00:00 AM5/14/99
to

Reading through the dbmsXXXX.sql scripts in the
$ORACLE_HOME/rdbms/admin directory for
8.0.4 last night, I came across an addition to the
dbms_utility package. I don't know how long
this has been there, but it is called
exec_ddl_statement(parse_string varchar2);

so a short solution is:

dbms_utility.exec_ddl_statement(
'truncate table ' || i_table_name
);


--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

AleX wrote in message <7hfkij$10l$1...@nnrp1.deja.com>...

Scott Hutchinson

unread,
May 20, 1999, 3:00:00 AM5/20/99
to
You need to use the Dynamic SQL Package (DBMS_SQL). With this you can
place DDL commands (such as Truncate) within a Stored Proc.

Scott Hutchinson
SHu...@Globalnet.co.uk

Big Bear wrote:

> yeah same here. they say you can't use DDL in store proc. see replies
> to my post dt 10th may
>
> Hi wrote:
>

0 new messages