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 : [EMAIL PROTECTED]
"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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
