最近在做数据移植工作,入库时使用了oracle的sql loader 工具,sql loader 工具支持大数据量的入库,同时配置参数多,兼容各种分隔符、结束符、处理函数、但是需要配置在一个ctl控制文件中,最近通过shell与临时存储过程的结合,写了一个生成主ctl文件的脚本,对于大表及需要处理复杂字段的表,感觉减轻了很大的工作量。下面上代码:
#!/bin/sh
Usage()
{
echo "Usage:"
echo "$1 -t [TABLENAME] -s [SID] -d [ DIRECTORY ]-? "
echo " -t: TableName"
echo " -s: Sid"
echo " -d: Directory"
echo " -?: help"
exit 1
}
fun_genctl () {
echo INFO ">>" TABLE[ $2 ] SID[ $1 ] DIR[ $3 ]
$ORACLE_HOME/bin/sqlplus -S $1>/dev/null <<ORA
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
set trim on
set trimspool on
set serveroutput on
set linesize 32767
set heading off
set feedback off
set pagesize 0
set verify off
spool ${3}/${2}.ctl
DECLARE
--
v_sqlcol VARCHAR2(500); --取字段sql字符串
v_tabnam VARCHAR2(40); --表名变量
v_column VARCHAR2(200); --字段值
v_coltyp VARCHAR2(20); --字段类型
v_colstr VARCHAR2(4000); --组合后的字段字符串
v_rownum INTEGER; --数量临时变量
v_collen INTEGER; --字段长度
--
TYPE ref_cursor IS REF CURSOR; --指针类型定义
v_curcol ref_cursor; --取字段指针引用
BEGIN
--变量赋值
v_tabnam := UPPER('$2');
--检查表正确性
v_rownum := 0;
SELECT count(*) INTO v_rownum FROM user_tables WHERE table_name = v_tabnam;
IF v_rownum = 0 THEN
DBMS_OUTPUT.PUT_LINE('can not found tabLE '||v_tabnam);
RETURN;
END IF;
DBMS_OUTPUT.PUT_LINE('LOAD DATA '|| CHR(10) || 'CHARACTERSET ZHS16GBK');
DBMS_OUTPUT.PUT_LINE('INFILE * "str X''0A''"');
DBMS_OUTPUT.PUT_LINE('TRUNCATE INTO TABLE ' || v_tabnam);
DBMS_OUTPUT.PUT_LINE('REENABLE DISABLED_CONSTRAINTS');
DBMS_OUTPUT.PUT_LINE('FIELDS TERMINATED BY ' || 'X''01''');
DBMS_OUTPUT.PUT_LINE('TRAILING NULLCOLS' || chr(10) || '(');
--循环遍历表字段 begin
v_sqlcol := 'select lower(column_name),data_type,data_length from user_tab_columns where table_name =''' ||
v_tabnam || ''' order by column_id';
--获取字段数量
v_rownum := 0;
SELECT count(*) INTO v_rownum FROM USER_TAB_COLUMNS WHERE table_name = v_tabnam;
--打开指针并循环
OPEN v_curcol FOR v_sqlcol;
LOOP
FETCH v_curcol
INTO v_column, v_coltyp, v_collen;
EXIT WHEN v_curcol%notfound;
--将组合字符串置空
v_colstr := '';
--type DATE
IF v_coltyp = 'DATE' THEN
v_column := v_column || ' date ''' || 'yyyy-mm-dd hh24:mi:ss'' "trim(:' || v_column || ')"';
END IF;
--type TIMESTAMP
IF v_coltyp LIKE 'TIMESTAMP%' THEN
v_column := v_column || ' timestamp "yyyy-mm-dd hh24:mi:ss.ff"';
END IF;
--type varchar2 or char
IF (v_coltyp = 'VARCHAR2' OR v_coltyp = 'CHAR') AND v_collen >= 200 THEN
v_column := v_column || ' char(4000)' || ' "trim(:' || v_column || ')"';
END IF;
v_rownum := v_rownum - 1;
IF v_rownum = 0 THEN
v_colstr := v_column;
ELSE
v_colstr := v_column || ',';
END IF;
DBMS_OUTPUT.PUT_LINE(v_colstr);
END LOOP;
CLOSE v_curcol;
DBMS_OUTPUT.PUT_LINE(')');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
spool off
exit
ORA
}
#==========================================================================#
# Copyright (C), 2011-2020 #
# Version : 1.0 #
# Description: #
# File : #
# Author : #
# Date : 2018/11/13 #
# History: #
#==========================================================================#
#=================================START====================================#
SID=""
TAB=""
DIR="."
while getopts :s:t:d:? opt
do
case $opt in
s) SID=$OPTARG ;;
t) TAB=$OPTARG ;;
d) DIR=$OPTARG ;;
?) Usage $0 ;;
*) echo "[$OPTARG] IS NOT A OPTION!!" ;;
esac
done
##SID##
if [ -z ${SID} ] ; then
echo "SID标识为空!!"
Usage $0
fi
##TAB##
if [ -z ${TAB} ] ; then
echo "TAB标识为空!!"
Usage $0
fi
##DIR##
echo $DIR
if [ ! -d $DIR ]; then
echo "$DIR not found!"
exit $?
fi
rm -f $DIR/$TAB.ctl
fun_genctl $SID $TAB $DIR
exit $?
#=================================END======================================#