Upload/import DBASE/FoxPro DBF files into Oracle Database

If you need to import/upload data from DBF (DBASE or Foxpro database files), there is a script that already developed by Tom Kyte.

I have made some modification to fulfill my need, here are the changes:

• Add UPLOAD_TIME column to keep date of upload process from DBF into oracle
• Add support of MEMO fields that has more than 4000 characters

Howto use this script?

First you need to prepare Oracle directory where the dbf files will you stored
You can called it to any name you like. For my example i choose to named it as DIRDBF.

create or replace directory dirdbf as 'e:\data\dbf';
grant read,write on directory dirdbf to <username>;

Show structure table of employee.DBF that located on DIRDBF directory
This will also generate Oracle’s command of “create table ” which constructed from DBF structure format. This table should be exists in Oracle database before import/upload process in next step.

begin
    dbase_fox.load_table( 'DIRDBF','employee.DBF','DT_EMPLOYEE'
        ,p_show => TRUE );
end;
/

Uplad employee.DBF that located on DIRDBF directory into Oracle database
This is simple, you just need to change the p_show parameter to FALSE

begin
    dbase_fox.load_table( 'DIRDBF','employee.DBF','DT_EMPLOYEE'
        ,p_show => FALSE );
end;
/

References:

• Many Thanks for Tom Kyte that has many great discussions on Oracle base. Discussion and original code can be followed at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:711825134415
• Xbase ( & dBASE ) File Format Description by Erik Bachmann

Package Specification

create or replace package dbase_fox
as

    -- procedure to a load a table with records
    -- from a DBASE 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 DBASE 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 DBASE 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
    --        DBASE files (not the data, just the info
    --        from the dbase 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;
/

Package Body

create or replace package body dbase_fox
as

-- Might have to change on your platform!!!
-- Controls the byte order of binary integers read in
-- from the dbase file
BIG_ENDIAN      constant boolean default TRUE;

type dbf_header is RECORD
(
    version    varchar2(25), -- dBASE 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;

-- Dede from Indonesia, to support char > 4000
type rowArrayCLOB
is table of
clob index by binary_integer;

g_process_time  varchar2(10) default to_char(sysdate,'YYYYMMDD');   -- Dede+, Jkt, 20091109_1

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;

-- Alex from Russia add this function
-- to convert a HexDecimal value
-- into a Decimal value
function Hex2Dec( p_data in varchar2 ) return number
is
    l_number number default 0;
    l_bytes  number default length(p_data);
    byte_number number;
    byte_string varchar2 (1);
begin
  if( l_bytes > 0 ) then
    for i in 1 .. l_bytes loop
         byte_string := substr(p_data,l_bytes-i+1,1);
         case byte_string
            when 'A' then byte_number:=10;
            when 'B' then byte_number:=11;
            when 'C' then byte_number:=12;
            when 'D' then byte_number:=13;
            when 'E' then byte_number:=14;
            when 'F' then byte_number:=15;
            else byte_number:=to_number(byte_string);
         end case;
         l_number := l_number + byte_number * power(16,(i-1));
    end loop;
    return l_number;
  else
    return 0;
  end if;
end;

--Mattia from Italy add this function
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;

-- Routine to parse the DBASE header record, can get
-- all of the details of the contents of a dbase file from
-- this header

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 := 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 );
    dbms_output.put_line('get_header passed.');
end;

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 ||',"UPLOAD_TIME"';    -- Dede+, Jkt, 20091109_1
        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;
    -- Dede+, Jkt, 20091109_1, START
    l_insert_statement := l_insert_statement ||
             ',to_date(:bv' || (p_flds.count+1) || ',''yyyymmdd'' )';
    -- Dede+, Jkt, 20091109_1, END
    l_insert_statement := l_insert_statement || ')';

    return l_insert_statement;
end;

function get_row
( p_bfile in bfile,
  p_bfile_offset in out number,
  p_hdr in dbf_header,
  p_flds in field_descriptor_array,
  f_bfile in bfile,
  -- memo_block in number ) return rowArray     -- Dede-, Jkt, 20090828
  memo_block in number ) return rowArrayCLOB    -- Dede+, Jkt, 20090828
is
    --l_data  varchar2(4000);   -- Dede-, Jkt, 20090828
    l_data  CLOB;               -- Dede+, Jkt, 20090828
    l_row   rowArrayCLOB;       -- Dede+, Jkt, 20090828
    l_n     number default 2;
    f_block number;
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;
-------------------working with Memo fields
        elsif ( p_flds(i).type = 'M' ) then
           --Check is file exists
           if( dbms_lob.isopen( f_bfile ) != 0) then
              --f_block - memo block length
              f_block  := Hex2Dec(dbms_lob.substr( f_bfile, 4, to_number(l_row(i))*memo_block+5 ));
              --to_number(l_row(i))*memo_block+9 - offset in memo file *.fpt, where l_row(i) - number of
              --memo block in fpt file
              l_row(i) := utl_raw.cast_to_varchar2(dbms_lob.substr( f_bfile, f_block, to_number(l_row(i))*memo_block+9));
              --l_row(i) := substr(utl_raw.cast_to_varchar2(dbms_lob.substr( f_bfile, f_block, to_number(l_row(i))*memo_block+9)),1,4000);
           else
              l_row(i) := NULL;
              /*
              dbms_output.put_line('Not found .fpt file');
              exit;
              */
           end if;
