Hi All ,
I have a use case in sql loader where in i have two columns A and B in a Table.
My Use case for control file is to get B from csv ( which is a mandatory column) . A is optional in CSV . if A is populated take that value for A . If not default if from B.
Thing i have tried is :
B , A EXPRESSION : "NVL( :A, :B) " ,
This is failing saying that
Invalid bind variable A in SQL string for column A.
Any ideas ? I know we could define a function. . But any better way to do with control files itself ?
Remove the word EXPRESSION.
SCOTT@orcl12c> host type test.csv
SCOTT@orcl12c> host type test.ctl
into table a_table
fields terminated by ','
a "nvl (:a, :b)")
SCOTT@orcl12c> create table a_table
2 (b varchar2(5),
3 a varchar2(5))
SCOTT@orcl12c> host sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 188.8.131.52.0 - Production on Fri Nov 15 13:46:40 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 2
2 Rows successfully loaded.
Check the log file:
for more information about the load.
SCOTT@orcl12c> select * from a_table
2 rows selected.