Docs for DBO.com - DBase to Oracle data utility - Tue 03-28-1989

See bottom of file for latest revision info, file DBO.TUT for brief tutorial.

DBO is a public domain utility which makes the process of transferring PC data
from dBase and Clipper files into Oracle tables much less tedious. It will read
in a dBase type DBF file, analyze the field structure, and generate some small
program and control files to assist Oracle in loading the data.

*----------------------------------------*
| Syntax: |
| |
| DBO filename[.ext] [/nNAME] [/r] |
| |
| /n specify new primary Name |
| /r display Reserved symbols |
*----------------------------------------*

filename[.ext] -name of dBase or Clipper DBF-type input file

[/nNAME] -optional primary Name to use in building other names

[/r] -display Oracle's Reserved symbol list


DBO requires an input file (1st parameter) which is in dBase III format. It
will analyze the fields in this file, then output either 2 or 3 new files to
help get the data into Oracle. The files are the BLD (build) file, the CTL
(control) file, and the CVT (convert) file. They are all 7-bit ASCII text
files, and can be edited if necessary.

The BLD file is a small program to create the new Oracle table. Like the CVT
file below, it is executed from the SQL*PLUS command level via either the
'START' or '@' commands.

The CTL file is the control-file needed by the Oracle Data Loader (ODL)
utility. It specifies the field alignments of the raw data file, and contains
information about the Oracle table being loaded.

The CVT file is another small program to convert any date fields into Oracle's
internal DATE format. It simply creates a temporary table, inserts the values
from the original table using the TO_DATE() function on any date fields, then
drops the original table and renames the temporary one. This file will only be
generated by DBO if there are date fields in the dBase file.

The default name of each of these files consists of the primary name of the
input file with the appropriate extent added. The raw data file is expected to
use the same name but with a TXT extent (dBase's default), and the Oracle table
will be named with just the primary name alone.

If you want another name to be used, you can enter it immediately following the
/n option switch. For example, we'd use /nTEST (note that no space is allowed
between the /n and the name).

As part of the preparation for creating it's output files, DBO will check the
table name and each of the field names against a list of Oracle's reserved
symbols. If we get a hit, the characters "_$" will be appended to the name and
the user notified.

DBO will display a list of the names it plans to use, and will ask for
confirmation before actually writing the output files to disk.

The list of reserved symbols can be displayed by using the /r switch. This is
for reference, when you're about to go in and edit the created files to change
the names which caused conflicts. You can also just leave the files as they
are, if you don't mind having the _$ on the names.


---NOTES:

-You may want to edit the files DBO generates, to add things like the NOT NULL
clause to field definitions. The DBO program will at least get you close.

-You will _need_ to edit these files if you intend to use them in a *nix
environment. Specifically, the CR must be stripped from each CR/LF end of
line, and the record size value (in the .bld file) must be decremented by 1.
Thanks to Paul Cirelli (Letchworth Village) for pointing this out. His notes
on this are as follows:

"...you have to decrement the record size by one
and you need lowercase names. Also, when a
datafile is sent across TCP/IP with FTP the
CR/LF combination marking the end of an ASCII
record is maintained. Xenix uses LF only. This
preservation of the CR made Oracle a little
nuts.

Solution: Take the ASCII file to floppy, suck
it into Xenix with doscp (doscp automatically
strips the offending CR's)."

Note that I modified the program to use only lowercase names, so that part is
taken care of automatically...


-Oracle can accept table names up to 30 characters long. You can use these
long names with DBO by using the /n option. The filenames will look a bit
odd, but will still work (DOS will truncate the primary part of the name.)
Perhaps an easier method is to simply edit the files after DBO is done with
them, changing the table name to whatever you like.

-The files generated by DBO will always be created in the logged directory.
Full pathnames (including drive) are supported for naming the dBase input
file, but will be stripped off if included in the /nNAME option.

-The program skips MEMO fields, as dBase cannot output these into the SDF text
files. It would simply throw off our field alignments if we included the
field definition in the CTL file. Whenever a memo field is hit, DBO will
display a warning to the effect that the field has been skipped.

-DBO requires files which comply with the dBase III data file definition. It
will *NOT* work with the older dBase II formats.

-DBO will set the DOS errorlevel code to 0 == success or no arguments found; 1
== input file read error; 2 == invalid dBase header; 3 == error writing output
file; 4 == Oracle table name more than 30 characters long; 255 == program
interrupted by the user (Ctrl-C or Escape pressed at a prompt).



---REVISION HISTORY:

*** Mar 28 1989

-Changed all names to lower case, for *nix users. You will still need to
decrement the record size by 1 in the .bld file, and use the *nix utilities to
strip out the CR from each CR/LF end-of-line. See NOTES, above.

*** Nov 09 1988

-Added generation of 'CVT' file when date fields are present. This is a
program to complete the process of converting the dBase date fields to Oracle
format. The previous version left this step up to the user, and it's a fairly
tedious step. No reason not to finish the job if we've come this far.

-Added test for Oracle reserved symbols, implemented as a simple search against
a character array. This array, which is hard-coded in DBO.H, is alphabetized
and set up to allow easy additions as future releases of Oracle introduce new
reserved symbols. When reserved symbols are found, DBO renames them by
appending "_$" to the end, and notifies the user. Oracle currently has many of
these reserved symbols, and a large number of them are common usage words
which are quite likely to show up as dBase field or file names.

-Added /r switch, to display these reserved names. Simply prints out the
character array used in the test above, with a header to indicate which
revision of Oracle this applies to. Since this array is already sitting there
taking up space, we may as well get some extra mileage out of it.

-Simplified the syntax. It was too complicated, and the options were rarely
needed. When they _were_ needed, they were generally all needed together.
The new syntax allows setting all of the names at once, basing them on
whatever is entered immediately following the new /n switch.

-Added routines to request confirmation after all of the names and renaming
messages have been displayed, and prior to writing any files to disk.

-Changed file extent of program which creates the Oracle table from SQL to BLD,
to avoid any confusion with existing SQL programs.

-Changed 'C' header file to DBO.H, and customized it for DBO. Moved all setup
information to this file for legibility. Quite tightly coupled, with lots of
globals, but it's so simple that who cares?


*** Aug 24 1988

-Initial release.



Bob Trevithick GEnie address: R.TREVITHICK
Information Services Department
N.Y.S. OMRDD, Newark Office
703 E. Maple Ave
Newark, NY 14513

Voice: (315) 331-1700