Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Migration Issues - from DBase to Oracle.

Re: Migration Issues - from DBase to Oracle.

From: Douglas Dunnigan <douglas_at_yogananda-srf.org>
Date: 1997/07/14
Message-ID: <01bc90a6$1247d680$b3ba0c26@systems-douglas>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US