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
Open the dbf file as BLOB.
Analyze and extract metadata for the structure and fields from the dbf file header:
a. Field name
Build and execute DDL to create the external table.
Extract the data from the dbf file and write to pipe delimited file.
, P_File_Name VARCHAR2
, P_Schema VARCHAR2
, P_Delimiter VARCHAR2 DEFAULT '|'
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
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.