Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Posts
    4

    Unanswered: sqlloader: how to use substr in when-clause correctly?

    Hello!

    I'm having a problem that I thought to be rather common, but trying to look it up in the "Oracle Database 10g2 Utilities_b14215.pdf" didn't help. After that I've surfed through numerous threads but no luck so far.

    I'm having a tab-delimited file (x'09') e. g. name, userid, persnr. The values for the userids begin with either 'P', 'R' or 'T' e. g. P2198, P2199, R7288, T1229.

    I want to load only the records with userids beginning with 'P'.

    Isolating a single record with a controlfile like this works splendidly:

    OPTIONS (SKIP=1)
    LOAD DATA
    INFILE UserlistLoader.dat
    APPEND
    INTO TABLE Z_USERLIST
    WHEN USERID = 'P2198'
    FIELDS TERMINATED BY x'09'
    TRAILING NULLCOLS
    (name, userid, persnr)

    But every attempt at using SUBSTR in the when-clause fails.

    This:

    OPTIONS (SKIP=1)
    LOAD DATA
    INFILE UserlistLoader.dat
    APPEND
    INTO TABLE Z_USERLIST
    WHEN SUBSTR(USERID, 1, 1) = 'P'
    FIELDS TERMINATED BY x'09'
    TRAILING NULLCOLS
    (name, userid, persnr)

    ends in an SQL*Loader-350: Syntax-Error.

    This

    OPTIONS (SKIP=1)
    LOAD DATA
    INFILE UserlistLoader.dat
    APPEND
    INTO TABLE Z_USERLIST
    WHEN "SUBSTR(:USERID, 1, 1)" = 'P'
    FIELDS TERMINATED BY x'09'
    TRAILING NULLCOLS
    (name, userid, persnr)

    ends in an SQL*Loader-403: Referenced column USERID not present in table Z_USERLIST.

    But IT IS PRESENT - as the first example proves!! I've found that the column should be preceded by ':' but that obviously isn't the issue.

    What am I doing wrong?

    Any hint is greatly appreciated...thanks a lot in advance!

    Mathew

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any hint is greatly appreciated.
    Use EXTERNAL TABLE against the raw data file.
    Then CREATE VIEW
    WHERE USERID LIKE 'P%'
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2008
    Posts
    4

    Client Access only

    Hello anacedent!

    Sorry, my mistake: I didn't specify the situation clearly enough.

    But first: Thanks a lot for the hint - I might have a use for that in the future!

    The thing is I was hoping for a "direct" sqlloader-solution since I'm dealing with a situation where the loading happens via client access only (not our own infrastructure - I'm simply a temporary guest with limited db access in the customers organization).

    If I understood the utility documentation correctly then the access driver runs in the db server and would need the creation of directory which is not possible in my case at the moment.

    Any other chance of achieving what I intended within "normal" sqlloader syntax (as a client-based utility)? I mean without a workaround like loading everything and thereafter deleting the unwanted records?

    Thanks!

    Mathew

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The WHEN clause is pretty limited in its usage.
    You can only use simple expressions without a function call.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There *might* be a workaround, if you can make your input text file fixed length so that you could reference each field by their positions.

    Unfortunately, you can not use a function in the WHEN clause of the control file - all SQL*Loader offers is full field name or position specifier (see the documentation).

    Obviously, full field name isn't an option in your case. However, alternative option might do the job. Here's a simple example - see if you can use it.

    This is the table:
    Code:
    SQL> create table test (col varchar2(20));
    A sample control file (which includes data):
    Code:
    load data
      infile *
      replace
    into table test
    when (1:2) = '21'
      (
       col char(20)
      )
    
    begindata
    2103020000
    0903010000
    As you can see, if the first two characters are '21', I'll load the record.

    Loading session and the final result:
    Code:
    SQL> $sqlldr scott/tiger control=test.ctl log=test.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on ╚et O×u 19 09:15:15 2009
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 1
    
    SQL> select * from test;
    
    COL
    --------------------
    2103020000
    
    SQL>

Posting Permissions

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