Sample for SQL Loader

Http://psoug.org/reference/sqlloader.html




Upload_Cylinder_Control.ctl

LOAD DATA

INFILE          'Upload_Cylinder.txt'
BADFILE         'Upload_Cylinder_BAD.bad'
DISCARDFILE 'Upload_Cylinder_DSC.dsc'

--REPLACE         -- delete the data before loading
APPEND

INTO TABLE cyl_cylinder_base_data
TRAILING NULLCOLS
( 
bot_no          TERMINATED BY ' ',
    cylnd_no        TERMINATED BY ' ',
    bot_head_spec    TERMINATED BY ' ',
    load_gas_id     INTEGER EXTERNAL TERMINATED BY ' ',
load_gas_ingred TERMINATED BY ' ',
    cylnd_mtspc      TERMINATED BY ' ',
    arv_dat         DATE "DD-Mon-YY" TERMINATED BY ' ',
    av_non_chg_gas  TERMINATED BY ' ',
    ast_no          TERMINATED BY ' ',
    kd              TERMINATED BY ' ',
    cylnd_inner_vol INTEGER EXTERNAL TERMINATED BY ' ',
    OPLT_DAT        DATE "DD-Mon-YY" TERMINATED BY ' ',
    ltm_isp_dat     DATE "DD-Mon-YY" TERMINATED BY ' ',
    lst_isp_dat     DATE "DD-Mon-YY" TERMINATED BY ' ',
    ast_ben          TERMINATED BY ' ',
    ast_ben_id      TERMINATED BY ' ',
    nr_tx_id        TERMINATED BY ' ',
    pressure_test    TERMINATED BY ' ',
    capqty          INTEGER EXTERNAL TERMINATED BY ' ',
fct_id TERMINATED BY ' ',
enable TERMINATED BY ' ',
place_loc_no TERMINATED BY ' ',
place_loc_id TERMINATED BY ' '
)

INTO TABLE cyl_cylnd_trans_d
TRAILING NULLCOLS
(
        TX_ID POSITION(148:151),
        TX_DAT POSITION(90:99) DATE "DD-Mon-YY",
        TX_CATE POSITION(100:101),
        TX_TO POSITION(144:147),
        TX_TO_CATE POSITION(142:143),
        GAS_ID POSITION(23:29),
        CYLND_NO POSITION(08:19),
        BOT_NO POSITION(01:07),
        DONE POSITION(100:101),
        FCT_ID POSITION(164:167)
)


SQLLDR_UploadCylinder_TEST.bat

@ECHO OFF
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
SET /P yn=您確定要執行鋼瓶新購上傳到 TEST 的動作嗎？[y/n]：

IF %yn%==y GOTO yes
IF %yn%==Y GOTO yes
IF %yn%==n GOTO no
IF %yn%==N GOTO no

:no
ECHO 取消執行！！！
PAUSE
EXIT

:yes
SQLLDR apps/apps@mlgtpeerpt1.boclh.com.tw:1571/crp2 CONTROL=Upload_Cylinder_Control.ctl LOG=Upload_Cylinder_Log.log
::TYPE NULL > Upload_Cylinder.txt
ECHO 執行完成！！！
PAUSE

EXIT