Home » SQL & PL/SQL » SQL & PL/SQL » Looping through dba_objects to alter table
Looping through dba_objects to alter table [message #344355] Fri, 29 August 2008 09:00 Go to next message
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.
Re: Looping through dba_objects to alter table [message #344358 is a reply to message #344355] Fri, 29 August 2008 09:13 Go to previous message
BlackSwan
Messages: 25033
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:
  


Current Time: Fri Dec 02 14:06:32 CST 2016

Total time taken to generate the page: 0.09816 seconds