SQL-LOADER:Loading combined Physical Records

Loading Combined Physical Records

There may be chances that we get the data in which each row starts with a special character like '@','#','$' etc.Then we will have to use continueif to load the data from such kind of flat file into staging table.

There may be scenerios where we need to load two or three rows of the flat-file as a single row in the staging table then we will use Concatenate option to acheive that requirement.

USING CONTINUEIF:

load data

infile *

replace

continueif this (1)= '@'

into table vnk_emp

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

c1,

c2,

c3,

c4 char terminated by whitespace

)

BEGINDATA

@1,2,

    3,4

@5,6,

    7,8

USING CONCATENATE:

load data

infile *

replace

concatenate 2

into table vnk_emp

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

c1,

c2,

c3,

c4 

)

BEGINDATA

1,2,

3,4

5,6,

7,8

Staging table:

Create table vnk_emp(c1 number,

                                   c2 number,

                                   c3 number,

                                   c4 number

                                  )