Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: TRUNCATE IN PL/SQL

RE: TRUNCATE IN PL/SQL

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Thu, 21 Jun 2001 10:44:14 -0700
Message-ID: <F001.003325AE.20010621104336@fatcity.com> 






It should only be drop any table.



"Walking on water and developing software from a specification are easy if
both are frozen."



Christopher R. Spence


Oracle DBA


Fuelspot 





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



Sent: Thursday, June 21, 2001 2:10 PM


To: Multiple recipients of list ORACLE-L




Lisa,



Just tried my steps on 816 and I needed one more system priv - drop any
table.



the stored procedure then worked fine.



nice catch!



Tom Mercadante


Oracle Certified Professional




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



Sent: Thursday, June 21, 2001 1:40 PM


To: Multiple recipients of list ORACLE-L




Not so fast my friend...unless I am missing a step, this still does not
work.


USER: cogroster (user--not a role, but DOES have DBA privs, and does have
the system priv of *delete any table* specifically granted) -- also contains
the procedure


USER: user01 (schema to which I want to truncate a table, and the explicit
grants to update/delete/select are granted to cogroster)




command:  execute truncate_table('user01.TABLE_NAME');
begin truncate_table('user01.TABLE_NAME');
*


ERROR at line 1:


ORA-01031: insufficient privileges


yaddah


yaddah



Not that I want to beat a dead horse, but I don't see what I am missing...



lc



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



Thomas F


Sent: Thursday, June 21, 2001 10:51 AM


To: Multiple recipients of list ORACLE-L




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 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:


  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 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: 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


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


--------------------------------------------------------------------

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: 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


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


--------------------------------------------------------------------

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: Christopher Spence


  INET: cspence_at_FuelSpot.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).
Received on Thu Jun 21 2001 - 12:44:14 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US