sqlloader控制文件生成工具

最近在做数据移植工作，入库时使用了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======================================#