自动产生sql*loader 控制文件的shell scripts,
运行格式与脚本如下,可根据实际需要更改...
######################################################
#./create_ctl_file.sh username/password tab1 tab2 ...
######################################################
PW=$1
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/${X}_old.cntl --要产生的对应controlfile 名及存放路径
set wrap off
set feedback off
set pagesize 0
set verify off
prompt LOAD DATA
prompt CHARACTERSET ZHT16BIG5
prompt APPEND INTO TABLE ${X}_old --要导入对应table名
prompt FIELDS TERMINATED BY x'09'
prompt TRAILING NULLCOLS
prompt (
SELECT decode(column_id,
1,' ',
',')
|| substr(column_name
|| ' ',1,35)
|| CASE
WHEN data_type = 'DATE'
THEN 'DATE "YYYY/MM/DD HH24:MI:SS"'
ELSE ''
END
FROM user_tab_columns
WHERE table_name = upper('$X')
ORDER BY column_id
/
prompt )
exit
EOF
done
运行格式与脚本如下,可根据实际需要更改...
######################################################
#./create_ctl_file.sh username/password tab1 tab2 ...
######################################################
PW=$1
shift
for X in $*
do
sqlplus -s $PW << EOF > /tmp/${X}_old.cntl --要产生的对应controlfile 名及存放路径
set wrap off
set feedback off
set pagesize 0
set verify off
prompt LOAD DATA
prompt CHARACTERSET ZHT16BIG5
prompt APPEND INTO TABLE ${X}_old --要导入对应table名
prompt FIELDS TERMINATED BY x'09'
prompt TRAILING NULLCOLS
prompt (
SELECT decode(column_id,
1,' ',
',')
|| substr(column_name
|| ' ',1,35)
|| CASE
WHEN data_type = 'DATE'
THEN 'DATE "YYYY/MM/DD HH24:MI:SS"'
ELSE ''
END
FROM user_tab_columns
WHERE table_name = upper('$X')
ORDER BY column_id
/
prompt )
exit
EOF
done
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-175845/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/308563/viewspace-175845/