Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27

    Question Unanswered: load data infile

    Hi,

    I'm trying to load lots of data into a table in Oracle9i from a text file where the data is not separated by a special character like ",". I did some research and I found out that this could be done with the following command.

    LOAD DATA
    INFILE 'C:\Documents and Settings\Gerard\Bureaublad\organisaties.txt'
    INTO TABLE oganisatiegegevens
    (
    LRRORG POSITION(01:10) INT,
    LRRNM1 POSITION(11:40) CHAR,
    LRRNM2 POSITION(41:70) CHAR,
    LRRSTR POSITION(71:94) CHAR,
    LRRHNR POSITION(95:100) INT,
    LRRTHN POSITION(101:112) CHAR,
    ...
    ...
    )

    (I've made the command a little shorter because it's very big. About 70 lines)

    I give the right path where the text file is saved, I give the right table name, I give the right column names and I give the right positions. I checked them all.

    When I run this command in Oracle I get the following error:

    SP2-0552: Bindvariable "100" is not declarated.

    Does anyone know what this means and how I could solve the problem?

    Joachim

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: load data infile

    A bind variable is a place-holder for a value, and is indicated by a colon before the variable name, e.g. :empno, :100 (numeric bind variables are allowed, and are often used in SQL generated by Oracle itself).

    So somewhere in your control file is the text ":100" in a place where Oracle thinks it is a bind variable. I wonder if it could be a typo in one of your POSITION declarations? e.g.
    LRRHNR POSITION(95):100 INT,

    This is just a guess - but search for ":100" and you should find it.

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27

    Re: load data infile

    Hi,

    Thnx for your reply.

    I'm sorry, but that's not it. The only line that has :100 in it is de following line:

    LRRHNR POSITION(95:100) INT,

    And that one is correct. I have checked all the other positions and they are also correct.

    So it must be something else.

    Joachim

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: load data infile

    Post the complete control file, maybe someone can spot the problem - it might not be in the few lines you posted earlier.

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27

    comlete file

    The complete file:

    LOAD DATA
    INFILE 'C:\Documents and Settings\Gerard\Bureaublad\organisaties.txt'
    INTO TABLE oganisatiegegevens
    (
    LRRORG POSITION(01:10) INT,
    LRRNM1 POSITION(11:40) CHAR,
    LRRNM2 POSITION(41:70) CHAR,
    LRRSTR POSITION(71:94) CHAR,
    LRRHNR POSITION(95:100) INT,
    LRRTHN POSITION(101:112) CHAR,
    LRRPCD POSITION(113:119) CHAR,
    LRRPLT POSITION(120:144) CHAR,
    LRRSTRS POSITION(145:145) CHAR,
    LPRPPN POSITION(146:151) CHAR,
    LRRPPC POSITION(152:158) CHAR,
    LRRPWP POSITION(159:183) CHAR,
    LRRPNRS POSITION(184:184) CHAR,
    LRRTEL POSITION(185:199) CHAR,
    LRRFAX POSITION(200:214) CHAR,
    LREADR POSITION(215:264) CHAR,
    LRRNAC POSITION(265:270) CHAR,
    LRRNN1 POSITION(271:276) CHAR,
    LRRNN2 POSITION(277:282) CHAR,
    LRRNN3 POSITION(283:288) CHAR,
    LRRNN4 POSITION(289:294) CHAR,
    LRRNN5 POSITION(295:300) CHAR,
    LRRNN6 POSITION(301:306) CHAR,
    LRRNN7 POSITION(307:312) CHAR,
    LRRNN8 POSITION(313:318) CHAR,
    LRRNN9 POSITION(319:324) CHAR,
    LRRAOC POSITION(325:327) INT,
    LRRAWR POSITION(328:333) INT,
    LRRSTS POSITION(334:334) CHAR,
    LRRKVK POSITION(335:346) CHAR,
    LRRHNV POSITION(347:347) CHAR,
    LRRSTN POSITION(348:479) CHAR,
    LFFHN1 POSITION(480:539) CHAR,
    LRRHN2 POSITION(540:599) CHAR,
    LRRHN3 POSITION(600:659) CHAR,
    LRRRVC POSITION(660:662) CHAR,
    LRRJAR POSITION(663:667) INT,
    LRRPRC POSITION(668:669) CHAR,
    LRRGMC POSITION(670:674) INT,
    LRRCEB POSITION(675:678) INT,
    LRREGG POSITION(679:682) INT,
    LRRCOR POSITION(683:686) INT,
    LRRKVD POSITION(687:689) CHAR,
    LRRJARRK POSITION(689:696) INT,
    LRRREC POSITION(697:710) CHAR,
    LRROMZ POSITION(711:724) CHAR,
    LRROMC POSITION(725:738) CHAR,
    LRROKC POSITION(739:741) INT,
    LRRFIL POSITION(742:747) INT,
    LRBNC1 POSITION(748:751) CHAR,
    LRBNC2 POSITION(752:755) CHAR,
    LRBNC3 POSITION(756:759) CHAR,
    LRBNC4 POSITION(760:763) CHAR,
    LRBNC5 POSITION(764:767) CHAR,
    LRBNC6 POSITION(768:771) CHAR,
    LRROGI POSITION(772:772) CHAR,
    LRROGO POSITION(773:779) INT,
    LRROGW POSITION(780:792) INT,
    LRRECA POSITION(793:793) CHAR,
    LRRIMP POSITION(794:794) CHAR,
    LRREXP POSITION(795:795) CHAR,
    LRWADR POSITION(796:845) CHAR,
    LRROPH POSITION(846:846) CHAR
    )

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: comlete file

    I think I see your problem - you are trying to run this as a command in SQL Plus, right? I just tried it myself and got:

    Bind variable "100" not declared.

    You can't do this in SQL Plus, you need to run SQL Loader. The LOAD DATA command has to be saved in a "control" file e.g. org.ctl

    Then you can run SQL Loader from the OS prompt something like this:

    sqlldr80 userid=tony/secret control=org.ctl

  7. #7
    Join Date
    Jan 2003
    Location
    The Netherlands, Oudkarspel
    Posts
    27

    Re: comlete file

    Hey thnx!!! It worked.

    Joachim

Posting Permissions

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