Import or load DBF file to Oracle


I use this package to import data from dbase file – foxpro (dbf) to oracle table,

–this is spec
CREATE OR REPLACE PACKAGE DBF2ORA
AS
— PROCEDURE TO A LOAD A TABLE WITH RECORDS
— FROM A FOXPRO FILE.

— USES A BFILE TO READ BINARY DATA AND DBMS_SQL
— TO DYNAMICALLY INSERT INTO ANY TABLE YOU
— HAVE INSERT ON.

— P_DIR IS THE NAME OF AN ORACLE DIRECTORY OBJECT
— THAT WAS CREATED VIA THE CREATE DIRECTORY
— COMMAND

— P_FILE IS THE NAME OF A FILE IN THAT DIRECTORY
— WILL BE THE NAME OF THE FOXPRO FILE

— P_TNAME IS THE NAME OF THE TABLE TO LOAD FROM

— P_CNAMES IS AN OPTIONAL LIST OF COMMA SEPARATED
— COLUMN NAMES. IF NOT SUPPLIED, THIS PKG
— ASSUMES THE COLUMN NAMES IN THE FOXPRO FILE
— ARE THE SAME AS THE COLUMN NAMES IN THE
— TABLE

— P_SHOW BOOLEAN THAT IF TRUE WILL CAUSE US TO JUST
— PRINT (AND NOT INSERT) WHAT WE FIND IN THE
— FOXPRO FILES (NOT THE DATA, JUST THE INFO
— FROM THE FOXPRO HEADERS….)
PROCEDURE LOAD_TABLE (
P_DIR IN VARCHAR2,
P_FILE IN VARCHAR2,
P_TNAME IN VARCHAR2,
P_CNAMES IN VARCHAR2 DEFAULT NULL,
P_SHOW IN BOOLEAN DEFAULT FALSE
);
END DBF2ORA;
/

–this body
CREATE OR REPLACE PACKAGE BODY MIS_ODS.DBF2ORA
AS
— MIGHT HAVE TO CHANGE ON YOUR PLATFORM!!!
— CONTROLS THE BYTE ORDER OF BINARY INTEGERS READ IN
— FROM THE FOXPRO FILE
BIG_ENDIAN CONSTANT BOOLEAN DEFAULT TRUE;

TYPE DBF_HEADER IS RECORD (
VERSION VARCHAR2 (25), — FOXPRO VERSION NUMBER
YEAR INT, — 1 BYTE INT YEAR, ADD TO 1900
MONTH INT, — 1 BYTE MONTH
DAY INT, — 1 BYTE DAY
NO_RECORDS INT, — NUMBER OF RECORDS IN FILE,
— 4 BYTE INT
HDR_LEN INT, — LENGTH OF HEADER, 2 BYTE INT
REC_LEN INT, — NUMBER OF BYTES IN RECORD,
— 2 BYTE INT
NO_FIELDS INT — NUMBER OF FIELDS
);

TYPE FIELD_DESCRIPTOR IS RECORD (
NAME VARCHAR2 (11),
TYPE CHAR (1),
LENGTH INT, — 1 BYTE LENGTH
DECIMALS INT — 1 BYTE SCALE
);

TYPE FIELD_DESCRIPTOR_ARRAY IS TABLE OF FIELD_DESCRIPTOR
INDEX BY BINARY_INTEGER;

TYPE ROWARRAY IS TABLE OF VARCHAR2 (4000)
INDEX BY BINARY_INTEGER;

G_CURSOR BINARY_INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;

— FUNCTION TO CONVERT A BINARY UNSIGNED INTEGER
— INTO A PLSQL NUMBER
FUNCTION TO_INT (P_DATA IN VARCHAR2)
RETURN NUMBER
IS
L_NUMBER NUMBER DEFAULT 0;
L_BYTES NUMBER DEFAULT LENGTH (P_DATA);
BEGIN
IF (BIG_ENDIAN)
THEN
FOR I IN 1 .. L_BYTES
LOOP
L_NUMBER :=
L_NUMBER
+ ASCII (SUBSTR (P_DATA, I, 1)) * POWER (2, 8 * (I – 1));
END LOOP;
ELSE
FOR I IN 1 .. L_BYTES
LOOP
L_NUMBER :=
L_NUMBER
+ ASCII (SUBSTR (P_DATA, L_BYTES – I + 1, 1))
* POWER (2, 8 * (I – 1));
END LOOP;
END IF;

