Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Migration Issues - from DBase to Oracle.
Max Lambertini
> I'm fairly new to Oracle system, and I am using it to build web-based
> application w/ PL/SQL. While SQL+ sometimes fails when reporting errors
> (wrong line numbers...) the system in itself is very solid, and I've
> come to like it...
>
> Now, I have to populate an Oracle table with data from a DBase table.
> My question is: are there any tools that can read data from DBase
> sources and put into Oracle?
>
> Thanks in advance,
>
> Max Lambertini
I have loaded FoxPro files (Dbase 3 format) into Oracle through Sql*Loader. The Sql*Loader control commands used are not (I think) documented in the manual. We originally generated control files for importing from Foxpro DBF's with an Oracle utility called DB3PREP, but that is more than 3 years ago and that program is no longer available. It won't run except with Oracle's obsolete (and dropped) proprietary extended memory manager. To do Sql*Loader imports from DBF files now, I use one of the old control files as a paradigm to construct one for the new import.
Here's a Foxpro structure listing of a table:
CNTCDS.DBF
Code Page: 0 Field Field Name Type Width Dec Index Collate Nulls 1 CNTCD Numeric 5 No 2 CNTNAME Character 31 No 3 ADRFMT Numeric 5 No 4 ADRLANGS Character 6 No 5 POSTFR1 Character 11 No 6 POSTTO1 Character 11 No 7 POSTFR2 Character 11 No 8 POSTTO2 Character 11 No 9 POSTFR3 Character 11 No 10 POSTTO3 Character 11 No 11 POSTFR4 Character 11 No 12 POSTTO4 Character 11 No 13 POSTFR5 Character 11 No 14 POSTTO5 Character 11 No 15 ISALRG Numeric 5 No 16 IPAZONE Numeric 5 No 17 ISEC Logical 1 No 18 MXSTATE Logical 1 No 19 UCLAST2 Logical 1 No 20 VALAREAC Logical 1 No ** Total ** 172
And here's the Sql*Loader control file for importing the data into Oracle:
LOAD DATA
INFILE "CNTCDS.DBF" "DB3 172"
REPLACE
INTO TABLE CNTCDS
(CNTCD POSITION(2:6) INTEGER EXTERNAL, CNTNAME POSITION(7:37) CHAR, ADRFMT POSITION(38:42) INTEGER EXTERNAL, ADRLANGS POSITION(43:48) CHAR, POSTFR1 POSITION(49:59) CHAR, POSTTO1 POSITION(60:70) CHAR, POSTFR2 POSITION(71:81) CHAR, POSTTO2 POSITION(82:92) CHAR, POSTFR3 POSITION(93:103) CHAR, POSTTO3 POSITION(104:114) CHAR, POSTFR4 POSITION(115:125) CHAR, POSTTO4 POSITION(126:136) CHAR, POSTFR5 POSITION(137:147) CHAR, POSTTO5 POSITION(148:158) CHAR, ISALRG POSITION(159:163) INTEGER EXTERNAL, IPAZONE POSITION(164:168) INTEGER EXTERNAL, ISEC POSITION(169:169) CHAR, MXSTATE POSITION(170:170) CHAR, UCLAST2 POSITION(172:172) CHAR, VALAREAC POSITION(173:173) CHAR)
Date fields are handled like this:
In the FoxPro structure listing:
2 INPDT Date 8 No In the Sql*Loader control file: INPDT POSITION(11:18) DATE 'YYYYMMDD' NULLIF (INPDT = BLANKS),
Regards,
Doug
Received on Mon Jul 14 1997 - 00:00:00 CDT