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
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
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
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
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)
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
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
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
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
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
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:
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
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
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
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
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
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
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
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
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
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
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
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,
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
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.
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
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.
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
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-
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
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
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
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
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
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
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
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 developin
: 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
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
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
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
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
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);
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
44 matches
Mail list logo
