OraFAQ Forum: SQL & PL/SQL » Looping through dba

Home » SQL & PL/SQL » SQL & PL/SQL » Looping through dba_objects to alter table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Looping through dba_objects to alter table [message #344355]

Fri, 29 August 2008 09:00

bogey
Messages: 53
Registered: February 2006

Member

I'm trying to alter all the tables within a schema with the following:

for rec in (select OBJECT_NAME from dba_objects where owner='DANNY' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME NOT LIKE '%$%') loop
alter table rec.object_name enable row movement;
end loop;

But I'm receiving this
ORA-00900: invalid SQL statement

Details:
for rec in (select OBJECT_NAME from dba_objects where owner='DANNY' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME NOT LIKE '%$%') loop
Error at line 3
ORA-00900: invalid SQL statement

How can I loop through all the tables within a schema and execute an alter statement against that table?

thansk.

Report message to a moderator

Re: Looping through dba_objects to alter table [message #344358 is a reply to message #344355]

Fri, 29 August 2008 09:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

EXECUTE IMMEDIATE

or

SPOOL DONOW.SQL
select 'alter table ' || OBJECT_NAME || ' enable row movement;'
from dba_objects where owner='DANNY' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME NOT LIKE '%$%');
@DONOW.SQL

[Updated on: Fri, 29 August 2008 09:15] by Moderator

Report message to a moderator

Previous Topic:	Defining and initializing a VARRAY in PL/SQL for insert into a table
Next Topic:	window function error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 08:03:08 CST 2024