| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: TRUNCATE IN PL/SQL
All,
that's because there are special security rules attached to stored procedures and packages.
from sql*plus, you can do a truncate if you have the DBA priv. The DBA priv is a ROLE, and the ROLE gives you the {delete any table} system priv.
from a stored procedure/package, you may NOT perform a system priv (like delete any table as Rachel explained) on a db table that is inherited thru a ROLE. you must have the delete any table system priv granted directly to the user where the stored procedure/package exists.
So, to summarize,
grant delete and table directly to an Oracle account. create the stored proc/pack in that users schema. execute the proc/pack.
should work fine.
hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, June 21, 2001 10:31 AM
To: Multiple recipients of list ORACLE-L
Not true...If I open a sqlplus window and issue a truncate on the schema--no problem. If I try and do it through a procedure like below--I get the errors....
lc
-----Original Message-----
Witold.Iwaniec_at_atl.bluecross.ca
Sent: Thursday, June 21, 2001 8:47 AM
To: Multiple recipients of list ORACLE-L
Not 100% sure but I think only the table owner can use "TRUNCATE"
Witold
Raymond Lee Meng Hong <RAYMOND_at_infopro.com.my> on 06/21/2001 01:10:52
Please respond to ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Witold Iwaniec/ATL_BLUECROSS_CA)
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 Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Witold.Iwaniec_at_atl.bluecross.ca
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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).
--------------------------------------------------------------------
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 Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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). Received on Thu Jun 21 2001 - 09:42:07 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |