Re: skipping fields in SQL*Loader for Oracle 8.0.5

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 15 Oct 1999 08:33:56 -0400
Message-ID: <MB4HOKoVDB92qFYuHA=rlLOo7EKW@4ax.com>

A copy of this was sent to "Me" <annardo_at_earthlink.net> (if that email address didn't require changing) On Thu, 14 Oct 1999 21:52:20 -0400, you wrote:

>thanks for the help. However, I do have one last question. Is there a
>limitation on the number of rows that I can have in the control file. When I
>have 20 columns to load everything is fine. But if I add another column then
>I get an error message stating that not all variable are bounded.  Any
>ideas?
>
>I tried changing the readsize and bind to a higher value but no luck.  Each
>row is only 312 bytes. I tried reading in around 312 bytes and the 4096
>bytes.
>

create a table like this:

tkyte_at_8i> desc t

 Name                    Null?    Type
 ----------------------- -------- ----------------
 FIELD1                           VARCHAR2(25)
 FIELD2                           VARCHAR2(25)

and use this control file to see if you can reproduce on your system. On mine, with 25 columns -- it works fine. I cannot reproduce your error in 8.0.5 on solaris.

LOAD DATA
INFILE *
REPLACE
INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,1,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,3,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,4,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,5,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,6,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,7,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,8,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,9,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,10,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,11,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,12,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,13,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,14,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,15,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,16,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,17,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,18,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,19,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,20,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,21,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,22,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,23,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,24,NULL,',')"
)

INTO TABLE T
(
  FIELD1 position(1:1024) "delimited.word(:field1,2,NULL,',')",   FIELD2 position(1:1) "delimited.word(:field1,25,NULL,',')"
)

BEGINDATA
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25

>
[snip]

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 15 1999 - 07:33:56 CDT