Using CONTINUEIF in sqlldr control file

From: Morten <morten_at_kikobu.com>
Date: Wed, 30 Jan 2002 11:36:13 +0100
Message-ID: <3C57CC9D.6030603_at_kikobu.com>

Hi. Can any of you clarify the usage of CONTINUEIF to me:

LOAD DATA
INFILE *
CONTINUEIF NEXT (1) = '%'
INTO TABLE poll
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' ( id "poll_seq.nextval",

    title,
    founder CONSTANT 'admin',
    enabled CONSTANT 'N',

    startdate DATE "dd-mm-yyyy",
    enddate   DATE "dd-mm-yyyy",
    allowAnonymous CONSTANT 'Y',
    active CONSTANT 'N',

    grp CONSTANT 'pub',
    domain CONSTANT 'test'
)
BEGINDATA
|Title 1|24-03-1973|25-03-1973
|Tit&le 2|24-03-1974|25-03-1974
|"Title' 3"|"24-03-1975"|"25-03-1975"

This fails. But if I remove the 'CONTINUEIF' line, it works. The errors:

bash-2.03$ cat demo.log

SQL*Loader: Release 8.1.6.1.0 - Production on Tue Jan 29 18:55:44 2002

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Control File: demo.ctl

Data File:      demo.ctl
    Bad File:     demo.bad

    Discard File: none specified

   (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 65536 bytes
Continuation:   1:1 = 0X25(character '%'), in next physical record
Path used:      Conventional

Table POLL, loaded from every logical record. Insert option in effect for this table: INSERT

     Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
ID                                  FIRST     *   |  O(") CHARACTER
      SQL string for column : "poll_seq.nextval"
TITLE                                NEXT     *   |  O(") CHARACTER
FOUNDER                                                   CONSTANT
      Value is 'admin'
ENABLED                                                   CONSTANT
      Value is 'N'
STARTDATE                            NEXT     *   |  O(") DATE dd-mm-yyyy
ENDDATE                              NEXT     *   |  O(") DATE dd-mm-yyyy
ALLOWANONYMOUS                                            CONSTANT
      Value is 'Y'
ACTIVE                                                    CONSTANT
      Value is 'N'
GRP                                                       CONSTANT
      Value is 'pub'
DOMAIN                                                    CONSTANT
      Value is 'talentum'

Record 1: Rejected - Error on table POLL, column ENDDATE. Column not found before end of logical record (use TRAILING NULLCOLS) Record 2: Rejected - Error on table POLL, column ENDDATE. Column not found before end of logical record (use TRAILING NULLCOLS) Record 3: Rejected - Error on table POLL, column ENDDATE. Column not found before end of logical record (use TRAILING NULLCOLS)

Table POLL:

    0 Rows successfully loaded.
    3 Rows not loaded due to data errors.     0 Rows not loaded because all WHEN clauses were failed.     0 Rows not loaded because all fields were null.

Space allocated for bind array:                  65534 bytes(62 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         3
Total logical records discarded:        0

Run began on Tue Jan 29 18:55:44 2002
Run ended on Tue Jan 29 18:55:44 2002

Elapsed time was:     00:00:00.28
CPU time was:         00:00:00.03

[Quoted] Can any of you explain this behaviour to me? Ideally, I would like to be able to have the loader to handle a multiline 'title' column, eg.

|"Title that br

%eaks line"|22-12-1990|21-04-1994

This was the kind of behaviour I'd expect the above control file to deal with. Any insight much appreciated.

Morten Received on Wed Jan 30 2002 - 11:36:13 CET