This discussion is archived
1 Reply Latest reply: Nov 15, 2013 1:48 PM by Barbara Boehmer RSS

Defaulting values SQL Loader

891071 Newbie
Currently Being Moderated

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 ?




  • 1. Re: Defaulting values SQL Loader
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    Remove the word EXPRESSION.



    SCOTT@orcl12c> host type test.csv




    SCOTT@orcl12c> host type test.ctl

    load data

    infile test.csv

    into table a_table

    fields terminated by ','

    trailing nullcols


    a "nvl (:a, :b)")


    SCOTT@orcl12c> create table a_table

      2    (b  varchar2(5),

      3     a  varchar2(5))

      4  /


    Table created.


    SCOTT@orcl12c> host sqlldr scott/tiger control=test.ctl log=test.log


    SQL*Loader: Release - 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


    Table A_TABLE:

      2 Rows successfully loaded.


    Check the log file:


    for more information about the load.


    SCOTT@orcl12c> select * from a_table

      2  /


    B     A

    ----- -----

    bval1 aval1

    bval2 bval2


    2 rows selected.


  • Correct Answers - 10 points
  • Helpful Answers - 5 points