一个把altibase中数据导入到oracle中的脚本

srzyhead

使用iloader生成fmt文件,用sed处理后生成ctl文件,用sqlldr导入到oracle中

1. #!/bin/bash
3. #"TB_BIL_ACCT_BALANCE_bak" "RATABLE_HISTORY_bak"
5. TABLE_ARRAY=( "tb_bil_rent_arrear" )    #到导入的表的表名
7. ALTIBASE_CONN="-s 127.0.0.1 -u test -p test" 
9. ORACLE_CONN="test/test"
11. INPUT_TYPE=1 #2 REPLACE ;other APPEND
13. TIME=`date +"%m%d%H%M%S"`
15. case ${INPUT_TYPE} in
17. 1)
19. INPUT_TYPE_STR="APPEND"
21. ;;
23. 2)
25. INPUT_TYPE_STR="REPLACE"
27. ;;
29. *)
31. INPUT_TYPE_STR="APPEND"
33. ;;
35. esac
37. echo 4
39. for NUM in ${!TABLE_ARRAY[@]}
41. do
43. TABLE_NAME=${TABLE_ARRAY[NUM]}
45. iloader formout ${ALTIBASE_CONN} -T ${TABLE_NAME} -f data.fmt >/dev/null
47. if [ $? -gt 0 ] ; then
49. echo "iloader formout error!"
51. exit
53. fi
55. iloader out ${ALTIBASE_CONN} -f data.fmt -d data.dat >/dev/null
57. if [ $? -gt 0 ] ; then
59. echo "iloader out error!"
61. exit
63. fi
67. echo "LOAD DATA" > data.ctl
69. echo "INTO TABLE ${TABLE_NAME}" >> data.ctl
71. echo "${INPUT_TYPE_STR}" >> data.ctl
73. echo "FIELDS TERMINATED BY X'5E'" >> data.ctl
75. echo 'TRAILING NULLCOLS' >> data.ctl
77. echo "(" >> data.ctl
79. sed -n '/{/,/}/{p;}' data.fmt | tail +2 | sed '$d;'|sed 's/bigint/integer/;s/numeric/integer/;s/integer[^;]*/integer external/;s/varchar/char/;s/date/date "YYYY\/MM\/DD HH24:MI:SS"/;s/;/,/;$s/,//;' >> data.ctl
81. echo ")" >> data.ctl
85. sqlldr userid=${ORACLE_CONN} control=data.ctl "log=${TABLE_NAME}_${TIME}.log" "bad=${TABLE_NAME}_${TIME}.bad" data=data.dat >/dev/null
89. if [ $? -gt 0 ] ; then
91. echo "sqlldr error!"
93. exit
95. fi
97. done
99. echo "load over!"