CODEWITHSUNDEEP
oracle-databasesql-loader
user10231003
user10231003

Reputation: 35

SQL Loader data insert: can we use default value as another column value?

I am trying data import using SQL*Loader. During import, if col1 value is blank I need to replace it with col0 value. Like these I need to set default values for multiple columns.

I tried setting it as DEFAULTIF col1=col0 by adding length check. Assuming if col1 value is blank, its length will be 0 and it will use execute defaultif condition. But its giving error saying

Expecting quoted string or hex identifier, found "col0".

Can someone help and explain how to set default value as another column value?

Upvotes: 1

Views: 8437

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

A simple NVL function call does the job.

Here's an example:

SQL> create table test (col0 number, col1 varchar2(10), col2 number);

Table created.

Control file: note col2 "nvl(:col2, :col0)" which will put col0 value into col2 if col2 doesn't exist. In my example, it is the second row, 2,yyy, which doesn't contain value for col2 so it'll be populated with col0 value, i.e. 2.

load data 
infile *
replace
into table test
fields terminated by ","
trailing nullcols
(
col0,
col1,
col2 "nvl(:col2, :col0)"
)

begindata
1,xxx,111
2,yyy,
3,zzz,333

Loading session & the result:

SQL> $sqlldr scott/tiger@xe control=test02.ctl log=test02.log

SQL*Loader: Release 11.2.0.2.0 - Production on Pet Kol 17 21:48:59 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2
Commit point reached - logical record count 3

SQL> select * From test;

      COL0 COL1             COL2
---------- ---------- ----------
         1 xxx               111
         2 yyy                 2
         3 zzz               333

SQL>

Upvotes: 3

Related Questions