RETURN L_NUMBER;
END TO_INT;

FUNCTION MYTRIM (P_STR IN VARCHAR2)
RETURN VARCHAR2
IS
I NUMBER;
J NUMBER;
V_RES VARCHAR2 (100);
BEGIN
FOR I IN 1 .. 11
LOOP
IF ASCII (SUBSTR (P_STR, I, 1)) = 0
THEN
J := I;
EXIT;
END IF;
END LOOP;

V_RES := SUBSTR (P_STR, 1, J – 1);
RETURN V_RES;
END MYTRIM;

PROCEDURE GET_HEADER (
P_BFILE IN BFILE,
P_BFILE_OFFSET IN OUT NUMBER,
P_HDR IN OUT DBF_HEADER,
P_FLDS IN OUT FIELD_DESCRIPTOR_ARRAY
)
IS
L_DATA VARCHAR2 (100);
L_HDR_SIZE NUMBER DEFAULT 32;
L_FIELD_DESC_SIZE NUMBER DEFAULT 32;
L_FLDS FIELD_DESCRIPTOR_ARRAY;
BEGIN
P_FLDS := L_FLDS;
L_DATA :=
UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (P_BFILE,
L_HDR_SIZE,
P_BFILE_OFFSET
)
);
P_BFILE_OFFSET := P_BFILE_OFFSET + L_HDR_SIZE;
P_HDR.VERSION := ASCII (SUBSTR (L_DATA, 1, 1));
P_HDR.YEAR := 1900 + ASCII (SUBSTR (L_DATA, 2, 1));
P_HDR.MONTH := ASCII (SUBSTR (L_DATA, 3, 1));
P_HDR.DAY := ASCII (SUBSTR (L_DATA, 4, 1));
P_HDR.NO_RECORDS := TO_INT (SUBSTR (L_DATA, 5, 4));
P_HDR.HDR_LEN := TO_INT (SUBSTR (L_DATA, 9, 2));
P_HDR.REC_LEN := TO_INT (SUBSTR (L_DATA, 11, 2));
P_HDR.NO_FIELDS :=
TRUNC ((P_HDR.HDR_LEN – L_HDR_SIZE) / L_FIELD_DESC_SIZE);

FOR I IN 1 .. P_HDR.NO_FIELDS
LOOP
L_DATA :=
UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (P_BFILE,
L_FIELD_DESC_SIZE,
P_BFILE_OFFSET
)
);
P_BFILE_OFFSET := P_BFILE_OFFSET + L_FIELD_DESC_SIZE;
/*
P_FLDS(I).NAME := RTRIM(SUBSTR(L_DATA,1,11),CHR(0));
P_FLDS(I).TYPE := SUBSTR( L_DATA, 12, 1 );
P_FLDS(I).LENGTH := ASCII( SUBSTR( L_DATA, 17, 1 ) );
P_FLDS(I).DECIMALS := ASCII(SUBSTR(L_DATA,18,1) );
*/
P_FLDS (I).NAME := MYTRIM (SUBSTR (L_DATA, 1, 11));
P_FLDS (I).TYPE := SUBSTR (L_DATA, 12, 1);
P_FLDS (I).LENGTH := ASCII (SUBSTR (L_DATA, 17, 1));
P_FLDS (I).DECIMALS := ASCII (SUBSTR (L_DATA, 18, 1));
END LOOP;

P_BFILE_OFFSET :=
P_BFILE_OFFSET + MOD (P_HDR.HDR_LEN – L_HDR_SIZE, L_FIELD_DESC_SIZE);
END GET_HEADER;

