Betrieve to Oracle conversion

How to convert betrive databases into oracle?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ron WarshawskyCommented:

1. Use

Pervasive SQL 2000

This database for Windows NT and Novell NetWare lets independent software developers create custom database applications. It supports Inscribe Visual Basic scripting, triggers and stored procedures; transactional logging; and files up to 64GB in size. The database can convert Btrieve databases into a relational SQL format.




Abstract:            This bulletin answers questions on using          btrieve files with SQL*Loader and BTRPREP.

                  Using SQL*Loader with Btrieve Files  
      The DOS, OS/2, and NetWare versions of SQL*Loader can load data  
      directly from Btrieve files into an Oracle database. Only  
      fixed-length Btrieve files are supported by SQL*Loader. Load-  
      ing data from a complex Btrieve file can require a lengthy  
      data definition in the SQL*Loader control file. To assist in  
      the preparation of the SQL*Loader control file, a utility  
      program called BTRPREP is included with SQL*Loader.  
  Using BTRPREP  
      BTRPREP creates both a .SQL file to define the Oracle table  
      corresponding to the Btrieve file, and a .CTL file to load  
      the data into the Oracle table. The .SQL file is used by  
      SQLDBA and the .CTL file is used by SQL*Loader.  
           Note: The BTRPREP utility only runs under DOS or OS/2.  
                 The NetWare version of SQL*Loader, however, can  
                 load files after they have been prepared with  
  Options: -D  = Drops table (and contents) and creates new table  
                 with same name.  
           -T  = Specify tablename (changes tablename specified  
                 in the .SQL file). Format: -T<tablename>  
  Btrieve Field Types  
       Btrieve field type              Oracle datatype  
       ----------------------------    -----------------------------  
       string                          varchar2 *  
       integer                         number  
       float                           number  
       date                            date **  
       time                            date ***  
       decimal                         number  
       money                           number  
       logical                         varchar2  
       numeric                         number  
       bfloat                          varchar2  
       lstring                         varchar2  
       zstring                         varchar2  
       unsigned binary                 number  
       autoincrement                   number  
       *     VARCHAR2 fields must be less than 2000 characters long.  
       **    Date fields in Btrieve files will convert to date  
             datatypes in the Oracle database. The time component  
             of the date datatype will convert to 12:00 am, while  
             date component remains intact.  
       ***   Time fields in Btrieve files will convert to date  
             datatypes in the Oracle database. When converted, the  
             date component will default to the value of the first  
             day of the current month, while time component remains  
       Note: DO not use any Oracle reserved words as field names.  
             See "Oracle7 Server SQL Language Reference Manual"  
  Invoking BTRPREP  
      BTRPREP is invoked from the DOS or OS/2 command line as in:  
         C:\> BTRPREP server\vol:directory\btrfile  
      Note: To use this method you must be logged onto the server and  
            have read access to the file.  
         C:\> BTRPREP <full_dos_path>\btrfile  
      Note: Use this method after you have copied the file to the  
            local PC hard drive. This will aviod network traffic.  
            Any file name extension is assumed legal, if none is  
            used, extension .BTR is assumed.  
      The column names in the .SQL file created by invoking BTRPREP  
      will have the format: FIELD0, FIELD1, FIELD2, ... FIELDx  
      Note: BTRPREP only reads key fields found in the Btrieve file  
            header. You must alter the .SQL file and the .CTL file  
            to include any other fields that are not key fields.  
            C:\> btrprep PC_USER\SYS:ORACLE7\BTR\EMP.BTR  
            Will create this SQL file EMP.SQL:  
                 CREATE TABLE EMP  
                    (FIELD0 NUMBER(4),  
                     FIELD1 VARCHAR2(10),  
                     FIELD2 VARCHAR2(9),  
                     FIELD3 NUMBER(4),  
                     FIELD4 DATE,  
                     FIELD5 NUMBER(7,2),  
                     FIELD6 NUMBER(7,2),  
                     FIELD7 NUMBER(2)) ;  
            C:\> sqldba  
            SQLDBA> connect username/password  
            SQLDBA> @emp.sql  
            Will also create this .CTL file EMP.CTL:  
                 LOAD DATA  
                 INFILE "EMP.BTR" "BTRIEVE"  
                 INTO TABLE EMP  
                    (FIELD0    POSITION(2:5) INTEGER EXTERNAL,  
                     FIELD1    POSITION(6:15) CHAR,  
                     FIELD2    POSITION(16:24) CHAR,  
                     FIELD3    POSITION(25:28) INTEGER EXTERNAL,  
                     FIELD4    POSITION(29:36) DATE 'DDMMYYYY',  
                     FIELD5    POSITION(37:43) DECIMAL EXTERNAL,  
                     FIELD6    POSITION(44:50) DECIMAL EXTERNAL,  
                     FIELD7    POSITION(51:52) INTEGER EXTERNAL)  
            C:\> sqlload username/password control=emp.ctl  
  Using SQL*Loader with the .CTL file  
       Caution: If the Btrieve file has the same name as a table  
                already existing in your database, the new records  
                from the Btrieve file are inserted into the existing  
                table. Because no check is made, this could result  
                in duplicate records.  
       If you want to load data from only selected fields in each  
       Btrieve record, delete the corresponding fields' definitions  
       from the .SQL file and .CTL file. Use an ASCII text editor  
       to delete the lines. This only applies to Btrieve and  
       dBASE III PLUS files.  
       Since Btrieve files do not have field names, you may want to  
       edit the files to change the names of any fields before  
       loading. Edit the .SQL and .CTL files with an ASCII text  
       editor and type over the names of the fields you want to  
       change. It might also be recommended that you edit any  
       CHAR datatypes in the .CTL file to include lengths.  
          Ex:    FIELD2    POSITION(16:24) CHAR(9),  
  BTRPREP Examples  
       Example 1  
          C:\> btrprep -D c:\orados\btr\campsite.btr  
          C:\> sqldba  
          SQLDBA> connect scott\tiger@x:orasrv  
          SQLDBA> @c:\campsite.sql  
          C:\> slqload scott\tiger@x:orasrv control=c:\campsite.ctl  
       Example 2  
          C:\> btrprep c:\orados\btr\datalot.btr  
          C:\> sqldba  
          SQLDBA> connect scott\tiger@x:orasrv  
          SQLDBA> @c:\datalot.sql  
          C:\> slqload scott\tiger@x:orasrv control=c:\datalot.ctl  
       Example 3  
          C:\> btrprep -Tassets c:\orados\btr\debits.btr  
          C:\> sqldba  
          SQLDBA> connect scott\tiger@x:orasrv  
          SQLDBA> @c:\debits.sql  
          C:\> slqload scott\tiger@x:orasrv control=c:\debits.ctl  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ron WarshawskyCommented:

 Why you are not accepting answer?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.