RE: TRUNCATE IN PL/SQL

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Fri, 22 Jun 2001 05:36:45 -0700
Message-ID: <F001.003336EB.20010622051541@fatcity.com>

Raymond,

You asked why you could delete the records in a procedure, but not truncate the table. A delete command is a DML command, while a truncate table command is DDL. There is a distinct difference between these two types of commands. DDL commands (Data Definition Language) are used to create and modify structures within the database (like tables, views etc), while DML (Data Manipulation Language) commands do just what the name suggests - change the data within structures.

Within PL/SQL, you can perform any type of DML command. It was only recently (Oracle v7) that Oracle gave us the opportunity to use DDL commands with PL/SQL. In Version 7, you must use the DBMS_SQL package to perform DDL commands. Karthik Mohan supplied the sample script below early yesterday (Thursday). Within Oracle 8, there is a new version of this (the execute immediate option - a little easier to use).

Since you are using Oracle V7, try the sample script below.

Hope this helps.

DECLARE

myCur           number;
mySQL           varchar2(2000);

BEGIN
mySQL := 'TRUNCATE TABLE (table_name)';
myCur := DBMS_SQL.open_cursor;
DBMS_SQL.PARSE(myCur, mySQL, DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR(myCur);
END; Regards,
Karthik

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Thursday, June 21, 2001 10:35 PM
To: Multiple recipients of list ORACLE-L

Is this command avaiable in Oracle 7 ??? I got this error in TOAD.

The following error has occurred:

ORA-06550: line 6, column 11:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the
following:

:= . ( @ % ;

-----Original Message-----
Sent: Thursday, June 21, 2001 10:22 PM
To: Multiple recipients of list ORACLE-L

You can by using the following statement:

execute immediate 'truncate table table_name';

Prakash

-----Original Message-----
Sent: Wednesday, June 20, 2001 10:55 PM
To: Multiple recipients of list ORACLE-L

Hello guru , how can I execute a truncate table in PL/SQL ?? It only work for delete DML only ? why ?

Raymond Lee
Infopro Sdn Bhd
Block B3 Level 8, Leisure Commerce Square No. 9, Jalan PJS 8/9 46150 Petaling Jaya Selangor , Malaysia
Tel : 603-78766666 ext : 266 Fax : 603-78761233 Email : Raymond_at_infopro.com.my

"Friendship with oneself is all important, because without it one cannot be friend with anyone else in the world "
- Eleanor Roosevelt

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bala, Prakash
INET: prakash.bala_at_cingular.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Raymond Lee Meng Hong
INET: RAYMOND_at_infopro.com.my

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------