oracle的文本导入、导出技巧

2024-08-29 13:52:01

字体：大中小

来源：转载

供稿：网友

在使用Oracle时，总觉得oracle的导入，导出没有informix的load,unload好用，没办法，只能参照网上朋友的思路和informix的实现，写了几个脚本，希望各位高手指点：

=============================================

工具用法：
unload.sh
Usage: unload.sh userid/passwd[@oraclesid] [tabname]
注释：
假如不加’@oraclesid’，则用当前环境变量ORACLE_SID。
假如不加tabname参数，则处理该用户下的所有表，生成’表名.unl’文件。
    假如加tabname参数，则单独处理该表，生成’表名.unl’文件。
load.sh
Usage: load.sh userid/passwd[@oraclesid] [tabname]
注释：
假如不加’@oraclesid’，则用当前环境变量ORACLE_SID。
假如不加tabname参数，则装载该用户下的所有表，必须存在’表名.unl’
文件。
假如加tabname参数，则单独处理该表，必须存在’表名.unl’文件。

=============================================

具体实现：
unload.sh
利用spool的功能，将表中的数据导出到对应的文本中。
load.sh
利用sql*load的功能，将文本中的数据导入到表中。
           控制文件是自动生成的，使用者不需要知道格式
           执行sqlload的脚本也是自动生成
generate_control_file.sh
                生成控制文件
generate_execute_shell.sh
                生成执行脚本

=============================================
代码说明
load.sh
=============================================
#!/usr/bin/ksh
################################################################################
#
#  ?&&é:        load.sh
#
#  ?èê&:        &ù?Yò&&&ê&?Y&&&ò±í?&à&×°??&?ó&&?ê&?Y???&
#
#  2?ê& 1 = ó?&§?&/?ü?&[@ê&à&?&]
#  2?ê& 2 = ±í?&(&é?&
#
#  ×÷?& Bing He
#
#  DT&?????
#    è??ú       DT&?è?                   DT&??èê&
#
#  09/23/2003   Bing He                       &&ê?±àD&
#
################################################################################

lv_temp1="wk.test1"

f_get_tables()
{
rm -f ${lv_temp1}
sqlplus ${userid} <<! >/dev/null
set colsep $sep;
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set numwidth 12;
set termout off;

set trimout on;
set trimspool on;
spool ${lv_temp1};
select table_name from user_tables;
spool off;
exit
!
if [ "$?" -ne 0 ]
then
  echo "Error:sqlplus ${userid} error in load for ${userid} !"
  echo "please check userid and passwd or oracle_sid."
  exit
fi

if [ -f ${lv_temp1} ]
then
  lv_tables=`cat ${lv_temp1} grep -v "^SQL>"  tr -d ' '`
else
  echo "Error:${lv_temp1} file not found!"
  exit
fi

rm -f ${lv_temp1}

}

################################################################################
## ?÷3ìDòè&&ú

lv_no=$#

case ${lv_no} in
  1

userid=$1
f_get_tables;
;;
2

userid=$1
lv_tables=$2
;;
*

   echo "Usage: $0 <userid/passwd[@connection]> <table_name>"
exit
;;
esac

for lv_table in ${lv_tables}
do
  if [ ! -f ${lv_table}.unl ]
  then
      echo "Error:${lv_table}.unl file not found!"
  else
      generate_control_file.sh ${userid} ${lv_table}
      generate_execute_shell.sh ${userid} ${lv_table}
      sh load_${lv_table}.sh
      rm -f ${lv_table}.ctl
      rm -f load_${lv_table}.sh
  fi
done

################################################################################

=============================================

unload.sh
=============================================
#!/usr/bin/ksh
################################################################################
#
#  ?&&é:        unload.sh
#
#  ?èê&:        &ù?Yò&&&ê&?Y&&&ò±í?&à&D&??ê&?Y2&éú3é&?ó&&?ê&?Y???&
#
#  2?ê& 1 = ó?&§?&/?ü?&[@ê&à&?&]
#  2?ê& 2 = ±í?&(&é?&

#
#  ×÷?& Bing He
#
#  DT&?????
#    è??ú       DT&?è?                   DT&??èê&
#
#  09/23/2003   Bing He                       &&ê?±àD&
#
################################################################################

lv_sep='' # --·?&&·&&&&éò?DT&?3é×??&?&ò&&?·?&&·&&&è&''
lv_temp1="unload.temp1"

f_get_tables()
{
  rm -f ${lv_temp1}
  sqlplus ${userid} <<! >/dev/null
  set colsep ${lv_sep};
  set echo off;
  set feedback off;
  set heading off;
  set pagesize 0;
  set linesize 1000;
  set numwidth 12;
  set termout off;
  set trimout on;
  set trimspool on;
  spool ${lv_temp1};
  select table_name from user_tables;
  spool off;
  exit
!
  if [ "$?" -ne 0 ] ; then
    echo "sqlplus $userid error in get table name <"$?">!!"
    echo "please check userid and passwd or database."
    exit
  fi

if [ -f ${lv_temp1} ]
then
  lv_tables=`cat ${lv_temp1} grep -v "^SQL>"  tr -d ' '`
else
  echo "Error:${lv_temp1} file not found!"
  exit
fi
rm -f ${lv_temp1}

}

################################################################
## ?÷3ìDòè&&ú
lv_no=$#

case ${lv_no} in
  1

userid=$1
f_get_tables;
;;
2

userid=$1
lv_tables=$2
;;
*

