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: Johnston, Tim <TJohnston_at_quallaby.com>
Date: Thu, 21 Jun 2001 12:12:09 -0700
Message-ID: <F001.00332645.20010621110538@fatcity.com>

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

Original text of this message

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