Export content of a table in the Oracle database is text format file

#! / bin / ksh ############################################################################################################################################################################################################################################################################### ######################################################################################### Each record line # configuration: # See the current directory unload.ctl file # usage: # sqlunldr userid / passwd [@connection] Table_name # Note: # If a certain field in the table is char (n), contain / n, / R, will result in the exported data line number than the actual number of records # ################################# ###################################################################################

GetConfig () {IF [-f "./unload.ctl"]; then. ./unload.ctl

echo "Begin Reading Unload.ctl." echo ============================= c "SEP = $ SEP" Echo "Fields = $ FIELDS "#` Echo $ FIELDS | SED -E "S / // G" `echo" destdir = $ dechdir "echo" where = $ where "echo" Order = $ ORDER "echo ======== ====================== Echo "Reading unload.ctl completion." Echo "" Return 0 else return 1 fi}

GetConfig IF [$? -ne 0]; the echo "error: can not find unload.ctl file.please check it!" EXIT 1FI

IF ["x $ 1" = "x"]; the echo "Usage: $ 0 # EXIT ECHO / C" Userid: "Read Userid1 Echo / C" Passwd: "Echo off Read Passwd userid = $ userid1 $ passwd echo on else userid = $ 1 fi

IF ["x $ 2" = "x"]; the echo "No Table in User $ UserId." Echo "Usage: $ 0 " Else Table = $ 2fi # selectsql = `Echo "SELECT $ FIELDS from $ TABLE" | SED -E "S /, / || / ', /' || / g" `#echo selectsql = $ selectsqlecho" Begin to unload ... please wait ... "SQLPlus $ userid < / dev / null set colsep $ SEP; set echo off; set finted OFF; set feeding off; set linesize 0; set linesize 1000; set termout off; set trimout on; set trimspool on; spool wk_ $ {Table} .txt; SELECT $ FIELDS from $ TABLE $ Where $ ORDER; spool off; / exit /! f ["$?" -ne 0]; the echo "error: SQLPLUS $ UserId Error in Unload Table $ table! "Echo" please check userid and passwd or database. "EXIT FI CAT WK _ $ {Table} .txt | SED -E '/ ^ SQL> / D' -E '/ ^ $ / D'> $ TABLE.TXT

IF [[`GREP" ORA / - "$ {Table} .txt` =" "]]; then" "] t ([: space:] // g '> wk_ $] {Table} .txt Else Cat $ TABLE.TXT ERR = "$ TABLE" FI

IF [[["x $ err" = "x"]; then "unload table $ TABLE COMPLETE!" Else Echo "unload table!" EXIT FI Echo "Check The Correctness ..." CNT1 = `SQLPLUS $ UserId < / dev / null set echo off; set finted OFF; set feedingback off; set termout off; set trimout on; set trimuspool on; spool cnt1.txt; select count *) from $ table $ where; spool off; exit; / < / D' -E '/ ^ $ / d' -e ' / // g '`CNT2 =` wc -1 $ {Table} .txt | awk' {print ($ 1)} '`#echo" / $ cnt1 = $ cnt1 "#echo" / $ cnt2 = $ cnt2 "

= $ CNT1 in the echo table, the result of the number of files generated = $ CNT2. "IF [$ CNT1-EQ $ CNT2]; the echo" congratulation, unload successful! "else echo" error: Export record number and table If the number of records is different, please check if there is a certain domain of CHAR (n) in the table with /n."fi

MV WK_ $ TABLE.TXT $ TABLE.TXTRM -F CNT1.TXT ########### *********************** ***************** Profile #file: unload.ctl # 分隔 SEP = "," output field, no space connection; if there is a space, you need to use = right "" causes fields = "ORG_BIND_ACC_NBR, ACCT_MONTH_ID, ACCT_CYCLE_TMPL_ID, ACCT_CYCLE_ID, PARTITION_ID, SOURCE_TYPE, BUSINESS_KEY, CALL_TYPE, ORG_ACC_NBR_A, ORG_ACC_NBR_B, START_DATE, START_TIME, CALL_DURATION, END_DATE, END_TIME, TOTAL_METER_VALUE, PURE_METER_VALUE, SWITCH_AREA_CODE, SWITCH_ID, ORG_FILE_NAME, CDR_POS_IN_FILE, TRUNK_IN, TRUNK_OUT, CARD_NO, CARD_TYPE, IS_PREFEE, ORG_FEE1 ORG_FEE2 ORG_FEE3 ORG_FEE, VISIT_AREA_CODE_A, SWITCH_TYPE, BILLING_TYPE, OPER_SEQUENCE "# condition, no condition is empty where =" where ACCT_MONTH_ID> = '200410' "# sort fields, no condition is null order = # "ORDER BY START_DATE" # Output file storage directory DESTDIR =.