Skip to content
February 3, 2011 / doganay

REPLACE TEXT IN ALL VIEWS

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Hi,

sometimes you need to replace some string with another in all views in a whole schema. Well, you may want to use this:
(you can change view to any other object type)

–coded by Mustafa DOGANAY
–03.02.2011
declare
x clob;
y varchar2(32000);
z number;
t varchar2(32000);
CURSOR view_list IS
select view_name from dba_views where owner=’MYSCHEMA’;
begin
for i in view_list loop
x:=dbms_metadata.get_ddl(‘VIEW’,i.view_name);
y:=dbms_lob.substr(x, 32000, 1 );
z:=instr(y,’OLDSTRING’,1,1);
if (z!=0)
then
t:=replace(y,’OLDSTRING’,’NEWSTRING’);
dbms_output.put_line(t);
end if;
end loop;
end;

try and pray 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: