Sqloader by simple example

load_data.sh

DB_USER=MY_USER

DB_PASS=MY_PASS

DB_INST=orainst

CTL_FILE=/starhome/iu/workarea/loader_ora/raf_msisdn_set_details_sfi_b.ctl

SQL_LOADER_LOG_FILE=/starhome/iu/workarea/loader_ora/raf_msisdn_set_details_sfi_b.log

sqlldr ${DB_USER}/${DB_PASS}@${DB_INST} CONTROL=${CTL_FILE} LOG=${SQL_LOADER_LOG_FILE}

raf_msisdn_set_details_sfi_b.ctl

OPTIONS(DIRECT=TRUE)

LOAD DATA

INFILE '/starhome/iu/workarea/loader_ora/input_file.txt'

BADFILE '/starhome/iu/workarea/loader_ora/input_file.bad'

DISCARDMAX 0

APPEND

INTO TABLE raf_msisdn_set_details_sfi_b

FIELDS TERMINATED BY ','

(

MSISDN_SET_ID    "TRIM (:MSISDN_SET_ID)",

MSISDN_FROM      "TRIM (:MSISDN_FROM)",

MSISDN_TO        "TRIM (:MSISDN_TO)",

MSISDN_DESC      "TRIM (:MSISDN_DESC)",

IS_PREFIX        "TRIM (:IS_PREFIX)",

TS_LAST_MODIFIED SYSDATE

)

replace tabs with comma in input file

less input_file.txt_comma | sed 's/\t/,/g' > input_file.txt

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

OPTIONS(DIRECT=TRUE)

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

By default, direct load is not activated in sqlldr, and the load is via regular flow.

To avoid archiving during load, one can activate direct load by:

A. set table to be NOLOGGING

B. add OPTIONS(DIRECT=TRUE) to sqlldr control file

This will cause a direct path load, and when sqlldr is running, this message with appear:

Path used:      Direct

For example:

OPTIONS(DIRECT=true)

LOAD DATA

INFILE '/starhome/sfi_usage/input/imput.dat'

BADFILE '/starhome/sfi_usage/ctl/input.bad'

DISCARDMAX 0

TRUNCATE

INTO TABLE table_hourly_a

FIELDS TERMINATED BY ','

(

KEY1                          "TRIM (:KEY1)",

IMSI                          "TRIM (:IMSI)"

)