Oracle中SPOOL导出文本去除回车tab键等功能键脚本.docVIP

Oracle中SPOOL导出文本去除回车tab键等功能键脚本 if [ $# -ne 1 ] then echo Usage: $0 didate return 1 fi dt=$1 DBUSER=crdit66 cd /db2data/ncd_data/ mkdir $dt;cd $dt sqlplus -s reader/reader@credit ! CREATE OR REPLACE FUNCTION REPLACEMENT(FILENAME VARCHAR) RETURN VARCHAR AS RETURNVAL VARCHAR2(32767); V_COUNTER NUMBER; V_CHAR VARCHAR2(2); BEGIN V_COUNTER := 0; RETURNVAL:= FILENAME; WHILE V_COUNTER 28 LOOP IF V_COUNTER 10 THEN V_CHAR := 0||V_COUNTER; ELSE V_CHAR := V_COUNTER; END IF; RETURNVAL:= REPLACE(RETURNVAL,CHR(V_CHAR)); V_COUNTER :=V_COUNTER+1; END LOOP; RETURN RETURNVAL; END; / COMMIT; SET COLSEP |; SET ECHO OFF; SET FEEDBACK OFF; SET HEADING OFF; SET LINESIZE 30000; --SET NUMWIDTH 20; SET PAGESIZE 0; SET TERMOUT OFF; SET TRIMSPOOL ON; SET TRIMOUT ON; SET VERIFY OFF; SET ARRAYSIZE 5000; --set filepath=D:\dbtxt\cd_data SPOOL ./CUS_BASE.txt; SELECT CUS_ID|||||CUS_NAME|||||CUS_SHORT_NAME|||||CUS_TYPE|||||CERT_TYPE|||||CERT_CODE|||||CONTACT_NAME||||| PHONE|||||OPEN_DATE|||||TRANS_CUS_ID|||||REG_CODE||| FROM $DBUSER.CUS_BASE; SPOOL ./CUS_COM.txt; SELECT INNER_CUS_ID|||||CUS_ID|||||MNG_BR_ID|||||CUS_TYPE|||||CUS_NAME|||||CUS_EN_NAME|||||CUS_SHORT_NAME||||| CERT_TYPE|||||CERT_CODE|||||COM_COUNTRY|||||COM_LCL_FLG|||||CUS_BANK_REL|||||COM_HOLD_STK_AMT|||||INVEST_TYPE|||||COM_SUB_TYP||||| COM_SCALE|||||COM_HOLD_TYPE|||||LOAN_CARD_FLG|||||LOAN_CARD_ID|||||LOAN_CARD_PWD|||||LOAN_CARD_EFF_FLG|||||LOAN_CARD_AUDIT_DT||||| COM_INS_CODE|||||COM_CLL_TYPE|||||REPLACEMENT(COM_CLL_NAME)|||||COM_STR_DATE|||||COM_EMPLOYEE|||||COM_INS_REG_DATE|||||COM_INS_EXP_DATE||||| COM_INS_ANN_DATE|||||COM_INS_ORG|||||REG_CODE|||||REG_TYPE|||||ADMIN_ORG|||||APPR_ORG|||||APPR_DOC_NO|||||REG_STATE_CODE||||| REG_AREA_CODE2|||||REG_AREA_CODE3|||||REG_AREA_NAME|||||REG_ADDR|||||EN_REG_ADDR|||||REG_CUR_TYPE|||||REG_CAP_AMT||||| PAID_CAP_CUR_TYPE|||||PAID_CAP_AMT|||||FUN_SOURCE|||||LEGAL_NAME||