RE: TRUNCATE IN PL/SQL

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Thu, 21 Jun 2001 06:51:27 -0700
Message-ID: <F001.00331C48.20010621064116@fatcity.com>

Didn't someone already say you have to have drop any table privilege to truncate another user's table? That's at least part of the problem.

Or have you looked at compiling the procedure with another user's privs? I haven't used that myself but I know it's possible, I forgot the exact syntax.

Lisa Koivu
Oracle Database Administrator
954-935-4117

The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful.

The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments.

-----Original Message-----

From:   Lisa Clary [SMTP:lisa_at_cog.ufl.edu]
Sent:   Thursday, June 21, 2001 9:11 AM
To:     Multiple recipients of list ORACLE-L

Subject: RE: TRUNCATE IN PL/SQL

I tried this same thing (procedure below) and received the error messages below when I was trying to truncate a schema.tablename, which was not the schema I was logged in as. What is interesting is that the schema to which I was logged into, does have select, insert, delete and update privs on the table I was trying to truncate. I actually meant to post this question myself, so I am glad I am not alone. Any ideas?

-----Original Message-----

Meng Hong
Sent: Thursday, June 21, 2001 12:11 AM
To: Multiple recipients of list ORACLE-L

Got these error ? may be my DBA restrict these ?

ORA-00903: invalid table name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at line 8

-----Original Message-----

Sent: Thursday, June 21, 2001 11:20 AM
To: Multiple recipients of list ORACLE-L

You can try the following , not sure if it will work though......let me know if it does... :-)

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

-----Original Message-----

Sent: Thursday, June 21, 2001 10:55 AM
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: Mohan, Karthik (GEP)
INET: Karthik.Mohan_at_gepex.ge.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

--

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

Author: Lisa Clary
INET: lisa_at_cog.ufl.edu

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