CODEWITHSUNDEEP
sqloracle11gksh
homer
homer

Reputation: 433

generate dynamic script with ksh and sql

DB: Oracle

I'm accepting filesystem name from user input and would like to use that value in sql query to generate dynamic script to restore datafile.

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 sql 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 only two mounts or can be three or four and can start with anything (e.g. /u01 or /u06 etc) so I can't really hardcode them.

I think what I need is to chop -mt values and store them into another variable and use those variable to generate query.

Is such thing possible?

Dynamic query:

#!/bin/ksh
LOG_FILE = test_gen_query.log
exec >> $LOG_FILE 2>&1

gen_query=
SELECT 'set newname for datafile ''' 
   || file_name
   || ''' to ''/u01 or /u02 or /u03 or /u04'
   || 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';
...
...

Upvotes: 0

Views: 225

Answers (1)

Walter A
Walter A

Reputation: 19982

When you want to use arguments with a minus-sign, see learn about getopts. When you are lazy, you can use fixed positions: ./gen_query.ksh db1 db2 /u01 /u02 /u03 /u04 and get the vars with:

if [ $# -lt 3 ]; then
   echo "Usage: $0 olddb newdb mount(s)"
   exit 1
fi
# assign the first 2 vars
olddb=$1
newdb=$2
# Move "pointer" in the arglist
shift 2
# Split remaining args on a space
for mnt in $*; do
        echo "my sql for dbold ${olddb} to dbnew ${newdb} for mount ${mnt}"
done

Upvotes: 1

Related Questions