FUNCTION BUILD_INSERT (
P_TNAME IN VARCHAR2,
P_CNAMES IN VARCHAR2,
P_FLDS IN FIELD_DESCRIPTOR_ARRAY
)
RETURN VARCHAR2
IS
L_INSERT_STATEMENT LONG;
BEGIN
L_INSERT_STATEMENT := ‘insert into ‘ || P_TNAME || ‘(‘;

IF (P_CNAMES IS NOT NULL)
THEN
L_INSERT_STATEMENT := L_INSERT_STATEMENT || P_CNAMES || ‘) values (‘;
ELSE
FOR I IN 1 .. P_FLDS.COUNT
LOOP
IF (I 1)
THEN
L_INSERT_STATEMENT := L_INSERT_STATEMENT || ‘,’;
END IF;

L_INSERT_STATEMENT :=
L_INSERT_STATEMENT || ‘”‘ || P_FLDS (I).NAME || ‘”‘;
END LOOP;

L_INSERT_STATEMENT := L_INSERT_STATEMENT || ‘) values (‘;
END IF;

FOR I IN 1 .. P_FLDS.COUNT
LOOP
IF (I 1)
THEN
L_INSERT_STATEMENT := L_INSERT_STATEMENT || ‘,’;
END IF;

IF (P_FLDS (I).TYPE = ‘D’)
THEN
L_INSERT_STATEMENT :=
L_INSERT_STATEMENT || ‘to_date(:bv’ || I || ‘,”yyyymmdd” )’;
ELSE
L_INSERT_STATEMENT := L_INSERT_STATEMENT || ‘:bv’ || I;
END IF;
END LOOP;

L_INSERT_STATEMENT := L_INSERT_STATEMENT || ‘)’;
RETURN L_INSERT_STATEMENT;
END BUILD_INSERT;

FUNCTION GET_ROW (
P_BFILE IN BFILE,
P_BFILE_OFFSET IN OUT NUMBER,
P_HDR IN DBF_HEADER,
P_FLDS IN FIELD_DESCRIPTOR_ARRAY
)
RETURN ROWARRAY
IS
L_DATA VARCHAR2 (4000);
L_ROW ROWARRAY;
L_N NUMBER DEFAULT 2;
BEGIN
L_DATA :=
UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (P_BFILE,
P_HDR.REC_LEN,
P_BFILE_OFFSET
)
);
P_BFILE_OFFSET := P_BFILE_OFFSET + P_HDR.REC_LEN;
L_ROW (0) := SUBSTR (L_DATA, 1, 1);

FOR I IN 1 .. P_HDR.NO_FIELDS
LOOP
L_ROW (I) := RTRIM (LTRIM (SUBSTR (L_DATA, L_N, P_FLDS (I).LENGTH)));

IF (P_FLDS (I).TYPE = ‘F’ AND L_ROW (I) = ‘.’)
THEN
L_ROW (I) := NULL;
END IF;

L_N := L_N + P_FLDS (I).LENGTH;
END LOOP;

RETURN L_ROW;
END GET_ROW;

PROCEDURE SHOW (
P_HDR IN DBF_HEADER,
P_FLDS IN FIELD_DESCRIPTOR_ARRAY,
P_TNAME IN VARCHAR2,
P_CNAMES IN VARCHAR2,
P_BFILE IN BFILE
)
IS
L_SEP VARCHAR2 (1) DEFAULT ‘,’;

