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: Lisa Clary <lisa_at_cog.ufl.edu>
Date: Thu, 21 Jun 2001 13:09:13 -0700
Message-ID: <F001.0033299F.20010621131317@fatcity.com> 






Actually, I really like this idea. Just tried it out (works, of course), and
it does provide better security. thanks for the tip!



lc



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



Tim


Sent: Thursday, June 21, 2001 3:06 PM


To: Multiple recipients of list ORACLE-L




If possible, I prefer to create the procedure in the table owners schema and
then grant execute to the user that needs to perform the truncate...  That
way you don't have to grant privileges like "DROP ANY TABLE"...



Tim



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



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


To: Multiple recipients of list ORACLE-L




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).


--



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


--



Author: Johnston, Tim


  INET: TJohnston_at_quallaby.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: 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).
Received on Thu Jun 21 2001 - 15:09:13 CDT












Original text of this message













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