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-----
[EMAIL PROTECTED]
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 <[EMAIL PROTECTED]> on 06/21/2001 01:10:52
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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 : [EMAIL PROTECTED]
"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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
