Results 1 to 5 of 5

Thread: Sql Loader

  1. #1
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    Question Unanswered: Sql Loader

    Hi ,
    Can one use case, decode inside an control file to format the data ?
    If so are can some one give me some examples of using case?

    Thanks

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Yes, you can use decode, substr etc as shown in eg below

    Code:
    load data 
    infile 'ss.dat'
    into table ss
    append 
    fields terminated by ','
    TRAILING NULLCOLS
    (col1 "decode(:col1,'X',:col1,'XXXXX')",
    col2,
    col3 "substr(:col3,3,3)" )
    Oracle can do wonders !

  3. #3
    Join Date
    Feb 2004
    Location
    india
    Posts
    23

    sqlloader

    hi,

    Thanks but how to use case ...?
    say when x = something then insert into table y values '10'
    ..etc

    please do let me know.

    thanks again

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    simply put, YES.

    here is an example:
    Code:
    1    -- This is a sample control file
    2    LOAD DATA
    3    INFILE 'sample.dat'
    4    BADFILE 'sample.bad'
    5    DISCARDFILE 'sample.dsc'
    6    APPEND
    7    INTO TABLE emp
    8    WHEN (57) = '.'
    9    TRAILING NULLCOLS
    10  (hiredate SYSDATE,
          deptno POSITION(1:2)  INTEGER EXTERNAL(2)
                  NULLIF deptno=BLANKS,
           job    POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
                  NULLIF job=BLANKS  "UPPER(:job)",
           mgr    POSITION(28:31) INTEGER EXTERNAL 
                  TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
           ename  POSITION(34:41) CHAR 
                  TERMINATED BY WHITESPACE  "UPPER(:ename)",
           empno  POSITION(45) INTEGER EXTERNAL 
                  TERMINATED BY WHITESPACE,
           sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE
                  "TO_NUMBER(:sal,'$99,999.99')",
           comm   INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
                  ":comm * 100"
        )
    link to docs:
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2004
    Location
    india
    Posts
    23
    But one does not understand what this (57) is !!!! is it the position of the column or what ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •