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 ?

 

Thanks,

Bibin

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

    Remove the word EXPRESSION.

     

     

    SCOTT@orcl12c> host type test.csv

    bval1,aval1,

    bval2,,

     

    SCOTT@orcl12c> host type test.ctl

    load data

    infile test.csv

    into table a_table

    fields terminated by ','

    trailing nullcols

    (b,

    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 12.1.0.1.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

     

    Table A_TABLE:

      2 Rows successfully loaded.

     

    Check the log file:

      test.log

    for more information about the load.

     

    SCOTT@orcl12c> select * from a_table

      2  /

     

    B     A

    ----- -----

    bval1 aval1

    bval2 bval2

     

    2 rows selected.

Legend

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