sqlloader

 

options(skip=1)

load data

truncate into table sqlldr_ex

fields terminated by ","

optionally enclosed by '"'

trailing nullcols

(id,name,jdate date "yyyymmdd")

 

xxchidam_multiple.ctl_edit

 

sqlldr apps/apps data=xxchidam_datafile.dat control=xxchidam_multiple.ctl_edit direct=true discard=chidam.csv

 

** dont use skip option when using when clause

** we need to use position from second when clause

** use discard option to capture disard records

 

CREATE TABLE tab1(ID NUMBER, NAME VARCHAR2(100),jdate DATE);

 

CREATE TABLE tab2(ID NUMBER, NAME VARCHAR2(100),jdate DATE);

 

CREATE TABLE tab2(ID NUMBER, NAME VARCHAR2(100),const varchar2(100),func varchar2(100),default_v varchar2(100),created_date date);

 

load data

append

into table tab1

when name='chidam'

fields terminated by ","

optionally enclosed by '"'

trailing nullcols

(

     name POSITION(1) CHAR,

     id,

     jdate date "yyyymmdd"

)

into table tab2

when (name='divi')

fields terminated by ","

optionally enclosed by '"'

trailing nullcols

(

       name POSITION(1) CHAR,

       id,

       jdate date "yyyymmdd"

)

into table tab3

when (name='aksh')

fields terminated by ","

optionally enclosed by '"'

trailing nullcols

(

       name POSITION(1) CHAR "TRIM(:name)",

       x filler,

       id,

       const constant "CHIDAM",

       func "sqlldr_fun",

       default_v "NVL(:default_v,'default')",

       created_date "SYSDATE"

)

 

begindata

id,name,date

chidam,1,20200120

divi,2,20200122

aksh,filler_data,100,,,,,

 

 

 

sqlldr parfile=chidam.parfile

parfile=chidam.parfile

chidam.parfile content

                       userid=ot@pdborc/Abcd1234

                       control=email.ctl

                       log=email.log

                       bad=email.bad

                       data=email.dat

                       direct=true

 

 

 

POSITION BASED

 

$ cat employee-fixed.txt

200JasonTechnology5500

300MaylaTechnology7000

400NishaTechnology9500

500RandyTechnology6000

 

$ cat sqlldr-fixed.ctl

load data

 infile '/home/ramesh/employee-fixed.txt'

 into table employee

 fields terminated by ","

 ( id position(1:3), name position(4:8), dept position(9:18), salary position(19:22) )

 

CREATE TABLE xxlin_load_file_sqlldr(ID NUMBER, file_name VARCHAR2(100),file_data CLOB)

 

load data

truncate into table xxlin_load_file_sqlldr

fields terminated by ","

optionally enclosed by '"'

trailing nullcols

(

id,

file_name,

file_data lobfile(file_name) terminated by eof

)

begindata

100,/home/oracle/xxchidam/xxchidam_datafile_1.dat

101,/home/oracle/xxchidam/xxchidam_datafile_2.dat

102,/home/oracle/xxchidam/xxchidam_datafile_3.dat