PROCEDURE P (P_STR IN VARCHAR2)
IS
L_STR LONG DEFAULT P_STR;
BEGIN
WHILE (L_STR IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE (SUBSTR (L_STR, 1, 250));
L_STR := SUBSTR (L_STR, 251);
END LOOP;
END;
BEGIN
P (‘/*’);
P (‘Size of FoxPro File: ‘ || DBMS_LOB.GETLENGTH (P_BFILE));
P (‘FoxPro Header Information: ‘);
P (CHR (9) || ‘Version = ‘ || P_HDR.VERSION);
P (CHR (9) || ‘Year = ‘ || P_HDR.YEAR);
P (CHR (9) || ‘Month = ‘ || P_HDR.MONTH);
P (CHR (9) || ‘Day = ‘ || P_HDR.DAY);
P (CHR (9) || ‘#Recs = ‘ || P_HDR.NO_RECORDS);
P (CHR (9) || ‘Hdr Len = ‘ || P_HDR.HDR_LEN);
P (CHR (9) || ‘Rec Len = ‘ || P_HDR.REC_LEN);
P (CHR (9) || ‘#Fields = ‘ || P_HDR.NO_FIELDS);
P (CHR (10) || ‘–Data Fields:’);

FOR I IN 1 .. P_HDR.NO_FIELDS
LOOP
P ( ‘Field(‘
|| I
|| ‘) ‘
|| ‘Name = “‘
|| P_FLDS (I).NAME
|| ‘”, ‘
|| ‘Type = ‘
|| P_FLDS (I).TYPE
|| ‘, ‘
|| ‘Len = ‘
|| P_FLDS (I).LENGTH
|| ‘, ‘
|| ‘Scale= ‘
|| P_FLDS (I).DECIMALS
);
END LOOP;

P (CHR (10) || ‘Insert We would use:’);
P (BUILD_INSERT (P_TNAME, P_CNAMES, P_FLDS));
P (CHR (10) || ‘Table that could be created to hold data:’);
P (‘*/’);
P (‘create table ‘ || P_TNAME);
P (‘(‘);

FOR I IN 1 .. P_HDR.NO_FIELDS
LOOP
IF (I = P_HDR.NO_FIELDS)
THEN
L_SEP := ‘)’;
END IF;

DBMS_OUTPUT.PUT (CHR (9) || ‘”‘ || P_FLDS (I).NAME || ‘” ‘);

IF (P_FLDS (I).TYPE = ‘D’)
THEN
P (‘date’ || L_SEP);
ELSIF (P_FLDS (I).TYPE = ‘F’)
THEN
P (‘float’ || L_SEP);
ELSIF (P_FLDS (I).TYPE = ‘N’)
THEN
IF (P_FLDS (I).DECIMALS > 0)
THEN
P ( ‘number(‘
|| P_FLDS (I).LENGTH
|| ‘,’
|| P_FLDS (I).DECIMALS
|| ‘)’
|| L_SEP
);
ELSE
P (‘number(‘ || P_FLDS (I).LENGTH || ‘)’ || L_SEP);
END IF;
ELSE
P (‘varchar2(‘ || P_FLDS (I).LENGTH || ‘)’ || L_SEP);
END IF;
END LOOP;

P (‘/’);
END SHOW;

PROCEDURE LOAD_TABLE (
P_DIR IN VARCHAR2,
P_FILE IN VARCHAR2,
P_TNAME IN VARCHAR2,
P_CNAMES IN VARCHAR2 DEFAULT NULL,
P_SHOW IN BOOLEAN DEFAULT FALSE
)
IS
L_BFILE BFILE;
L_OFFSET NUMBER DEFAULT 1;
L_HDR DBF_HEADER;
L_FLDS FIELD_DESCRIPTOR_ARRAY;
L_ROW ROWARRAY;
BEGIN
L_BFILE := BFILENAME (P_DIR, P_FILE);
DBMS_LOB.FILEOPEN (L_BFILE);
GET_HEADER (L_BFILE, L_OFFSET, L_HDR, L_FLDS);

IF (P_SHOW)
THEN
SHOW (L_HDR, L_FLDS, P_TNAME, P_CNAMES, L_BFILE);
ELSE
DBMS_SQL.PARSE (G_CURSOR,
BUILD_INSERT (P_TNAME, P_CNAMES, L_FLDS),
DBMS_SQL.NATIVE
);

FOR I IN 1 .. L_HDR.NO_RECORDS
LOOP
L_ROW := GET_ROW (L_BFILE, L_OFFSET, L_HDR, L_FLDS);

IF (L_ROW (0) ‘*’) — DELETED RECORD
THEN
FOR I IN 1 .. L_HDR.NO_FIELDS
LOOP
DBMS_SQL.BIND_VARIABLE (G_CURSOR,
‘:bv’ || I,
L_ROW (I),
4000
);
END LOOP;

IF (DBMS_SQL.EXECUTE (G_CURSOR) 1)
THEN
RAISE_APPLICATION_ERROR (-20001,
‘Insert failed ‘ || SQLERRM
);
END IF;
END IF;
END LOOP;
END IF;

DBMS_LOB.FILECLOSE (L_BFILE);
EXCEPTION
WHEN OTHERS
THEN
IF (DBMS_LOB.ISOPEN (L_BFILE) > 0)
THEN
DBMS_LOB.FILECLOSE (L_BFILE);
END IF;

RAISE;
END LOAD_TABLE;
END DBF2ORA;
/

References :

Advertisements

8 Responses

  1. Hi..
    Thanks for the article.
    I wanna ask u a question.

    My case as follow:
    ===================
    Case:
    I take all *.dbf file from oracle 9i installed in Solaris 9.2 system.

    I want to read the content/data from those .dbf files on my PC (Windows XP).

    Question:
    1. Is it possible to read the data from those .dbf file without installing oracle in my PC?

    2. Is there any tool to do it?

    3. If it is not possible, is there another way that should I do to get the data?

    Any comment, answer, recommendation will greatly appreciated. Thanks a lot.

    Thanks

  2. if you wanna read those .dbf files (dbase/foxpro database format), you can use foxpro or another dbf viewer, without installing oracle.

    This article explain how to import data from .dbf file to oracle table, so user can read and then use the data for data manipulation or something else that user want.

    For example, my PC (windows XP) and my server (windows server 2003), then i login to oracle and run above package to load data from .dbf file into oracle table. So, my PC must login first to oracle server. You can install oracle instant client or oracle xe first,then using sqlplus.

    Thank for the comment, is my answer help you? keep contact me, i wanna learn to from any other who have problem, maybe i can help to search for good solution.

    ^_^

  3. I have been trying to get this package to work for a couple of days and I have a few questions. The procedure ‘SHOW’ doesn’t seem to do anything. Is there something I’m missing. I’m trying to use ‘SHOW’ to define the tables I need to create to hold the DBF data.

  4. You can use DBF2ORA.LOAD_TABLE() to upload data from dbf file to oracle table, for example
    DBF2ORA.LOAD_TABLE(‘DATA_200’, ‘PKMPJJ.DBF’, ‘TMP_DATA_PKMPJJ’, NULL, FALSE);

    — ‘DATA_200’ is oracle directory, create it with this script,
    DROP DIRECTORY DATA_200;

    CREATE OR REPLACE DIRECTORY
    DATA_200 AS
    ‘E:\DATA_DIRECTORY\DATA_200’;

    — ‘PKMPJJ.DBF’ is a dbf file which you want the data in it be uploaded to oracle table

    — ‘TMP_DATA_PKMPJJ’ is a table in oracle schema

    Maybe this answer can help, keep me contact if there is anything i can share. ^_^

    • hi all,
      plz help me..im getting error when running below code..

      BEGIN
      EXPRESS.DBF2ORA.LOAD_TABLE ( ‘DATA_201’, ‘SAL_HEAD.DBF’, ‘dbf_tab_pc’, NULL, false );
      END;

      ——————————————————————
      ORA-22285: non-existent directory or file for FILEOPEN operation
      ORA-06512: at “EXPRESS.DBF2ORA”, line 414
      ORA-06512: at line 2
      ———————————

      kindly help me

      • please, first create oracle directory ‘DATA_201’
        ‘SAL_HEAD.DBF’ must in that directory ^_^

  5. Is this package is meant only for FoxPro ver 2.0 ?
    I am unable to read dbf file generated from higher version of FOXPRO. (>2.0)

    Please update me on this.

  6. i’m using foxpro 2.6

    if you copy the code from this blog maybe, there is some character that can be miss when you paste to your editor,

    so can you try download the code from left widget “wiBOX”, locate DBF2ORA.pkb.sql and DBF2ORA.pks.sql

    ^_^

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: