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)



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

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/%')
execute immediate i.cmd;
end loop;


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


Answer Source

As you mentioned in name column consists


And, your expected output is


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

>>>Demo Link<<<