3 Replies Latest reply: Jul 30, 2008 4:30 PM by 427941 RSS

    sqlldr control file WHEN clause--how to achieve LIKE '%'

    427941
      Hi all,

      I'm on 10g and trying to exclude some records during a sqlldr call. So I turn to the WHEN clause and find out that is pretty limited. I cannot use functions in it and I cannot use the LIKE operator with it. Here is a sample of what i'm trying to do:

      LOAD DATA
      INFILE *
      INTO TABLE vc_t
      WHEN account_name like '%ONE'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"'
      TRAILING NULLCOLS
      (
      ACCOUNT_NAME,
      TYPE ,
      AMOUNT
      )
      BEGINDATA
      "ACCOUNT_ONE", "T", 1.23
      "ACCOUNT_TWO", "T", 1.24
      "ACCOUNT_THREE", "T", 1.25
      "ACCOUNT_FOR", "T", 1.26
      "ACCOUNT_ONE", "T", 1.27
      "ACCOUNT_SIX", "T", 1.28


      Of course this is only a test because in real life the data will not look exactly like this and the prefix will not always begin with "ACCOUNT". it can be anything. But the WHEN clause is key and it doesn't work. Please help!

      Thanks,

      Piyush
        • 1. Re: sqlldr control file WHEN clause--how to achieve LIKE '%'
          damorgan
          Look at Demo 5 here:
          http://www.psoug.org/reference/sqlloader.html

          But you are correct that SQL*Loader tends to be a blunt instrument. So why not define your file as an external table and query it any way you wish?
          http://www.psoug.org/reference/externaltab.html
          • 2. Re: sqlldr control file WHEN clause--how to achieve LIKE '%'
            MaximDemenko
            I agree with Dan, that with external tables this task becomes trivial, because you have much bigger toolset with sql than with syntax of control file, but in situtations where you are constrained to sqlldr it still can be done:
            SQL> create table VC_T
              2  (
              3    ACCOUNT_NAME VARCHAR2(30),
              4    TYPE         VARCHAR2(1),
              5    AMOUNT       NUMBER
              6  )
              7  ;
            
            Table created.
            
            SQL> !cat vc_t.ctl
            LOAD DATA
            INFILE *
            INTO TABLE vc_t
            TRUNCATE
            WHEN AUX_SUFFIX = 'ONE'
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '"'
            TRAILING NULLCOLS
            (
            AUX_PREFIX BOUNDFILLER POSITION(2) terminated by "_",
            AUX_SUFFIX BOUNDFILLER POSITION(*) terminated by '"',
            ACCOUNT_NAME position(1),
            TYPE ,
            AMOUNT
            )
            BEGINDATA
            "ACCOUNT_ONE", "T", 1.23
            "ACCOUNT_TWO", "T", 1.24
            "ACCOUNT_THREE", "T", 1.25
            "ACCOUNT_FOR", "T", 1.26
            "ACCOUNT_ONE", "T", 1.27
            "ACCOUNT_SIX", "T", 1.28
            
            SQL> !sqlldr userid=scott/tiger control=vc_t.ctl
            
            SQL*Loader: Release 10.2.0.4.0 - Production on Sun Jul 27 12:57:41 2008
            
            Copyright (c) 1982, 2007, Oracle.  All rights reserved.
            
            Commit point reached - logical record count 6
            
            SQL> select * from vc_t;
            
            ACCOUNT_NAME                   T     AMOUNT
            ------------------------------ - ----------
            ACCOUNT_ONE                    T       1.23
            ACCOUNT_ONE                    T       1.27
            Best regards

            Maxim
            • 3. Re: sqlldr control file WHEN clause--how to achieve LIKE '%'
              427941
              Thanks everyone for posting their comments. We looked at the external table concept. It certainly will give me all the abilities I would want in the future, but at this point we're debating whether it's worth it to switch over. The other idea worked also although I'm not sure what kind of performance impact there will be if I have to define a few extra FILLER fields.