Re: Help with Version 7 loader, Non-Fixed length records

paladin_at_along.dialix.oz.au (Alex Long) wrote:

>Greetings All,
 

>Firstly, I _have_ RTFM, at last count, it was the 7th time.
 

>I need to load a lot of data that has been exported from a Paradox table
>as a comma delimited file.
 

>I can't find a way to load data into Oracle 7, unless it's a fixed
>length record. All the examples that I saw seem to predicate a fixed
>length record.
 

>The keywords DELIMITED BY and it's other variations form
>version 6 don't work.
 

>I have been forced to convert every line in the data into an INSERT
>statement using editors like Brief and Ed 4 Windows, which is very time
>consuming and a literal pain the rear.
 

>Can someone please confirm that non fixed length records _can_ be
>loaded, and either show me the syntax required, or give me a pointer to
>there in the manual I can find the syntax.

This is the example control file, ulcase1.ctl, found in $ORACLE_HOME/rdbms/demo:

LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC)
BEGINDATA

12,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLEVELAND
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN"

In the server utilities guide, Chapter 5, Case 1 "Loading Variable Length Data" it walks thru this control file explaining what it does and why.

Look at the examples in $ORACLE_HOME/rdbms/demo/ulcase*.ctl for other methods. Each of these control files are documented in chapter 5 of the above manual.

>--
> , \ / ,
> / \ )\__/( / \
> / \ (_\ /_) / \
> ______________________/_____\__\_at_ @/___/_____\________________
>|paladin.along.dialix.oz.au |
>|Alex Long |
>|Adelaide, South Australia |
>|_______________________________________________________________|
>|Billy, in one of his nice new sashes, |
>|Fell in the fire and was burnt to ashes; |
>|Now, although the room grows chilly, |
>|I haven't the heart to poke poor Billy. Harry Graham. |
>+_______________________________________________________________+
> | /\ / \\ \ /\ |
> | / V )) V \ |
> |/ ` // ' \|
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Fri Oct 13 1995 - 00:00:00 CET