red_bairn red_bairn - 2 months ago 12
Bash Question

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

Answer Source

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<<<