Oracle 12c - SQL * Loader conditional load

Question

I'm trying to use SQL Loader and while inserting the data into the tables I need to check some conditions and insert the data.
Example:

CASE COLUMNA  
WHEN 'NULL'
   -- INSERT NULL VALUE IN IT INSTEAD OF STRING 'NULL'  
ELSE  
   -- INSERT THE DATA AS IS
END

Can we use these case statements in the SQL Loader control file? Couldn't find good examples for this any where.

Koushik Ravulapelli · Accepted Answer · 2016-12-29 15:53:40Z

1

Try adding the codition in your control file like:

load data
  APPEND INTO TABLE XXX
  fields terminated by "\t"
  TRAILING NULLCOLS
  ( --Condition which you can add.
    START_DATE "CASE WHEN length(:START_DATE ) < 10 THEN null ELSE :START_DATE END"      

  )

where START_DATE isthe column of the table

edited Dec 29, 2016 at 15:53

Koushik Ravulapelli

1,13811 silver badges30 bronze badges

answered Dec 29, 2016 at 14:40

XING

9,9264 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary_W · Accepted Answer · 2016-12-29 16:13:11Z

0

An FYI that for more complex tests or other needs that you can reuse you can also call functions or package members that return a value:

...
START_DATE "UTILITY_PKG.VALIDATE_DATE(:START_DATE)"

Or queries (have to enclose in perentheses):

START_DATE "(select sysdate from dual)",

answered Dec 29, 2016 at 16:13

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Collectives™ on Stack Overflow

Oracle 12c - SQL * Loader conditional load

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related