echo "Usage: $0 <userid/passwd[@connection]> <table_name>"
exit
;;
esac

################################################################

################################################################
## ?&DD????2ù×÷

for table in ${lv_tables}
do
  rm -f lv_$table.txt
  sqlplus ${userid} <<! >/dev/null
  set colsep ${lv_sep};
  set echo off;
  set feedback off;
  set heading off;
  set pagesize 0;
  set linesize 1000;
  set numwidth 12;
  set termout off;
  set trimout on;
  set trimspool on;
  spool lv_$table.txt;
  select * from $table;
  spool off;
!
  if [ "$?" -ne 0 ]
  then
    echo "error:sqlplus $userid error in unload table $table!!"
    echo "please check userid and passwd or database."
    exit
  fi

  if [ -f lv_$table.txt ]
  then
    cat lv_$table.txt  grep -v "^SQL>"sed -e "s/ *$//g"sed "s/$///g"sed -e "s/ *////g" >${table}.unl
    if [[ `grep "ORA-" $table.unl` = "" ]]; then
      echo "unload table ${table}..../t/t/t/t/t ok"
    else
      cat ${table}.unl
      err="$err ${table}"
    fi
  else
    echo $0 error
  fi
  rm -f lv_$table.txt
done

################################################################

################################################################
## ?áê&2ù×÷

if [[ "X$err" = "X" ]];then
  echo "Unload Complete!,Thank you!"
else
  echo "Unload Table $err error, please check it!"
fi

################################################################

=============================================

generate_control_file.sh
=============================================
#!/usr/bin/ksh
################################################################################

#
#  ?&&é: generate_control_file.sh
#
#  ?èê&: &ù?Yò&&&±í?&éú3é&?±í&?&??????&
#
#  2?ê& 1 = ó?&§?&/?ü?&[@ê&à&?&]
#  2?ê& 2 = ±í?&
#
#  ×÷?& Bing He
#
#  DT&?????
#    è??ú       DT&?è?                   DT&??èê&
#
#  09/23/2003   Bing He               &&ê?±àD&
#
################################################################################

################################################################################
## ?÷3ìDòè&&ú

if [ ! $# -eq 2 ]
then
  echo "Usage: $0 <userid/passwd[@connection]> <table_name>"
  exit
else
  userid=$1
  table=$2
fi

################################################################################

################################################################################
## ?&DD????2ù×÷

lv_temp="wk_${table}.test"
lv_temp1="wk_${table}.test1"
lv_temp2="wk_${table}.test2"
lv_control="${table}.ctl"

sqlplus ${userid} <<! >/dev/null
spool ${lv_temp};
desc ${table}
spool off;
exit
!
if [ "$?" -ne 0 ]
then
  echo "Error:sqlplus ${userid} error in generate control file for table ${table} !"
  echo "please check userid and passwd or oracle_sid."
  exit
fi

if [ -f ${lv_temp} ]
then
    cat ${lv_temp}grep -v "^SQL>" grep -v " Name   " grep -v " -------" awk '{PRint $1}' > ${lv_temp1}
    lv_line_num=`cat ${lv_temp1}  wc -l`
    lv_line_num=`eXPr ${lv_line_num} - 2`
    lv_index=0

    rm -f ${lv_temp2}
    for lineinfo in `cat ${lv_temp1}`
    do
      if [ ${lv_index} -eq ${lv_line_num} ]

      then
          echo "${lineinfo}" >> ${lv_temp2}
      else
          echo "${lineinfo}," >> ${lv_temp2}
          lv_index=`expr ${lv_index} + 1`
      fi
    done
else
    echo "$0 error :not find ${lv_temp} file."
    exit
fi

lv_str="LOAD DATA INFILE '${table}.unl' BADFILE 'bad_${table}.bad' APPEND INTO TABLE ${table} FIELDS TERMINATEd BY /"/""
echo ${lv_str} > ${lv_control}
echo "("      >> ${lv_control}
cat  ${lv_temp2} >> ${lv_control}
echo ")"      >> ${lv_control}

rm -f ${lv_temp}
rm -f ${lv_temp1}
rm -f ${lv_temp2}

################################################################################

=============================================

generate_execute_shell.sh
=============================================
#!/usr/bin/ksh
################################################################################
#
#  ?&&é:        generate_execute_shell.sh
#
#  ?èê&:        &ù?Yò&&&±í?&éú3é&?±í&?×°????±?
#
#  2?ê& 1 = ó?&§?&/?ü?&[@ê&à&?&]
#  2?ê& 2 = ±í?&
#
#  ×÷?& Bing He
#
#  DT&?????
#    è??ú       DT&?è?                   DT&??èê&
#
#  09/23/2003   Bing He                       &&ê?±àD&
#
################################################################################

################################################################################
## ?÷3ìDòè&&ú

if [ ! $# -eq 2 ]
then
  echo "Usage: $0 <userid/passwd[@connection]> <table_name>"
  exit
else
  userid=$1
  table=$2
fi

################################################################################

################################################################################
## ??2&±&á&&¨ò&?&óò

lv_rows=10000
lv_bindsize=8192000
lv_readsize=8192000

################################################################################

################################################################################
## éú3é?&DD??±????&

echo "sqlldr ${userid} control=${table}.ctl rows=${lv_rows} bindsize=${lv_bindsize} readsize=${lv_readsize} log=log_${table}.log bad=bad_${table}.bad direct=true" > load_${table}.sh

################################################################################

=============================================

上一篇：从Oracle Developer/2000 调用VB

下一篇：Oracle客户端文件打包