Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: sqlldr when question

    I'm trying to do a selective append via sqlldr. I read on orafaq the when-clause can be used but it doesn't allow "or":
    Code:
    LOAD DATA
      INFILE  'data.txt' BADFILE  'data.bad' DISCARDFILE 'data.dis'
      APPEND
      INTO TABLE test
      WHEN value = "YES" or value = "NO"
    (
    value char(10)
    )
    Spreading it out into multiple INTO's doesn't work: the NO's are discarded.

    Is the control file a good place to be selective on the input? How do I get both the YES and NO records and discard anything else?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Externalized Oganization?

    Quote Originally Posted by Kaiowas
    I'm trying to do a selective append via sqlldr. I read on orafaq the when-clause can be used but it doesn't allow "or":
    ... etc ...
    Is the control file a good place to be selective on the input? How do I get both the YES and NO records and discard anything else?
    WHEN only allows the AND operator.

    You could create an ORGANIZATION EXTERNAL table on that file and load with DML statements.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    Quote Originally Posted by LKBrwn_DBA
    You could create an ORGANIZATION EXTERNAL table on that file and load with DML statements.
    So what you're saying is that in this case it's impossible using the ctl file alone. Originally I was thinking about a trigger that reacts on YES/NO/anything but I wanted the records in the discard file that could fit but didn't meet the requirement. I couldn't think of a trigger that denies access in that way but I guess raising an error could do it?

    Anyway, the bigger question I had is whether or not using the ctl file like this is a good idea in general or that triggers are more commonly used because they are closer to the table. I guess I answered that one already

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Actually, you may do that - it will just require two steps.

    Here's an example: I'll create a TEST table with two columns - "value" may be YES, NO or something different:
    Code:
    SQL> create table test
      2  (value varchar2(10),
      3   name  varchar2(20));
    
    Table created.
    
    SQL>
    This is the control file. Note that I've used two INTO TABLE TEST statements which are distinguished by the WHEN VALUE clause. Also, pay attention to POSITION(1) parameter in the second INSERT INTO; without it, loading will be unsuccessful. Sample data is located at the end of the control file.
    Code:
    LOAD DATA 
    INFILE *
    REPLACE
    INTO TABLE test
      WHEN value = "NO"
      FIELDS TERMINATED BY ","
      (       
      value,
      name
      )
    INTO TABLE test
      WHEN value = "YES"
      FIELDS TERMINATED BY ","
      (       
      value POSITION(1),
      name
      )
    
    BEGINDATA
    YES,Little
    YES,Foot
    XYZ,No name
    NO,Kaiowas
    Finally, loading session and the result:
    Code:
    SQL> $sqlldr scott/tiger@ora10 control=test.ctl log=test.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on Sri Svi 6 07:41:04 2009
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 3
    Commit point reached - logical record count 4
    
    SQL> select * from test;
    
    VALUE      NAME
    ---------- --------------------
    YES        Little
    YES        Foot
    NO         Kaiowas
    
    SQL>
    For more information, check SQL*Loader Control File Reference documentation.

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    Wonderful! It does exactly what I need!

    What I understand from the documentation you refer to, topic "Relative Positioning Based on the POSITION Parameter", the POSITION (1) repositions the read to column one instead of moving to the next column. Which explains the reason of discarding what it did.

    Without it expects data such as:
    YES,Little,YES,Big
    YES,Foot,NO,Toe
    XYZ,No name,ZYX,Unknown
    NO,Kaiowas,NO,Brasil

    Thanx!

Posting Permissions

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