Re: Problem with TEXT in Datatype LONG

Alternatively you can tell SQL*Loader how long the entire record is: this will allow you to load entire documents into a LONG column. If you don't you either cannot have new lines within your data or you are forced to use fixed length records which is inefficient .
e.g.

LOAD DATA
INFILE "" "VAR" -- 1st is filename, 2nd is 'VAR' or 'FIXED recordlength>' APPEND
INTO TABLE APP_CHEMICAL
FIELDS TERMINATED BY ',' ENCLOSED BY '"' (

 "COUNTRY" CHAR(2)
,"SERIAL" CHAR
,"TYPE" CHAR(2)
,"VERSION" CHAR
,"CATEGORY" CHAR(2)
,"CODE" CHAR(64000) -- LONG format

and this type of data:
01464"~~","847098","A1","0","EP","[01]
<001> 018 | R00868 G1105 G1092 D01 D19 D18 D31 D50 D76 D86 F31 F30 | H0328 |
L9999 L2391 | L9999 L2108 L2095 | M9999 M2108 M2095 | S9999 S1047 S1014 | H0000
<002> 018 | ND01 | ND07 | Q9999 Q7341 Q7330 | Q9999 Q9234 Q9212 | Q9999 Q9289
Q9212 | Q9999 Q7410 Q7330 | K9416
<003> 018 | N9999 N7078 N7034 N7023 | N9999 N7147 N7034 N7023 | K9552 K9483 |
K9687 K9676 | K9676-R | K9712 K9676 | N9999 N6780-R N6655 | N9999 N6600 | N9999 N6177-R | N9999 N6224 N6177 | N9999 N6315 N6268
<004> 018 | R05268 D01 D11 D10 D23 D22 D31 D41 D50 D75 D85 F71 | A999 A475 |
A999 A771
<005> 018 | R01669 D00 D09 C- 4A | A999 A135 | S9999 S1456-R | A999 A771
[02]
<001> 018 | R00363 G0555 G0022 D01 D12 D10 D51 D53 D58 D69 D82 F- 7A | H0000 |
S9999 S1058 S1014
<002> 018 | ND01 | ND07 | Q9999 Q7341 Q7330 | Q9999 Q9234 Q9212 | Q9999 Q9289
Q9212 | Q9999 Q7410 Q7330 | K9416
<003> 018 | N9999 N7078 N7034 N7023 | N9999 N7147 N7034 N7023 | K9552 K9483 |
K9687 K9676 | K9676-R | K9712 K9676
<004> 018 | R05268 D01 D11 D10 D23 D22 D31 D41 D50 D75 D85 F71 | A999 A475 |
A999 A771
<005> 018 | R01778 D00 D09 C- 4A | A999 A135 | S9999 S1456-R | B9999 B5209 B5185
B4740 | A999 A771
[03]
<001> 018 | R00326 G0044 G0033 G0022 D01 D02 D12 D10 D51 D53 D58 D82 | H0000 |
S9999 S1285-R | P1150 | P1161
<002> 018 | ND01 | ND07 | Q9999 Q7341 Q7330 | Q9999 Q9234 Q9212 | Q9999 Q9289
Q9212 | Q9999 Q7410 Q7330 | K9416
<003> 018 | B9999 B5221 B4740 | B9999 B5243-R B4740
"

Roel wrote:

> If you would like to import Ascii text into a single record, maybe this
> could help.
>
> I had a similar problem before.  What I did was, I developed a Forms program
> for that table, using Notepad I opened the text file that I wished to
> import, copied all the text onto the clipboard and pasted it on my LONG
> column.  And voila!  I was able to place the text into the Long column.
>
> Roel
>
> Joerg Reiner wrote in message <36383063.AAF970A4_at_gsf.de>...
> >Hello,
> >
> >I think I have a really basic problem about
> >the ORACLE Data Types specially LONG.
> >
> >I want to import ASCII Text into ORACLE LONG-Fields.
> >Is there any limitation about the Text Length? As I new there
> >can be up to 2^31 Bytes in there.
> >But the IMPORT-Tool give an length error message like the following:
> >
> >--> Record 3: Rejected - Error on table "UMLSADMIN"."MRDEF1", column DEF.
> >--> Field in data file exceeded maximum specified length
> >
> >The physical line length at this record is just 546 Characters.
> >
> >So my question: What goes wrong there.
> >
> >Hope Anybody could help me
> >
> >Thanks a lot, Joerg