Attach Dbase IV Data Files To Oracle Database

Overview

This past end of year brought us new requirements to download “compliance” files from government entities and the additional burden of maintaining these file updated monthly.

Some of these files are ‘Dbase IV’ (.dbf file format) which made it a manual procedure to load and maintain the data in Oracle tables.

To deal with this situation and be able to automate this process we coded a PL/SQL procedure to transform these “.dbf” files to pipe-delimited files and use as part of the ETL script.

This “ATTACH_DBF_FILE” procedure  provides the means to convert a ‘Dbase IV’ database file to a pipe-delimited file and attach the converted file to an oracle database as an external table.

Perhaps there already exists a Unix utility that does this, but with budget and time constraints we had to code our own.

How it Operates
 

1.

Open the dbf file as BLOB.

2.

Analyze and extract metadata for the structure and fields from the dbf file header:

a.      Field name
b.      Type
c.      Length
d.      Decimals
e.      Auto-Increment

3.

Build and execute DDL to create the external table.

4.

Extract the data from the dbf file and write to pipe delimited file.
Parameters

PROCEDURE Attach_Dbf_File
(
  P_Directory         VARCHAR2
, P_File_Name        VARCHAR2
, P_Schema            VARCHAR2
, P_Delimiter         VARCHAR2    DEFAULT '|'
)

Parameter   Description

Directory             Directory object pointing to the location of the dbf
                             and the pipe delimited file on the server.

File name            Name of the dbf file
Schema               Target schema name
Delimiter             Default is pipe “|”

Code is here

attach-dbf-file.sql

NO WARRANTY

THE PROGRAM IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.

IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW THE AUTHOR WILL BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT LIMITED TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER PROGRAMS), EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.