-------------------------------------------
        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 ',';
    l_length_num_adj  number default 3; -- Dede+, Jkt, 20090828, to handle "ORA-01438: value larger than specified precision allowed for this column" error

    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( 'Sizeof DBASE File: ' || dbms_lob.getlength(p_bfile) );
    p( 'DBASE 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( '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;    -- Dede-, Jkt, 20091109_1
        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||','||                -- Dede-, Jkt, 20090828
                p( 'number('||(TO_NUMBER(p_flds(i).length) + l_length_num_adj)||','||   -- Dede+, Jkt, 20090828
                              p_flds(i).decimals || ')' ||
                              l_sep );
            else
                p( 'number('||p_flds(i).length||')'||l_sep );
            end if;
        elsif ( p_flds(i).type = 'M' ) then
            p( 'clob' || l_sep);
        else
            p( 'varchar2(' || p_flds(i).length || ')'||l_sep);
        end if;
    end loop;
    p( chr(9) || '"UPLOAD_TIME"   date '||')');     -- Dede+, Jkt, 20091109_1
    p( '/' );
end;

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;
    f_bfile    bfile;
    l_offset   number default 1;
    l_hdr      dbf_header;
    l_flds     field_descriptor_array;
    -- l_row      rowArray;     -- Dede-, Jkt, 20090828
    l_row      rowArrayCLOB;    -- Dede+, Jkt, 20090828
    f_file     varchar2(25);
    memo_block   number;
    n_dummy     number;
begin
    f_file := substr(p_file,1,length(p_file)-4) || '.fpt';
    l_bfile := bfilename( p_dir, p_file );
    dbms_lob.fileopen( l_bfile );

-----------------------  Alex from Russia add this
    f_bfile := bfilename( p_dir, f_file );
    if( dbms_lob.fileexists(f_bfile) != 0 ) then
      dbms_output.put_line(f_file || ' - Open memo file');
      dbms_lob.fileopen( f_bfile );
    end if;
--------------------------------------------------
    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_output.put_line('before parse(): '||p_tname||'/'||p_cnames);   -- Dede+
        dbms_sql.parse( g_cursor,
                        build_insert( p_tname, p_cnames, l_flds ),
                        dbms_sql.native );
        dbms_output.put_line('after parse()');
--      Memo block size in ftp file
        if ( dbms_lob.isopen( f_bfile ) > 0 ) then
            memo_block := Hex2Dec(dbms_lob.substr(f_bfile, 2, 7));
        else
            memo_block := 0;
        end if;

        dbms_output.put_line( build_insert( p_tname, p_cnames, l_flds ) );
        for i in 1 .. l_hdr.no_records loop
            --dbms_output.put('fetch row#: '||i);
            l_row := get_row( l_bfile,
                              l_offset,
                              l_hdr,
                              l_flds, f_bfile, memo_block );
            --dbms_output.put_line('..done');
            if ( l_row(0) <> '*' ) -- deleted record
            then
                for i in 1..l_hdr.no_fields loop
                    n_dummy := i;
                    /*  -- Dede-, Jkt, 20090828
                    dbms_sql.bind_variable( g_cursor,
                                            ':bv'||i,
                                            l_row(i),
                                            4000 );
                    */
                    -- Dede+, Jkt, 20090828, START
                    if ( length( l_row(i) ) <= 4000 ) then
                        dbms_sql.bind_variable( g_cursor, ':bv'||i, l_row(i), 4000 );
                    else
                        dbms_sql.bind_variable( g_cursor, ':bv'||i, l_row(i) );
                    end if;
                    -- Dede+, Jkt, 20090828, END

                end loop;

                dbms_sql.bind_variable( g_cursor, ':bv'||(l_hdr.no_fields+1), g_process_time ); -- Dede+, Jkt, 20091109_1

                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 );
    if ( dbms_lob.isopen( f_bfile ) > 0 ) then
      dbms_lob.fileclose( f_bfile );
    end if;
exception
    when others then
        dbms_output.put_line('something error!!!');
        dbms_output.put_line(l_row(1)||' '||l_row(2));
        dbms_output.put_line(l_row(32));
        dbms_output.put_line(l_row(n_dummy));
        if ( dbms_lob.isopen( l_bfile ) > 0 ) then
            dbms_lob.fileclose( l_bfile );
        end if;
        if ( dbms_lob.isopen( f_bfile ) > 0 ) then
            dbms_lob.fileclose( f_bfile );
        end if;
        RAISE;
end;
end dbase_fox;
/