Re: TRUNCATE IN PL/SQL
On June 22, 2001 11:15 pm, Rachel Carmichael wrote: Greg, you gotta read my apologies first :) I already was informed about that, and apologized for the lack of caffeine/lack of sleep (should never have those two in combination!) error Happy Friday! Rachel D'oh! That's what I get for being tardy in my reading (had ~300 messages unread :) Maybe I need less sleep.. Merry Saturday, Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gregory Conron 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).
RE: TRUNCATE IN PL/SQL
Raymond, You asked why you could delete the records in a procedure, but not truncate the table. A delete command is a DML command, while a truncate table command is DDL. There is a distinct difference between these two types of commands. DDL commands (Data Definition Language) are used to create and modify structures within the database (like tables, views etc), while DML (Data Manipulation Language) commands do just what the name suggests - change the data within structures. Within PL/SQL, you can perform any type of DML command. It was only recently (Oracle v7) that Oracle gave us the opportunity to use DDL commands with PL/SQL. In Version 7, you must use the DBMS_SQL package to perform DDL commands. Karthik Mohan supplied the sample script below early yesterday (Thursday). Within Oracle 8, there is a new version of this (the execute immediate option - a little easier to use). Since you are using Oracle V7, try the sample script below. Hope this helps. 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 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, June 21, 2001 10:35 PM To: Multiple recipients of list ORACLE-L Is this command avaiable in Oracle 7 ??? I got this error in TOAD. The following error has occurred: ORA-06550: line 6, column 11: PLS-00103: Encountered the symbol IMMEDIATE when expecting one of the following: := . ( @ % ; -Original Message- Sent: Thursday, June 21, 2001 10:22 PM To: Multiple recipients of list ORACLE-L You can by using the following statement: execute immediate 'truncate table table_name'; Prakash -Original Message- Sent: Wednesday, June 20, 2001 10:55 PM 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-7876 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: Bala, Prakash 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: 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).
RE: TRUNCATE IN PL/SQL
On Thu, 21 Jun 2001,Raymond Lee Meng Hong scribbled on the wall in glitter...: -Is this command avaiable in Oracle 7 ??? -I got this error in TOAD. nope it's an 8ism. for 7 you need to use the dbms_sql package. -- Bill Shrek Thater Certifiable ORACLE DBA Telergy, Inc.[EMAIL PROTECTED] ~~ You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. ~~ Do you like me for my brain or my baud? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thater, William 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).
RE: TRUNCATE IN PL/SQL
In urder to TRUNCATE a table you have to have ge granted DELETE ANY TABLE. A very dangerous situation. ROR mª¿ªm -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-7876 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: Ron Rogers 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).
Re: TRUNCATE IN PL/SQL
On June 21, 2001 10:36 am, Rachel Carmichael wrote: anyone who has delete any table can truncate. isn't it 'drop any table'? Cheers, GC -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gregory Conron 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).
Re: TRUNCATE IN PL/SQL
Greg, you gotta read my apologies first :) I already was informed about that, and apologized for the lack of caffeine/lack of sleep (should never have those two in combination!) error Happy Friday! Rachel From: Gregory Conron [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: TRUNCATE IN PL/SQL Date: Fri, 22 Jun 2001 16:52:46 -0800 On June 21, 2001 10:36 am, Rachel Carmichael wrote: anyone who has delete any table can truncate. isn't it 'drop any table'? Cheers, GC -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gregory Conron 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
RE: TRUNCATE IN PL/SQL
I tried this same thing (procedure below) and received the error messages below when I was trying to truncate a schema.tablename, which was not the schema I was logged in as. What is interesting is that the schema to which I was logged into, does have select, insert, delete and update privs on the table I was trying to truncate. I actually meant to post this question myself, so I am glad I am not alone. Any ideas? lc -Original Message- Meng Hong Sent: Thursday, June 21, 2001 12:11 AM To: Multiple recipients of list ORACLE-L 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-7876 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: 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).
RE: TRUNCATE IN PL/SQL
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-7876 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).
RE: TRUNCATE IN PL/SQL
anyone who has delete any table can truncate. From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: TRUNCATE IN PL/SQL Date: Thu, 21 Jun 2001 04:46:31 -0800 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-7876 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
RE: TRUNCATE IN PL/SQL
Actually ... you can truncate someone elses table if you have the authority. I find that I need to put the users Schema on the front even if there is a synonym. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 21, 2001 7: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-7876 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: Kevin Lange 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).
RE: TRUNCATE IN PL/SQL
You are right on this to a certain extent, you either have to be the schema owner, or you need to have been granted the DROP ANY TABLE system privilege. Have you satisfied both or either of these Raymond? If not, try it out.. HTH Mark -Original Message- [EMAIL PROTECTED] Sent: Thursday, June 21, 2001 01:47 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-7876 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: Mark Leith 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).
Re: TRUNCATE IN PL/SQL
Oracle 7 docs state you need delete any table, Oracle 8 docs state you need drop any table privilege to truncate another owner's table. Dennis [EMAIL PROTECTED] wrote: 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-7876 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: Dennis M. Heisler 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).
RE: TRUNCATE IN PL/SQL
Title: RE: TRUNCATE IN PL/SQL Didn't someone already say you have to have drop any table privilege to truncate another user's table? That's at least part of the problem. Or have you looked at compiling the procedure with another user's privs? I haven't used that myself but I know it's possible, I forgot the exact syntax. Lisa Koivu Oracle Database Administrator 954-935-4117 The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -Original Message- From: Lisa Clary [SMTP:[EMAIL PROTECTED]] Sent: Thursday, June 21, 2001 9:11 AM To: Multiple recipients of list ORACLE-L Subject: RE: TRUNCATE IN PL/SQL I tried this same thing (procedure below) and received the error messages below when I was trying to truncate a schema.tablename, which was not the schema I was logged in as. What is interesting is that the schema to which I was logged into, does have select, insert, delete and update privs on the table I was trying to truncate. I actually meant to post this question myself, so I am glad I am not alone. Any ideas? lc -Original Message- Meng Hong Sent: Thursday, June 21, 2001 12:11 AM To: Multiple recipients of list ORACLE-L 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-7876 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: 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
RE: TRUNCATE IN PL/SQL
You can by using the following statement: execute immediate 'truncate table table_name'; Prakash -Original Message- Sent: Wednesday, June 20, 2001 10:55 PM 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-7876 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: Bala, Prakash 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).
RE: TRUNCATE IN PL/SQL
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-7876 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).
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-
[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-7876 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:
RE: TRUNCATE IN PL/SQL
Oracle 8 (8i) docs were finally corrected for this age old mistake ;) - Kirti -Original Message- From: Dennis M. Heisler [SMTP:[EMAIL PROTECTED]] Sent: Thursday, June 21, 2001 9:07 AM To: Multiple recipients of list ORACLE-L Subject: Re: TRUNCATE IN PL/SQL Oracle 7 docs state you need delete any table, Oracle 8 docs state you need drop any table privilege to truncate another owner's table. Dennis [EMAIL PROTECTED] wrote: 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-7876 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: Deshpande, Kirti 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).
Re: TRUNCATE IN PL/SQL
and the docs (can't remember if it's 6 or 7) used to state drop any table priv after all, the docs are NEVER wrong From: Dennis M. Heisler [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: TRUNCATE IN PL/SQL Date: Thu, 21 Jun 2001 06:06:53 -0800 Oracle 7 docs state you need delete any table, Oracle 8 docs state you need drop any table privilege to truncate another owner's table. Dennis [EMAIL PROTECTED] wrote: 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-7876 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: Dennis M. Heisler 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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL
RE: TRUNCATE IN PL/SQL
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-7876 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
RE: TRUNCATE IN PL/SQL
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-7876 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
RE: TRUNCATE IN PL/SQL
This is because you cannot truncate a synonym 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 10:16 AM To: Multiple recipients of list ORACLE-L Actually ... you can truncate someone elses table if you have the authority. I find that I need to put the users Schema on the front even if there is a synonym. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 21, 2001 7: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-7876 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: Kevin Lange 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
RE: TRUNCATE IN PL/SQL
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-7876 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
RE: TRUNCATE IN PL/SQL
Thanks Tom Kirti---
As an experiment I revoked the delete any table and granted the drop any
table, and it worked. The drop any table is the key
thanks to all!
lc
-Original Message-
Thomas F
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-7876 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
RE: TRUNCATE IN PL/SQL
There are two problems.
1. You must have permissions (Not via a role)
2. Must use Dynamic SQL (DBMS_SQL/Execute Immediate)
PL/SQL has had these restrictions for a long time, privileges will not work
through roles and cannot execute DDL without using Dynamic SQL options.
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 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-7876 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
RE: TRUNCATE IN PL/SQL
Do you get paid for your daily number of posts? Waleed -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 This is because you cannot truncate a synonym Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot You cannot execute DDL in PL/SQL (Never could) unless you use DBMS_SQL (Pre-8i) or Execute Immediate (8i) Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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).
Re: TRUNCATE IN PL/SQL
this is what happens when I post with insufficient blood levels of caffeine. take what I said and reverse it. You need DROP any table privilege, not delete any table. Early editions of the docs said you needed DELETE any table, they were wrong. Latest editions of the docs have been amended and are correct (at least about this) sigh. From: Rachel Carmichael [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: TRUNCATE IN PL/SQL Date: Thu, 21 Jun 2001 08:12:44 -0800 and the docs (can't remember if it's 6 or 7) used to state drop any table priv after all, the docs are NEVER wrong From: Dennis M. Heisler [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: TRUNCATE IN PL/SQL Date: Thu, 21 Jun 2001 06:06:53 -0800 Oracle 7 docs state you need delete any table, Oracle 8 docs state you need drop any table privilege to truncate another owner's table. Dennis [EMAIL PROTECTED] wrote: 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-7876 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: Dennis M. Heisler 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
RE: TRUNCATE IN PL/SQL
Oh boy, not this again. I left the last list because people complained I posted too much, and didn't like my view on things. Yet they never complained when I helped everyone with many many problems. I answered 20% of the posts on the list, and I always got flak for them. You cannot win. 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 3:16 PM To: Multiple recipients of list ORACLE-L Do you get paid for your daily number of posts? Waleed -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 This is because you cannot truncate a synonym Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot You cannot execute DDL in PL/SQL (Never could) unless you use DBMS_SQL (Pre-8i) or Execute Immediate (8i) Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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).
RE: TRUNCATE IN PL/SQL
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-
[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-7876 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
RE: TRUNCATE IN PL/SQL
Keep posting Chris, I think most of us greatly appreciate the work you do both on the list and at home with your servers. I suggest you just let comments like that go to /dev/null and just keep doing what your doing, don't even break stride to comment. Thanks, Ethan -Original Message- Sent: Thursday, June 21, 2001 1:12 PM To: Multiple recipients of list ORACLE-L Oh boy, not this again. I left the last list because people complained I posted too much, and didn't like my view on things. Yet they never complained when I helped everyone with many many problems. I answered 20% of the posts on the list, and I always got flak for them. You cannot win. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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).
RE: TRUNCATE IN PL/SQL
Title: RE: TRUNCATE IN PL/SQL Yes, he does. His pay for the incredible amount of help he provides is the simple thank you that the recipients of the help send him and he doesn't even ask for that... -Original Message- Sent: Thursday, June 21, 2001 3:16 PM To: Multiple recipients of list ORACLE-L Do you get paid for your daily number of posts? Waleed -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 This is because you cannot truncate a synonym Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot You cannot execute DDL in PL/SQL (Never could) unless you use DBMS_SQL (Pre-8i) or Execute Immediate (8i) Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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).
RE: TRUNCATE IN PL/SQL
I agree, Chris brings the goods!! Keep Posting! KK -Original Message- Sent: Thursday, June 21, 2001 4:36 PM To: Multiple recipients of list ORACLE-L Keep posting Chris, I think most of us greatly appreciate the work you do both on the list and at home with your servers. I suggest you just let comments like that go to /dev/null and just keep doing what your doing, don't even break stride to comment. Thanks, Ethan -Original Message- Sent: Thursday, June 21, 2001 1:12 PM To: Multiple recipients of list ORACLE-L Oh boy, not this again. I left the last list because people complained I posted too much, and didn't like my view on things. Yet they never complained when I helped everyone with many many problems. I answered 20% of the posts on the list, and I always got flak for them. You cannot win. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Kevin Kostyszyn 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).
RE: TRUNCATE IN PL/SQL
Easy, Chris. Back away from the ledge slowly. That's it. Just a little further... grin I'm sure Waleed meant that in jest. Just remember, its hard to determine tone via email. Believe me, your contributions are noted and appreciated. Scott Shafer San Antonio, TX 210-581-6217 I hate the country, all those animals walking around un-cooked. -Original Message- From: Christopher Spence [SMTP:[EMAIL PROTECTED]] Sent: Thursday, June 21, 2001 3:12 PM To: Multiple recipients of list ORACLE-L Subject: RE: TRUNCATE IN PL/SQL Oh boy, not this again. I left the last list because people complained I posted too much, and didn't like my view on things. Yet they never complained when I helped everyone with many many problems. I answered 20% of the posts on the list, and I always got flak for them. You cannot win. 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 3:16 PM To: Multiple recipients of list ORACLE-L Do you get paid for your daily number of posts? Waleed -- 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).
RE: TRUNCATE IN PL/SQL
Oh! I swear I meant nothing except sending a 'hello'! Regards, Waleed -Original Message- Sent: Thursday, June 21, 2001 4:12 PM To: Multiple recipients of list ORACLE-L Oh boy, not this again. I left the last list because people complained I posted too much, and didn't like my view on things. Yet they never complained when I helped everyone with many many problems. I answered 20% of the posts on the list, and I always got flak for them. You cannot win. 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 3:16 PM To: Multiple recipients of list ORACLE-L Do you get paid for your daily number of posts? Waleed -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 This is because you cannot truncate a synonym Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot You cannot execute DDL in PL/SQL (Never could) unless you use DBMS_SQL (Pre-8i) or Execute Immediate (8i) Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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).
OT RE: TRUNCATE IN PL/SQL ( a.k.a The Thread that Will Not Die )
Title: RE: TRUNCATE IN PL/SQL I personally notice a bunch of peole giving Chris their two cents on a fairly frequent basis. -Original Message-From: Bowes, Chris [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 21, 2001 4:36 PMTo: Multiple recipients of list ORACLE-LSubject: RE: TRUNCATE IN PL/SQL Yes, he does. His pay for the incredible amount of help he provides is the simple thank you that the recipients of the help send him and he doesn't even ask for that... -Original Message- Sent: Thursday, June 21, 2001 3:16 PM To: Multiple recipients of list ORACLE-L Do you get paid for your daily number of posts? Waleed -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 This is because you cannot truncate a synonym "Walking on water and developing software from a specification are easy if both are frozen." Christopher R. Spence Oracle DBA Fuelspot You cannot execute DDL in PL/SQL (Never could) unless you use DBMS_SQL (Pre-8i) or Execute Immediate (8i) "Walking on water and developing software from a specification are easy if both are frozen." Christopher R. Spence Oracle DBA Fuelspot -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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).
OT RE: TRUNCATE IN PL/SQL
Did someone pay you to say that? EG -Original Message- Sent: Thursday, June 21, 2001 5:27 PM To: Multiple recipients of list ORACLE-L Oh! I swear I meant nothing except sending a 'hello'! Regards, Waleed -Original Message- Sent: Thursday, June 21, 2001 4:12 PM To: Multiple recipients of list ORACLE-L Oh boy, not this again. I left the last list because people complained I posted too much, and didn't like my view on things. Yet they never complained when I helped everyone with many many problems. I answered 20% of the posts on the list, and I always got flak for them. You cannot win. 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 3:16 PM To: Multiple recipients of list ORACLE-L Do you get paid for your daily number of posts? Waleed -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 This is because you cannot truncate a synonym Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot You cannot execute DDL in PL/SQL (Never could) unless you use DBMS_SQL (Pre-8i) or Execute Immediate (8i) Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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: Mohan, Ross 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).
RE: OT RE: TRUNCATE IN PL/SQL (a.k.a. The World's Longest Thread
My boss yelled at me for rotating the message in my sig. As it offended someone one time. So I just stopped my sig total, I recently brought it back but I am not going to bother changing it, I would like to as I got some good ones. Somedays the sun doesn't shine, some days it pours, somedays you just don't want to get out of bed, and the rest of them you fall out. 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 5:13 PM To: Multiple recipients of list ORACLE-L Chris, Don't even respond to Ethan's post. Keep running. Let the baton remain in your grip. But how about a rotating message in the sig file? ;- Ross -Original Message- Sent: Thursday, June 21, 2001 4:36 PM To: Multiple recipients of list ORACLE-L Keep posting Chris, I think most of us greatly appreciate the work you do both on the list and at home with your servers. I suggest you just let comments like that go to /dev/null and just keep doing what your doing, don't even break stride to comment. Thanks, Ethan -Original Message- Sent: Thursday, June 21, 2001 1:12 PM To: Multiple recipients of list ORACLE-L Oh boy, not this again. I left the last list because people complained I posted too much, and didn't like my view on things. Yet they never complained when I helped everyone with many many problems. I answered 20% of the posts on the list, and I always got flak for them. You cannot win. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -- This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Mohan, Ross 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).
RE: OT RE: TRUNCATE IN PL/SQL ( a.k.a The Thread that Will Not D
Title: RE: TRUNCATE IN PL/SQL
Heh,
you should have saw the post on LazyDBA.
"Christopher is on the other
list, and all he is doing is promoting his website, how annoying".
I got a kick out of that one. Most of them I laugh off
though.
"Walking on water and developing software from a
specification are easy if both are frozen."
Christopher R. Spence Oracle DBA Fuelspot
-Original Message-From: Mohan, Ross
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 21, 2001 5:13
PMTo: Multiple recipients of list ORACLE-LSubject: OT
RE: TRUNCATE IN PL/SQL ( a.k.a The Thread that Will Not Die
)
I
personally notice a bunch of peole giving Chris their two cents on
a
fairly frequent basis.
-Original Message-From: Bowes, Chris
[mailto:[EMAIL PROTECTED]]Sent: Thursday, June 21, 2001 4:36
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
TRUNCATE IN PL/SQL
Yes, he does. His pay for the incredible amount of
help he provides is the simple thank you that the recipients of the help
send him and he doesn't even ask for that...
-Original Message- Sent:
Thursday, June 21, 2001 3:16 PM To: Multiple
recipients of list ORACLE-L
Do you get paid for your daily number of posts?
Waleed
-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
This is because you cannot truncate a synonym
"Walking on water and developing software from a
specification are easy if both are frozen."
Christopher R. Spence Oracle
DBA Fuelspot
You cannot execute DDL in PL/SQL (Never could) unless you
use DBMS_SQL (Pre-8i) or Execute Immediate
(8i)
"Walking on water and developing software from a
specification are easy if both are frozen."
Christopher R. Spence Oracle
DBA Fuelspot
-- Please see the official ORACLE-L
FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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).
RE: TRUNCATE IN PL/SQL
Yea, I second that. - Ross p.s. Chris, looks like Ron could use some moral support with his sig file, too. -Original Message- Sent: Thursday, June 21, 2001 6:23 PM To: Multiple recipients of list ORACLE-L Your right. What's even worse is when you post a solution (or idea) and never get any response back. Oh well. If some people don't like it, tough. Ron Hypercom. cspence@FuelS pot.com To: [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: RE: TRUNCATE IN PL/SQL com 06/21/01 01:12 PM Please respond to ORACLE-L -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross 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).
RE: OT RE: TRUNCATE IN PL/SQL
Figured life is hard enough! -Original Message- Sent: Thursday, June 21, 2001 6:13 PM To: Multiple recipients of list ORACLE-L Did someone pay you to say that? EG -Original Message- Sent: Thursday, June 21, 2001 5:27 PM To: Multiple recipients of list ORACLE-L Oh! I swear I meant nothing except sending a 'hello'! Regards, Waleed -Original Message- Sent: Thursday, June 21, 2001 4:12 PM To: Multiple recipients of list ORACLE-L Oh boy, not this again. I left the last list because people complained I posted too much, and didn't like my view on things. Yet they never complained when I helped everyone with many many problems. I answered 20% of the posts on the list, and I always got flak for them. You cannot win. 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 3:16 PM To: Multiple recipients of list ORACLE-L Do you get paid for your daily number of posts? Waleed -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 This is because you cannot truncate a synonym Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot You cannot execute DDL in PL/SQL (Never could) unless you use DBMS_SQL (Pre-8i) or Execute Immediate (8i) Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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: Mohan, Ross 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: Khedr, Waleed 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
RE: TRUNCATE IN PL/SQL
Another thing is I can do a delete from ln01 in PL/SQL , but why don't
truncate ?
-Original Message-
Sent: Thursday, June 21, 2001 10:50 PM
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-7876 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
RE: TRUNCATE IN PL/SQL
Is this command avaiable in Oracle 7 ??? I got this error in TOAD. The following error has occurred: ORA-06550: line 6, column 11: PLS-00103: Encountered the symbol IMMEDIATE when expecting one of the following: := . ( @ % ; -Original Message- Sent: Thursday, June 21, 2001 10:22 PM To: Multiple recipients of list ORACLE-L You can by using the following statement: execute immediate 'truncate table table_name'; Prakash -Original Message- Sent: Wednesday, June 20, 2001 10:55 PM 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-7876 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: Bala, Prakash 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).
RE: TRUNCATE IN PL/SQL
I'm the owner of the table also, becoz I create it and I drop it before ? -Original Message- Sent: Thursday, June 21, 2001 10:01 PM To: Multiple recipients of list ORACLE-L You are right on this to a certain extent, you either have to be the schema owner, or you need to have been granted the DROP ANY TABLE system privilege. Have you satisfied both or either of these Raymond? If not, try it out.. HTH Mark -Original Message- [EMAIL PROTECTED] Sent: Thursday, June 21, 2001 01:47 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-7876 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: Mark Leith 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
RE: TRUNCATE IN PL/SQL
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-7876 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).
RE: TRUNCATE IN PL/SQL
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-7876 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).
