CODEWITHSUNDEEP
sqloracle-databaseshell
red_bairn
red_bairn

Reputation: 73

How do I create an alter replace in Oracle SQL with dynamic data?

I am creating an SQL file that will be ran automatically by a shell script and I want all the name(s) changed automatically.

Current data for NAME (of datafiles) 

/oradata/dr4/<site_name>/scion/images09.dbf
/oradata/dr4/<site_name>/scion/images10.dbf

METHOD #1:

The loop below is what I have. The &1 is a variable with the SITE_NAME coming from the shell script:

 begin
for i in
(select 'alter database rename file
    '''||name||''' to '''||replace(name,'/oradata/dr4/&1/scion/%','/oradata/devdb12c/scion/%')||''''
 as cmd
from v$datafile
where name like '/oradata/dr4/&1/scion/%')
loop
execute immediate i.cmd;
end loop;
end;
/

METHOD#2:

----Change the names for those datafiles in v$datafile
SELECT name REPLACE(NAME,'dr4/&1','devdb12c') AS CUNION FROM v$datafile;

The method had to be in a loop but I had come close in METHOD#2. It was similar to something I did with another file for SED.

The result is:

old   4:     '''||name||''' to '''||replace(name,'/oradata/dr4/&1/scion/%','/oradata/devdb12c/scion/%')||''''
new   4:     '''||name||''' to '''||replace(name,'/oradata/dr4/<site_name>/scion/%','/oradata/devdb12c/scion/%')||''''
old   7: where name like '/oradata/dr4/&1/scion/us%')
new   7: where name like '/oradata/dr4/<site_name>/scion/us%')

Expected output:

/oradata/devdb12c/scion/images09.dbf
/oradata/devdb12c/scion/images10.dbf
/oradata/devdb12c/scion/users05.dbf

Upvotes: 0

Views: 898

Answers (2)

Ravi
Ravi

Reputation: 31417

As you mentioned in name column consists

/oradata/dr4/<site_name>/scion/images09.dbf

And, your expected output is

/oradata/devdb12c/scion/images09.dbf

Then, you can simply replace /dr4/<site_name>/ to /devdb12c/

>>>Demo Link<<<

Upvotes: 1

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1622

SET VERIFY OFF is the answer.

i.e.

SET VERIFY OFF
     begin
    for i in
    (select 'alter database rename file
        '''||name||''' to '''||replace(name,'/oradata/dr4/&1/scion/%','/oradata/devdb12c/scion/%')||''''
     as cmd
    from v$datafile
    where name like '/oradata/dr4/&1/scion/%')
    loop
    execute immediate i.cmd;
    end loop;
    end;
    /

Upvotes: 0

Related Questions