Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Tricky dynamic sql using user input

Mike301Sep 22 2015 — edited Sep 23 2015

Guru's and Experts

DB: Oracle

Trying to resolve this tricky case for last few days and now decided to take experts help.

I'm accepting filesystem mounts from user input and would like to use that value in SQL query to generate dynamic script to restore datafiles.

I can generate dynamic script if I know what filesystems name will be but trying to figure out on how to take user input value and put that in query?

Runtime:  ./gen_query.ksh -oldDB db1 -newDB db2 -mt /u01,/u02,/u03,/u04

values for -oldDB, -newDB, -mt are entered by users.

-mt values will be separated by comma ","

-mt values can differ from user and can be two or three or x numbers so I can't really hardcode them.

How to check all -mt mounts exist on server and place them into below to generate restore script?

Is it possible?

Dynamic query:

#!/bin/ksh LOG_FILE = test_gen_query.sql

exec >> $LOG_FILE 2>&1

gen_query= SELECT 'set newname for datafile '''

           || file_name

            || ''' to ''<-mt values>'

            || replace to_char(mod(rownum, 4)
           || replace(substr(file_name, 5, length (file_name)),'$2','$4')
           || ''';' FROM ( SELECT file_name, bytes from dba_data_files ORDER BY bytes desc ) 
           /

Sample output required:
set newname for datafile '/u40/oradata/db1/test1.dbf' to '/u01/oradata/db2/test1.dbf';
set newname for datafile '/u40/oradata/db1/test2.dbf' to '/u02/oradata/db2/test2.dbf';
set newname for datafile '/u41/oradata/db1/test3.dbf' to '/u03/oradata/db2/test3.dbf';
set newname for datafile '/u40/oradata/db1/test4.dbf' to '/u04/oradata/db2/test4.dbf';

set newname for datafile '/u40/oradata/db1/test5.dbf' to '/u01/oradata/db2/test5.dbf';
set newname for datafile '/u40/oradata/db1/test6.dbf' to '/u02/oradata/db2/test6.dbf';
...
...

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 21 2015
Added on Sep 22 2015
1 comment
379 views