SQL Loader problem

From: Marek Ksiazek <mksiazek_at_elka.pw.edu.pl>
Date: Wed, 14 Mar 2001 09:30:24 +0100
Message-ID: <3AAF2C20.CF5A412B_at_elka.pw.edu.pl>

Hi all!
I'm working on the following problem:
I have a table with the structure like this:

SQL> describe epdocuments

 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                              NOT NULL NUMBER(10)
 TYPE                            NOT NULL VARCHAR2(3)
 DOCNO                           NOT NULL NUMBER(12)
 REGION                          NOT NULL VARCHAR2(20)
 STATUS                          NOT NULL VARCHAR2(20)
 UPLOADED                        NOT NULL DATE
 UPLOADEDBY                      NOT NULL NUMBER(8)
 LASTMODIFIED                             DATE
 LASTMODIFIEDBY                           NUMBER(8)
 CONTENT                         NOT NULL LONG
 VENDOR                          NOT NULL VARCHAR2(30)
 USERGROUP                       NOT NULL VARCHAR2(30)
 PROJECTNO                                VARCHAR2(8)
 SITENO                          NOT NULL VARCHAR2(30)
 ACCEPTDATE                               DATE
 VENDORCUSTOM                             VARCHAR2(40)
 DOCDATE                                  DATE
 DRADATE                                  DATE

and want to upload data from flat file. Due to limitation (2000 characters) in length of quoted string in SQL insert statement I have to use SQL Loader to do this.
The *.utl file looks like this:

LOAD DATA
INFILE *
APPEND
CONTINUEIF THIS (1) = '*'
INTO TABLE EPDOCUMENTS
FIELDS TERMINATED BY '|'

(ID             INTEGER EXTERNAL,
TYPE            CHAR(20),
DOCNO           DECIMAL EXTERNAL,
REGION          CHAR(20),
STATUS          CHAR(20),
UPLOADED        DATE(20) "DD-MM-YYYY HH24:MI:SS",
UPLOADEDBY      DECIMAL EXTERNAL,

LASTMODIFIED DATE(20) "DD-MM-YYYY HH24:MI:SS", LASTMODIFIEDBY DECIMAL EXTERNAL,

CONTENT         CHAR(10000),
VENDOR          CHAR(30),
USERGROUP       CHAR(30),
PROJECTNO       CHAR(8),
SITENO          CHAR(30),
ACCEPTDATE      DATE(20) "DD-MM-YYYY HH24:MI:SS",
VENDORCUSTOM    CHAR(40),
DOCDATE         DATE(20) "DD-MM-YYYY HH24:MI:SS",
DRADATE         DATE(20) "DD-MM-YYYY HH24:MI:SS")

BEGINDATA
*5124|po|6401882|katowice|new|05-03-2001 13:08:58|0|05-03-2001
13:08:58|0|
*<PO>
*<Header>ZPR6;Relocation MSC Kat B -> MSC Kat D;PTC;ZWUT
S.A.;;;6401882;5
Katowice;50030;Katowice_50030_MSC;MSC50030A;50030.09;02.03.2001;MRAK;;;16.03.2001;DEM;22368,00</Header>

*<Object>0010;70276171;L30501-P70-X;PU: PCM

System;100390;1,000;SZT;50000</Object>


*<Object>0020;70276172;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0030;70276173;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0040;70276174;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0050;70276175;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0060;70276176;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0070;70276177;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0080;70276178;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0090;70276179;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0100;70276180;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0110;70276181;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0120;70276182;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0130;70276183;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0140;70276184;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0150;70276185;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0160;70276186;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0170;70276187;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0180;70276188;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0190;70276189;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0200;70276190;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0210;70276191;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0220;70276192;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0230;70276193;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0240;70276194;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0250;70276195;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0260;70276196;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0270;70276197;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0280;70276198;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0290;70276199;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0300;70276200;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0310;70276201;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0320;70276202;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0330;70276203;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>


*<Object>0340;70276204;L30501-P70-X;PU: PCM


System;100390;1,000;SZT;50000</Object>

*<Object>0350;70276007;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0360;70276008;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0370;70276009;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0380;70276010;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0390;70276011;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0400;70276012;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0410;70276013;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0420;70276014;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0430;70276015;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0440;70276016;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0450;70276017;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0460;70276018;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0470;70276019;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0480;70276020;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*<Object>0500;70276022;L30501-P4-X;PU:

EC;100166;1,000;SZT;50000</Object>
*</PO>
*|Siemens|BSS|50030.09|50030|||02.03.2001|16.03.2001|

The SQL Loader is invoked by:
sqlldr userid=user/pass_at_database control=insert_test.utl log=insert_test.log direct=true bindsize=10000 OK, I can upload data.
The problem is that I have to preserve structure of document in long field (each tag has to be in new line).
The question is: how to preserve CR/LF characters? I tried to use PRESERVE BLANKS clause but without any success (SQL Loader returns errors)
Any idea?
Thanks in advance.
Maras Received on Wed Mar 14 2001 - 09:30:24 CET

This message: [ Message body ]
Next message: mike triplett: "Re: Oracle Tools (TOAD)"
Previous message: Nuno Souto: "Re: Is it possible to create a System tablespace with Local managed extent?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message