Skip to Main Content
  • Questions
  • reading data from .dbf file into oracle 8i -- HOW TO LOAD DBASE data

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mamta .

Asked: September 07, 2000 - 9:33 am UTC

Answered by: Tom Kyte - Last updated: March 09, 2020 - 10:56 am UTC

Category: Database - Version: oracle 8i

Viewed 10K+ times! This question is

You Asked

I am unable to read the file which in .dbf format . if I open the file in excel and save it as .csv file , I can read the file
using utl_file package of oracle 8i.

I cannot do this activity manually as I would be ftp ..ing the files after every 10 minutes , hence the process needs to be automated .

how can do this . I have searched through many sites , asked many oracle professional but I have not got any answer for this .

I need the help on this immediately .

Regards
mamta

and we said...

Well, I looked up the dbase file format on the web and found some sample DBASE files (specifically at
</code> http://www.e-bachmann.dk/docs/xbase.htm
).  I put together a "dbase_pkg" plsql package that in Oracle8.0 and up should be able to load many DBASE files.  It requires Oracle8.0 because I use a bfile to do binary file io which is required with dbase files.

The package I developed will load into an existing table a DBASE file.  Optionally -- it will show you the insert it will use an print out a create table you might use to load into.  It is not heavily "field tested" as yet -- should work in most cases, however I'm pretty much sure MEMO fields won't work since they are stored out of line in another file.

Here is the specification of this package:

create or replace package dbase_pkg
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;
/

and the body is:

create or replace package body dbase_pkg
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;


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;


-- 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 := 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) );
    end loop;

    p_bfile_offset := p_bfile_offset +
                          mod( p_hdr.hdr_len - l_hdr_size,
                               l_field_desc_size );
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 ||
                                           ') 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;


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( '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;
        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;


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;

end;
/


Now, if you get the "sample" dbase files from the web site I referred to above, you can now run the following test to see this work.  You would need to change the PATH of the create directory below and make sure to put t1.dbf and so on in that directory (from their template/t1/with_data/t1.dbf directory and so on)


ops$tkyte@DEV816> set serveroutput on size 1000000
ops$tkyte@DEV816> 
ops$tkyte@DEV816> create or replace directory
  2  my_files as '/export/home/tkyte/dbf';

Directory created.

ops$tkyte@DEV816> 
ops$tkyte@DEV816> column cfield format a15 word_wrapped
ops$tkyte@DEV816> column memo format a5
ops$tkyte@DEV816> set numformat 99999.999999999999999
ops$tkyte@DEV816> alter session set nls_date_format = 'dd-mon-yyyy';

Session altered.

ops$tkyte@DEV816> 
ops$tkyte@DEV816> 
ops$tkyte@DEV816> begin
  2  dbase_pkg.load_table( 'MY_FILES',
  3                        't1.dbf',
  4                        't1',
  5                        p_show => TRUE );
  6  end;
  7  /
Sizeof DBASE File: 1581
DBASE Header Information:
        Version = 3
        Year    = 1900
        Month   = 6
        Day     = 12
        #Recs   = 11
        Hdr Len = 161
        Rec Len = 129
        #Fields = 4

Data Fields:
Field(1) Name = "CHARACTER", Type = C, Len  = 100, Scale= 0
Field(2) Name = "NUMERIC", Type = N, Len  = 19, Scale= 15
Field(3) Name = "DATE", Type = D, Len  = 8, Scale= 0
Field(4) Name = "LOGICAL", Type = L, Len  = 1, Scale= 0

Insert We would use:
insert into t1("CHARACTER","NUMERIC","DATE","LOGICAL") values
(:bv1,:bv2,to_date(:bv3,'yyyymmdd' ),:bv4)

Table that could be created to hold data:
create table t1
(
        "CHARACTER"   varchar2(100),
        "NUMERIC"   number(19,15),
        "DATE"   date,
        "LOGICAL"   varchar2(1))
/

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> drop table t1;

Table dropped.

ops$tkyte@DEV816> create table t1
  2  ( cfield varchar2(255),
  3    nfield number,
  4    dfield date,
  5    logical char(1)
  6  );

Table created.

ops$tkyte@DEV816> begin
  2  dbase_pkg.load_table( 'MY_FILES',
  3                        't1.dbf',
  4                        't1',
  5  'cfield, nfield, dfield, logical' );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> commit;

Commit complete.

ops$tkyte@DEV816> select * from t1;

CFIELD                          NFIELD DFIELD      L
--------------- ---------------------- ----------- -
One                  1.000000000000000 01-jan-1970 Y
Two records          2.000000000000002 31-dec-1970 Y
Three records        3.000000000000000 01-jan-1980 N
stored

Four records          .000000000000004 31-dec-1980 N
stored now

Five records       555.555555555555543 01-jan-1900
stored now in

Six records          6.000000000000000
stored now in
the

Seven records        7.000000000000000
stored now in
the database

Eight records        8.000000000000000
stored now in
the database
for

Nine records          .099999999999999
stored now in
the database
for test

Ten  records        10.000000000000000
stored now in
the database
for test
purposes



11 rows selected.

ops$tkyte@DEV816> 
ops$tkyte@DEV816> begin
  2  dbase_pkg.load_Table( 'MY_FILES',
  3                  't2.dbf',
  4                  't2',
  5                  p_show=>TRUE );
  6  end;
  7  /
Sizeof DBASE File: 1723
DBASE Header Information:
        Version = 131
        Year    = 1900
        Month   = 6
        Day     = 12
        #Recs   = 11
        Hdr Len = 193
        Rec Len = 139
        #Fields = 5

Data Fields:
Field(1) Name = "CHARACTER", Type = C, Len  = 100, Scale= 0
Field(2) Name = "NUMERIC", Type = N, Len  = 19, Scale= 15
Field(3) Name = "DATE", Type = D, Len  = 8, Scale= 0
Field(4) Name = "LOGICAL", Type = L, Len  = 1, Scale= 0
Field(5) Name = "MEMO", Type = M, Len  = 10, Scale= 0

Insert We would use:
insert into t2("CHARACTER","NUMERIC","DATE","LOGICAL","MEMO")
values (:bv1,:bv2,to_date(:bv3,'yyyymmdd' ),:bv4,:bv5)

Table that could be created to hold data:
create table t2
(
        "CHARACTER"   varchar2(100),
        "NUMERIC"   number(19,15),
        "DATE"   date,
        "LOGICAL"   varchar2(1),
        "MEMO"   varchar2(10))
/

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> drop table t2;

Table dropped.

ops$tkyte@DEV816> create table t2
  2  ( cfield varchar2(255),
  3    nfield number,
  4    dfield date,
  5    logical char(1),
  6    memo varchar2(255)
  7  );

Table created.

ops$tkyte@DEV816> begin
  2  dbase_pkg.load_Table( 'MY_FILES',
  3                  't2.dbf',
  4                  't2',
  5  'cfield, nfield, dfield, logical, memo' );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> select * from t2;

CFIELD                          NFIELD DFIELD      L MEMO
--------------- ---------------------- ----------- - -----
One                  1.000000000000000 01-jan-1970 T 1
Two records          2.000000000000002 31-dec-1970 T 2
Three records        3.000000000000000 01-jan-1980 F 3
stored

Four records          .000000000000004 31-dec-1980 F 4
stored now

Five records       555.555555555555543 01-jan-1900 F 5
stored now in

Six records          6.000000000000000             F 6
stored now in
the

Seven records        7.000000000000000             F 7
stored now in
the database

Eight records        8.000000000000000             F 8
stored now in
the database
for

Nine records          .099999999999999             F 9
stored now in
the database
for test

Ten  records        10.000000000000000             F 10
stored now in
the database
for test
purposes

                      .000000000000000             F

11 rows selected.

ops$tkyte@DEV816> 
ops$tkyte@DEV816> begin
  2  dbase_pkg.load_Table( 'MY_FILES',
  3                  't4.dbf',
  4                  't4',
  5                  p_show => true );
  6  end;
  7  /
Sizeof DBASE File: 1694
DBASE Header Information:
        Version = 3
        Year    = 2000
        Month   = 6
        Day     = 12
        #Recs   = 10
        Hdr Len = 193
        Rec Len = 150
        #Fields = 5

Data Fields:
Field(1) Name = "CHARACTER", Type = C, Len  = 100, Scale= 0
Field(2) Name = "NUMERICAL", Type = N, Len  = 20, Scale= 2
Field(3) Name = "DATE", Type = D, Len  = 8, Scale= 0
Field(4) Name = "LOGICAL", Type = L, Len  = 1, Scale= 0
Field(5) Name = "FLOAT", Type = F, Len  = 20, Scale= 18

Insert We would use:
insert into t4("CHARACTER","NUMERICAL","DATE","LOGICAL","FLOAT")
values (:bv1,:bv2,to_date(:bv3,'yyyymmdd' ),:bv4,:bv5)

Table that could be created to hold data:
create table t4
(
        "CHARACTER"   varchar2(100),
        "NUMERICAL"   number(20,2),
        "DATE"   date,
        "LOGICAL"   varchar2(1),
        "FLOAT"   float)
/

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> drop table t4;

Table dropped.

ops$tkyte@DEV816> create table t4
  2  ( cfield varchar2(255),
  3    nfield number,
  4    dfield date,
  5    logical char(1),
  6    float_field number
  7  );

Table created.

ops$tkyte@DEV816> begin
  2  dbase_pkg.load_Table( 'MY_FILES',
  3                  't4.dbf',
  4                  't4',
  5      'cfield, nfield, dfield, logical, float_field' );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> set numformat 999999
ops$tkyte@DEV816> column cfield format a6
ops$tkyte@DEV816> select * from t4;

CFIELD  NFIELD DFIELD      L FLOAT_FIELD
------ ------- ----------- - -----------
One          1 01-jan-1970 Y           1
Two          2 31-dec-1970 T           2
Three        3 01-jan-1980             3
Four         4 01-jan-1900             4
Five         5 31-dec-1900             5
Six          6 01-jan-1901             6
Seven        7 31-dec-1999             7
Eight        8 31-dec-1919             8
Nine         9
Ten         10                         0

10 rows selected.

ops$tkyte@DEV816> 



If anyone uses this and encounters a "bug", drop me a line and let me know -- I'll fix it accordingly so others don't hit it.


UTL_RAW might not be installed on your system -- it comes with 
Oracle7.1.6 and up.  To install if you do not have it:

o cd $ORACLE_HOME/rdbms/admin
o connect as SYS or INTERNAL only using SVRMGRL
o @utlraw
o @prvtrawb.plb

 
 
>

and you rated our response

  (87 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

reading data from dbf

June 18, 2001 - 2:03 am UTC

Reviewer: balu from india

hai tom
thanks for answer but i am facing problem
i am using o8i client and win 98 environment.i have created one table to hold bfile details with a directory in oracle pointing to it.well i have created a table in oracle to insert data from dbf to that.but it is reading data but not inserting into t1 table in oracle need u r help
urgent
thanks in advance
balu

It doesn't work with foxpro files ...

November 06, 2001 - 9:23 am UTC

Reviewer: Mattia Rossi from Italy

because the rtrim that returns the name of the column in the dbf file fails ( get_header function) ...
here is the new get_header function:


-- 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 := 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;


and this is the mytrim 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;

The package modified in this way works for me ...

Mattia

reading data from .dbf file into oracle 8i

November 17, 2001 - 6:17 am UTC

Reviewer: Deepali Aggarwal

Worked great. There are many ways to convert a .dbf file into oracle table manually but through this package, was able to automate the whole process. Being a novice PL*SQL programmer had to search few more links for 'unable to open file or directory to read'. But finally it worked.

Thanks


dbase_pkg in oracle9i

August 19, 2002 - 10:06 am UTC

Reviewer: mick barlow from Lancshire, England

Hi Tom,

I know this is a about a year late but, i am try to use this package in Oracle9i(win2k) but whem i create it using plus80w it says "Warning: Package created with compilation errors."

Any ideas's?

Thanx...

P.S. I am new to this Oracle stuff, so please be gentle.


Tom Kyte

Followup  

August 19, 2002 - 11:17 am UTC

must be a cut and paste error on your part.  I just ran it without issue.

why are you using plus80w, just use the sqlplus.exe that comes with 9i.

after you run the package body, do a 

SQL> show errors package body dbase_pkg

and see what is wrong (and then fix it).  The code "as is" compiled just fine. 

Need a bit more help

August 30, 2002 - 6:25 am UTC

Reviewer: Shyampaliyath from Tamil Nadu,India.

I was looking for a solution to read from dbf files into oracle when i came across this question.Hence i tried out the solution provided by u. But i came across a problem which i kindly request u to take into consideration.

create or replace directory my_files as '/home/applications';

directory created.

create or replace procedure dbf_file_open
as
file_name bfile;
begin
file_name:=bfilename('my_files','Bankfile.dbf');
dbms_lob.open(file_name);
dbms_output.put_line('Successful');
end;

ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "DEPOSITS.DBF_FILE_OPEN", line 280
ORA-06512: at line 2

how to proceed further.

(ii) This is another requirement
i get the path from the get_file_name function and

i execute the procedure as


create or replace procedure dbf_file_open
as
file_name bfile;
begin
file_name:=bfilename('c:\dbf','Bankfile.dbf');
dbms_lob.open(file_name);
dbms_output.put_line('Successful');
end;

i get the error as
==================
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "DEPOSITS.DBASE_PKG", line 280
ORA-06512: at line 2

what shall i do??

I f u could plz guide me i'd be much happy.

Shyam

Tom Kyte

Followup  

August 30, 2002 - 10:20 am UTC

i) file_name:=bfilename('MY_FILES','Bankfile.dbf');

it is case sensitive.

ii) You cannot use OS dependent paths like that to bfilename. You can only use directory objects.

dbase data into oracle

August 30, 2002 - 1:15 pm UTC

Reviewer: k ullur

It might be easier using odbc in a language like perl for dbase to oracle xfr...?

I used this method for data xfr between Access and Oracle.

odbc drivers for dbase and oracle are available and hence must be practical...

Tom Kyte

Followup  

August 30, 2002 - 4:21 pm UTC

Lets see --

I could write a custom perl program, dig up ODBC drivers (perl is generally associated with Unix, Unix is not generally associated with ODBC) -- blah blah blah

Or, I could use this utility and write nothing, be dependent on no other piece of software, have it work on any platform --- hmm.

I don't know -- which is easier?

Feedback

September 02, 2002 - 12:58 am UTC

Reviewer: Shyampaliyath from Tamil Nadu,India.

hello sir,

Thanxs for ur response to my question. I tried out the solution provided by u (i.e.) abt the case sensitivity 
and i rewritten my procedure as 
SQL> CREATE OR REPLACE procedure dbf_file_open
  2  as
  3  file_name bfile;
  4  begin
  5     file_name:=bfilename('DBF_PTR','Bankfile.dbf');
  6     DBMS_LOb.FILEOPEN(FILE_name);
  7  end;
  8  /

Procedure created.

SQL> EXEC DBF_FILE_OPEN;
BEGIN DBF_FILE_OPEN; END;

*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation 
ORA-06512: at "SYS.DBMS_LOB", line 475 
ORA-06512: at "KCP.DBF_FILE_OPEN", line 6 
ORA-06512: at line 1 

since my database is in linux i went to init.ora file and added another line as 

utl_file_dir=kcp_ro_linux/home/applications

and restarted my oracle database and executed my procedure
but the same error occurs.

waiting for ur response. 

Tom Kyte

Followup  

September 02, 2002 - 10:14 am UTC

UTL_FILE_DIR has nothing to do with bfiles -- nothing.


You do understand that the FILE itself must be on the SERVER (not your pc). I see in your example (original example)

...
create or replace directory my_files as '/home/applications';

directory created.
....
create or replace procedure dbf_file_open
as
file_name bfile;
begin
file_name:=bfilename('c:\dbf','Bankfile.dbf');
dbms_lob.open(file_name);
dbms_output.put_line('Successful');
end;
.....


So, I am thinking that since you are mixing Unix and DOS naming conventions -- you have the file on your PC and the SERVER is where it needs to be.



dbf to oracle

September 05, 2002 - 3:57 am UTC

Reviewer: Shyampaliyath from Tamil Nadu,India

atlast it worked. Thank u for u r help

Vice Versa

September 11, 2002 - 4:29 am UTC

Reviewer: Mick Barlow from Lancashire, England

Superb, works brilliant. BUT just one more thing, is possible to make this work the other way and export from Oracle to DBASE now that the data has been queried and amended.

Cheers

Tom Kyte

Followup  

September 11, 2002 - 7:47 am UTC

Well, I suppose you could write the reverse piece of code I have here however.... until 9ir2 you'll have to do it in a Java stored procedure (at least the IO part) or C since UTL_FILE does not support BINARY file io until then.

An alternative approach

December 05, 2002 - 2:05 pm UTC

Reviewer: andrew from ca, usa

Converting dbf to txt and txt to dbf outside the database is attractive is if saves coding. See this URL for txt2dbf and dbf2txt:
</code> http://www.usf.uos.de/~fkoorman/software/dbftools.en.html <code>



Thanks, but what is the best way to import DBF

December 23, 2002 - 8:37 am UTC

Reviewer: Edgar Chupit from Riga, Latvia

Hello Tom,

Thanks for a great piece of code, we learn a lot from this site. Can you please give me suggestion about my problem. My problem is that I need to migrate data from DBF files to Oracle, but not only once, for now, I need to do synchronize data from DBF to Oracle each month. I saw a lot of methods of loading DBF files to Oracle such as:
a) Use your code to load DBF directly from PL/SQL;
b) As I saw on internet there was an application to create SQLLDR script to load DBF files;
c) There are some articles and FoxPro scripts to migrate from FoxPro to Oracle7 at otn.oracle.com;
d) Write my own application that will put data from DBF files to Oracle database;
e) Write my own application that will generate a SQLLDR param file (DBF is just fixed record file);
f) Use third party app that will do what I need.

Can you please give me advice on what is better way to transfer data from DBF to Oracle. Thank you.


Tom Kyte

Followup  

December 23, 2002 - 8:58 am UTC

a) works
b) doesn't work
c) and on this site, you could use odbc easily and sqlplus copy commands
d) sure
e) no it is not. it contains binary data and a very binary header
f) sure


I would use either A or C as they work and involve no code. See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206 <code>
for help on C)

Output

March 06, 2003 - 9:59 am UTC

Reviewer: Steve from UK

Using 9iR2 I don't suppose you could give a very quick example of reversing your code and outputting in dbf format could you?

Thanks

Tom Kyte

Followup  

March 06, 2003 - 3:29 pm UTC

select reverse( text )
from user_source
where name = 'DBASE_PKG'
order by type, line desc;

should reverse the code from top to bottom ;)

seriously -- you should be able to reverse it based on this sort of code -- and most things that read "dbf" read CSV and for that you search for dump_csv

Thanks

March 06, 2003 - 5:17 pm UTC

Reviewer: Steve from UK

I know - I was just being lazy!! Sorry!
Almost got it cracked now anyway - just a question of reading the spec., using a decent text editor and some thought and perserverence!!

An Excellent and Flexible Package..

August 01, 2003 - 4:21 am UTC

Reviewer: Vikas Gupta from India

Thanks Tom, for the excellent package. This will solve many of my problems.

However I 'am confused at one place.

When ever I keep the file in the local drive of the server I am being able to run the package without problems.

But when the file is on a mapped drive, I get error of file not found.

What am I doing wrong here ?

Vikas.

Tom Kyte

Followup  

August 01, 2003 - 7:59 am UTC

windoze makes this really hard

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:241814624807 <code>

reading data from .dbf file into oracle 8i -- HOW TO LOAD DBASE data", version oracle 8i

September 21, 2003 - 1:42 am UTC

Reviewer: Dulal Sarkar from Bangladesh

Hi Tom,

I have go through this topics and its an excellent help for me. Thanks a lot.

I have to read Paradox5.0 file into Oracle8i frequently as automated system and so if you kindly give me a solution like dbase_pkg of you. I will be highly glad. I am using Oracle8i, Developer 6i and Windows2000.
I need the help on this immediately.

Thanks in advance.



Tom Kyte

Followup  

September 21, 2003 - 9:43 am UTC

well, i know as much about the file format of a paradox file as you do -- probably even less (i don't run any OS's that could even run paradox)

So, you see the method I used -- you could code the same...

or, you can use odbc:
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4406709207206 <code>

and just insert into select * from

November 19, 2003 - 12:49 am UTC

Reviewer: padmaja

the info is quite useful . but how to load memo fields into Oracle Database (8i) .

HOW TO LOAD DBASE data

December 06, 2003 - 10:36 am UTC

Reviewer: Vijay from Bahrain

Given info is very useful. but what about memo field in the dbf file. Please advise.

Thanks in advance


Tom Kyte

Followup  

December 06, 2003 - 10:52 am UTC

well, you see *how* i did what I did.

all you need to do now is dig up "how are memo fields stored in dbase files" (should be easy to find on google somewhere) and implement it.

and then you can post it for others

Not difficult

December 09, 2003 - 10:07 am UTC

Reviewer: Steve from UK

Memo fields are held in a separate file to the .dbf file - usually .dbt file. Memo's are held in 512k blocks and a pointer is held in a field in the .dbf file - so a value of 6 would indicate that the memo is in the 6th 512k block of the memo (dbt) file.

What I do is dbms_lob.fileopen the .dbt file the dbms_lob.substr it for the block I want.

How To Load DBASE Data

January 06, 2004 - 2:01 pm UTC

Reviewer: A reader from British Columbia Canada

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:711825134415 <code>

Thank you very much for this information, it was immensely helpful. Great site.

Corrected function because of different NLS_LANG parameter!

January 07, 2004 - 4:26 am UTC

Reviewer: Roman Frelih from Slovenia

Because of different NLS_LANG parameters, an error occured with message: INVALID NUMBER.
Because the type rowArray is consisted of strings, we have to change to real (system) decimal character.
Some could use , and someone could use .


This is my replaced function, which I use know!
It uses nested exception, which may be not so good.


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;
l_NumberRow NUMBER; -- by Roman
l_StringRow VARCHAR2(255); -- by Roman
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 ) ));
-- by Roman, jan.2004, Slovenia
-- Because of different NLS_LANG parameters, an error occured with message: INVALID NUMBER.
-- Because the type rowArray is consisted of strings, we have to change to real (system) decimal character.
-- Some could use , and someone could use .
-- So, what I do here:
-- I convert the string value of number to number value and that back to string value.
IF ( p_flds(i).TYPE = 'F' OR p_flds(i).TYPE = 'N' ) THEN -- Only for Float and Number type
BEGIN
l_StringRow := l_row(i); --Save to temporary variable
l_StringRow := REPLACE ( l_StringRow , ',' , '.' ); --We change characters
l_NumberRow := TO_NUMBER ( l_StringRow ); -- try to convert to number
l_row(i) := TO_CHAR( l_NumberRow ); --If succeded that we have right number value!
EXCEPTION
WHEN OTHERS THEN
l_StringRow := l_row(i); --Save to temporary variable
l_StringRow := REPLACE ( l_StringRow , '.' , ',' ); --We change characters
l_NumberRow := TO_NUMBER ( l_StringRow ); -- try to convert to number
l_row(i) := TO_CHAR( l_NumberRow ); --If succeded that we have right number value!
END;
END IF;
-- End Roman

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;

Possible with Selective Loading?

January 28, 2004 - 10:00 pm UTC

Reviewer: W. Leong from Singapore

First of all, thanx for the posting. It prove most useful for my existing project where we need to convert dbf (yes, people do use it still) to oracle.

Question: Is the package able to perform selective load?
Example:
begin
dbase_pkg.load_table( 'MY_FILES',
't1.dbf',
't1',
'cfield, logical' );
end;
/
(I have tried but it failed with 'too many values' error)


Reason I am asking is because we need to perform a daily migration of dbf to oracle. Some of the tables are fairly large (150MB) and it takes easily 30 min just to perform such migration using this package. Given 50% of the data are not relevant, it will be most helpful if we are able to load only the selective field and thus speed up the performance.

Cheers
-WK-


Tom Kyte

Followup  

January 29, 2004 - 7:54 am UTC

you have the code, you have the logic, you can make it do whatever you need!

It works except column format

February 24, 2004 - 10:36 am UTC

Reviewer: Henk Koekkoek from The Netherlands

On one dbf file it works perfectly, on another there is no column format specified for all but one column. What could be the cause of that (see output below)

Henk
A.S. Watson
------------------------------------------
Sizeof DBASE File: 840127582
DBASE Header Information:
Version = 3
Year = 2004
Month = 2
Day = 24
#Recs = 5154153
Hdr Len = 642
Rec Len = 163
#Fields = 19

Data Fields:
Field(1) Name = "ARTIKELID
Field(2) Name = "FILIAALID
Field(3) Name = "AANTAL
Field(4) Name = "GELEVERD
Field(5) Name = "BTWPERC
Field(6) Name = "DATUM
Field(7) Name = "TIJD
Field(8) Name = "PREVRECNO
Field(9) Name = "NEXTRECNO
Field(10) Name = "VERVANGT
Field(11) Name = "BRONINKPR
Field(12) Name = "BRONVRKPR
Field(13) Name = "USERINKPR
Field(14) Name = "USERVRKPR
Field(15) Name = "HISTORIEID", Type = C, Len = 9, Scale= 0
Field(16) Name = "ORDERID
Field(17) Name = "VRDOUD
Field(18) Name = "GIPOUD
Field(19) Name = "GIP

Insert We would use:
insert into
inkhis_tmp("ARTIKELID
,"DATUM
R
UD
(:bv1,:bv2,:bv3,:bv4,:bv5,to_date(:bv6,'yyyymmdd'
),:bv7,:bv8,:bv9,:bv10,:bv11,:bv12,:bv13,:bv14,:bv15,:bv16,:bv17,:bv18,:bv19)

Table that could be created to hold data:
create table inkhis_tmp
(
"ARTIKELID
"FILIAALID
"AANTAL
"GELEVERD
"BTWPERC
"DATUM
"TIJD
"PREVRECNO
"NEXTRECNO
"VERVANGT
"BRONINKPR
"BRONVRKPR
"USERINKPR
"USERVRKPR
"HISTORIEID" varchar2(9),
"ORDERID
"VRDOUD
"GIPOUD
"GIP
/

PL/SQL-procedure is geslaagd.

Tom Kyte

Followup  

February 24, 2004 - 12:55 pm UTC

stick some debug in there and see what you see. the code is all there and fairly straight forward.

without having your exact dbf file, i cannot really say. I just just the documented format I saw and implemented code that could read it.

Excellent package!

March 08, 2004 - 1:23 pm UTC

Reviewer: Robert Massey from Atlanta, GA USA

Tom,

I have a need to import a dBase table, and I decided to give your site a search (as I so often do). Your dbase_pkg is perfect! Many thanks!

Robert


EXCELENT!!

April 23, 2004 - 11:46 am UTC

Reviewer: Reynaldo Vial from Argentina

This package is perfect to mirate data from dbase to oracle.

Great Package

June 23, 2004 - 3:06 am UTC

Reviewer: WK from Singapore

I have managed to get this package running previously in client's environment. However, as I tried to introduce this in a new environment, it didn't insert any data (but the table header is displayed correctly). I traced it to where the get_row where data is always empty.

I have inserted a debug print statement in function get_row after the utl_raw.cast_to_varchar2()


l_data := utl_raw.cast_to_varchar2(
dbms_lob.substr( p_bfile,
p_hdr.rec_len,
p_bfile_offset ) );

dbms_output.put_line('result ' || l_data);


The result is always blank. The only difference is that it works in oracle 9.2.0.3.0 but not 9.2.0.1.0. Anyone encounter similar problem?


Problem with national character sets

July 08, 2004 - 9:27 am UTC

Reviewer: Antony Pyatkov from Russia

I tried to use your package to load DBase file with national character set RU8PC866 which is different from database charset ( CL8ISO8859P5). Of course, I saw garbage instead of chars in the fields. How can I modify the package to convert one character set to another while uploading?

Tom Kyte

Followup  

July 08, 2004 - 9:41 am UTC

you might be able to use the convert() builtin function -- documented in the sql reference.

Thanks!

July 09, 2004 - 1:16 am UTC

Reviewer: Antony Pyatkov from Russia

It's working! Excuse me please for such simple question.

data from .dbf file into oracle 8i

October 12, 2004 - 9:10 am UTC

Reviewer: Michael diPilla from Philadelphia, Pa

The Procedure looks solid.

However, I continue to get ora-22285.

I have noticed many people have upper and lower case issues.
but I tried both with no success.

Any Ideas?

Tom Kyte

Followup  

October 12, 2004 - 9:20 am UTC

[tkyte@xtkyte-pc tkyte]$ oerr ora 22285
22285, 00000, "non-existent directory or file for %s operation"
// *Cause: Attempted to access a directory that does not exist, or attempted
// to access a file in a directory that does not exist.
// *Action: Ensure that a system object corresponding to the specified
// directory exists in the database dictionary, or
// make sure the name is correct.


so.... questions for you


a) is the directory you are attempting to access ON THE DATABASE server (it must be)

b) is the directory you are attempting to access a "windows share" (tons of extra special 'cause its windows' setup must be done)

c) is the directory you are attempting to access *readable by the oracle software owner*

d) ask the same questions of the file itself.




On the database server ITSELF, do this or something similar to just test it out:


tkyte@ORA9IR2W> @z:\home\tkyte\test
tkyte@ORA9IR2W>
tkyte@ORA9IR2W> create or replace directory mydir as 'c:\test'
2 /

Directory created.

tkyte@ORA9IR2W>
tkyte@ORA9IR2W> host echo hello world > c:\test\foo.dat

tkyte@ORA9IR2W>
tkyte@ORA9IR2W> declare
2 l_bfile bfile;
3 begin
4 l_bfile := bfilename( 'MYDIR', 'foo.dat' );
5 dbms_lob.fileopen( l_bfile );
6 dbms_lob.fileclose( l_bfile );
7 end;
8 /

PL/SQL procedure successfully completed.




how the datetime type (binary) is stored in dbf files

October 22, 2004 - 4:01 am UTC

Reviewer: Hvezdosvit from Czech rep.

I dont know, if I dont bring something well known, but if somebody wants to know...(Your script is really super, but doesnt work conversion of datetime type (it remains still in binary format). I was looking for format specification of dbf datetime type but nowhere found it. So I have have a look at it and here it is.

1/ All dbf table elements in table body (text/binary) are delimited with x20 (set) or asterisk (* = x2A = unset) its delete flag
2/ datetime type takes 8 bytes
3/ first 4 bytes are date in hours:
first byte represents values up to 256^1 -1
second byte represents values up to 256^2 -1
etc... (little endian?)
4/ second 4 bytes are time in miliseconds
it is similar like with date. AM/PM is computed from value.
5/ i dont know where lies the origin of date... it is somewhere in time "long before" ;-). Take your own origin.
Origin of time is 00 00 00 00 which means 00:00:00:000.

Example:
if you would time 01:00:00:001 AM from 12:00:00:001 AM you must add 1 hour to "1milisec time" (in the table you can find 20|2A ?? ?? ?? ?? 01 00 00 00 20|2A). 1h = 60 * 60 *10^3 miliseconds= x36 ee 80.
(reverse bytes order before operation)
Operation is x00 00 01 + x36 ee 80 and the binary result is 80 ee 36
(reverse bytes order after operation).

for more informations about dbf format see
</code> http://labrocca.com/peekdbf/peekuserman.html <code>

Thank you all for help!

Large ascii format file?

October 29, 2004 - 3:42 am UTC

Reviewer: Tony from Sweden

Maybe I have lost it completely - but what if I want to do this with a large ascii file instead of a binary - would I use utl_file? As I read in your book you suggested bfile for large files - but what if it is in fact a laaarge asci file? Or have I just missed something in the spec on bfile?

Thanks again for a great site!!!

/Tony



Tom Kyte

Followup  

October 29, 2004 - 8:21 am UTC

to load just text -- EXTERNAL TABLES if you have 9i, sqlldr if you don't

Two Errors

November 15, 2004 - 7:03 am UTC

Reviewer: T Farewell from Silsoe, England

Many thanks for putting your code up. I'm afraid I'm having a bit of trouble with it though. 

I created a simple dbf table to test this on. Called test.dbf, stored in D:\ORACLEstuff\test.dbf

Running O8i, with windows 2000.

I've put the two error messages below. Any advice?

Thank you!

---ERROR 1---
SQL>  create or replace directory MY_FILES as 'D:\ORACLEstuff';

Directory created.

SQL>  Begin dbase_pkg.load_Table('MY_FILES','test.dbf','test', p_show => true );
  2  end;
  3  /
 Begin dbase_pkg.load_Table('MY_FILES','test.dbf','test', p_show => true );
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.
ORA-06512: at "NS08TSF.DBASE_PKG", line 336
ORA-06512: at line 1


--- ERROR 2 ---

SQL> Begin dbase_pkg.load_Table('MY_FILES','d:\ORACLEstuff\test.dbf','test', p_show => true );
  2  end;
  3  /
Begin dbase_pkg.load_Table('MY_FILES','d:\ORACLEstuff\test.dbf','test', p_show => true );
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The device does not recognize the command.
ORA-06512: at "NS08TSF.DBASE_PKG", line 336
ORA-06512: at line 1 

Tom Kyte

Followup  

November 15, 2004 - 1:24 pm UTC

is D:\oraclestuff a directory on the DATABASE SERVER machine? (it has to be)

February 01, 2005 - 11:03 am UTC

Reviewer: Badry


February 05, 2005 - 10:49 pm UTC

Reviewer: Harry from Malaysia

Hi Tom,
The field name format in dbase and clipper are different,
so I use

p_flds(i).NAME := substr(l_data, 1, instr(l_data, chr(0)) - 1);

[In dbase]

before rtrim:
dump:Typ=1 Len=11: 67,79,85,78,84,82,89,0,0,0,0
dump:Typ=1 Len=11: 89,69,65,82,0,0,0,0,0,0,0

after rtrim:
dump:Typ=1 Len=7: 67,79,85,78,84,82,89
dump:Typ=1 Len=4: 89,69,65,82

Data Fields:
Field(1) Name = "COUNTRY", Type = N, Len = 6, Scale= 0
Field(2) Name = "YEAR", Type = N, Len = 4, Scale= 0


[In clipper]

before rtrim:
dump:Typ=1 Len=11: 82,82,69,67,80,78,79,0,128,18,62
dump:Typ=1 Len=11: 82,72,65,78,68,76,69,0,128,18,62

after rtrim:
dump:Typ=1 Len=7: 82,82,69,67,80,78,79
dump:Typ=1 Len=7: 82,72,65,78,68,76,69

Data Fields:
Field(1) Name = "RRECPNO", Type = C, Len = 10, Scale= 0
Field(2) Name = "RHANDLE", Type = C, Len = 30, Scale= 0

oracle to dbase via web procedure example

May 04, 2005 - 11:32 am UTC

Reviewer: Quinn Sinnott from MD

I see people have asked for the reverse ora2dbf. If you are using the pl/sql web toolkit access you can try the following procedure. It expects to be the action element of an html <form> that lists the users tables in a <select> element called in_table and has a submit type CANCEL and GO button called p_request.

The source is available at:

</code> http://sun.ars-grin.gov:8080/dbf.sql <code>


i want to insert some specific record

June 14, 2005 - 1:57 am UTC

Reviewer: cmchelp from india

please tell me how to insert only specific fileds using this package

Tom Kyte

Followup  

June 14, 2005 - 9:48 am UTC

you have the code, you would have to modify the code.

its not work other on other language except engilsh

June 15, 2005 - 6:44 am UTC

Reviewer: ksncmc from india

this procedure is good for me in case of eng but when my dbf file has iscii or is foc data then its gives error invalid number aslo i check it actually data in l_data not in usinform asnd also whaen i changed all fields to varchar then its insert any data any where plz i need ur help as much as quick as possible because i am now try to do it.

Tom Kyte

Followup  

June 15, 2005 - 9:51 am UTC

please debug it? I mean, you have the code and I don't have (and don't want) your data.

Will save me a lot of time, but...

July 08, 2005 - 2:47 pm UTC

Reviewer: Jon Waterhouse from St. John's, Newfoundland

At least using 9.2 on linux, the directory reference needs to be in upper case, so

create or replace directory capsdir as '/home/oracle'

declare
l_bfile bfile;
begin
l_bfile := bfilename( 'CAPSDIR', 'showdb.sql' );
dbms_lob.fileopen( l_bfile );
dbms_lob.fileclose( l_bfile );
end;
/

will work, but

l_bfile := bfilename( 'capsdir', 'showdb.sql' );

will not

Tom Kyte

Followup  

July 08, 2005 - 3:20 pm UTC

doesn't matter --linux, unix, mainframe, or windows -- the string you send to bfilename must match the case of the object you created


create directory capsdir as ....
bfilename('CAPSDIR')


or

create directory "capsdir" as ...
bfilename('capsdir')



January 17, 2006 - 10:50 am UTC

Reviewer: A reader


dbf to Oracle

April 11, 2006 - 12:16 am UTC

Reviewer: ajay from Lansing,Mi

Hi Tom,
I created the package and executed it,it works for some doesn't for some other.Here is an example where it doesn't work
Table that could be created to hold data:
create table t8
(
"TYPE" varchar2(12),
"ID" varchar2(12),
"NAME" varchar2(4),
"READONLY" varchar2(1),
"CKVAL" number(6),
"DATA" varchar2(4),
"UPDATED" date,
"
" varchar2(0),
"" varchar2(0),
"" varchar2(0),
"" varchar2(0),
"" varchar2(0),
"" varchar2(0),
"" varchar2(0),
"" varchar2(0))
/
column names and and data specs aren't right.This table cannot get created.Please suggest what should be done in such a scenario

Tom Kyte

Followup  

April 11, 2006 - 2:20 pm UTC

hows about a debugging hand here - what types are in the dbf file

You do understand you have the code - you can peek at the code - you can tweak the code - you can fix the code if code is broke in some fashion.

Sometimes the header isn't "full"

April 11, 2006 - 6:03 pm UTC

Reviewer: Doug Case from NJ USA

Ajay,

I have found that some .dbf file headers have some empty space at the end. This throws off the calculation of the number of fields.

I have modified the code a little to get the right number of columns.

This is the modified part of the package. Perhaps it will fix your problem too.

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;
l_save_bfile_offset number;
l_RightFieldCount number;
l_wrongFieldCount number;
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 );

l_RightFieldCount := p_hdr.no_fields;
l_wrongFieldCount := p_hdr.no_fields;
l_save_bfile_offset := p_bfile_offset;
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;

if ascii(substr(l_data,1,1)) < 33 then
l_RightFieldCount := i - 1;
Exit;
end if;
end loop;

p_hdr.no_fields := l_RightFieldCount;
p_bfile_offset := l_save_bfile_offset;

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 := l_save_bfile_offset + (l_field_desc_size * l_wrongFieldCount);
p_bfile_offset := p_bfile_offset +
mod( p_hdr.hdr_len - l_hdr_size,
l_field_desc_size );
end;



DBF to oracle

April 18, 2006 - 9:56 pm UTC

Reviewer: Ajay Bhat from lansing,mi

Thanks Tom and Doug for the prompt response.

utl_raw.cast_to_varchar2 problem

May 17, 2006 - 5:44 am UTC

Reviewer: Florin

I used the package created on this site on an oracle 9ir2 with windows. everything was ok.
then we moved the db to hpux with 10g.
there were some problems when using the utl_raw.cast_to_varchar2 function; on the same files gave 2 differents results :

in the get_header procedure we have :

test raw(32767);
l_data varchar2(100);

UNIX(HPUX):
after the following line :

test :=dbms_lob.substr( p_bfile, l_hdr_size, p_bfile_offset );

the content of test variable is :

0306050A82020000E10192000000000000000000000000000000000000030000

then we have
l_data := utl_raw.cast_to_varchar2(test);

and this content in Jdeveloper (hexa) :
[00..07] 0003 0006 0005 000A FFFD 0002 0000 0000



[08..15] FFFD 0001 FFFD 0000 0000 0000 0000 0000

[16..23] 0000 0000 0000 0000 0000 0000 0000 0000

[24..31] 0000 0000 0000 0000 0000 0003 0000 0000


Windows(xp 32 ):
after the following line :

test :=dbms_lob.substr( p_bfile, l_hdr_size, p_bfile_offset );

the content of test variable is :

0306050A82020000E10192000000000000000000000000000000000000030000

then we have
l_data := utl_raw.cast_to_varchar2(test);

and now the content in Jdeveloper (hexa) :
[00..07] 0003 0006 0005 000A 201A 0002 0000 0000

‚

[08..15] 00E1 0001 2019 0000 0000 0000 0000 0000 á ’

[16..23] 0000 0000 0000 0000 0000 0000 0000 0000

[24..31] 0000 0000 0000 0000 0000 0003 0000 0000


I think there is a small problem with everything which is on more than one byte(big endian? or maybe it seemed to me midle endian!)...
If somebody hit the same problem or knows the answer I am very interested. thanks.
I changed the code in the following way :

--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_records := numar_intreg( substr( test, 9, 8 ) );
p_hdr.hdr_len := numar_intreg( substr( test, 17, 4 ) );
p_hdr.rec_len := numar_intreg( substr( test, 21, 4 ) );

where the procedure numar_intreg is :


function numar_intreg(text in varchar2) return number is
i number default 2;
numar number default 0;
octet number default 0;
contor number default 0;
begin

while (true) loop

if substr(text,i,1) in ('A','a') then octet:=10;
elsif substr(text,i,1) in ('B','b') then octet:=11;
elsif substr(text,i,1) in ('C','c') then octet:=12;
elsif substr(text,i,1) in ('D','d') then octet:=13;
elsif substr(text,i,1) in ('E','e') then octet:=14;
elsif substr(text,i,1) in ('F','f') then octet:=15;
else octet:= to_number(substr(text,i,1));
end if;
numar := numar + octet*power(16,contor) ;
contor:=contor+1;
if substr(text,i-1,1) in ('A','a') then octet:=10;
elsif substr(text,i-1,1) in ('B','b') then octet:=11;
elsif substr(text,i-1,1) in ('C','c') then octet:=12;
elsif substr(text,i-1,1) in ('D','d') then octet:=13;
elsif substr(text,i-1,1) in ('E','e') then octet:=14;
elsif substr(text,i-1,1) in ('F','f') then octet:=15;
else octet:= to_number(substr(text,i-1,1));
end if;
numar := numar + octet*power(16,contor) ;
contor:=contor+1;
exit when i+2 > length(text);
i:=i+2;
end loop;
return numar;
exception
when others then
dbms_output.put_line(sqlerrm);
end numar_intreg;




Its Excellent - but how to handle Null values in DBF file

June 29, 2006 - 8:09 am UTC

Reviewer: Lakshmi Kanth from Visakhaptnam, Andhra Pradesh, India

Dear Tom, its working excellent - very much thankful for the topic. But It is adding records where data in all columns is existing. What if Null values are available in DBF file ?
As I am a beginner of Oracle, please kindly elaborate the steps.

Regards,
Lakshmi Kanth


Tom Kyte

Followup  

June 29, 2006 - 9:25 am UTC

there will not be nulls in there? blanks maybe - and that'll just return null once trimmed.

You need to load Memo fields

October 31, 2006 - 6:07 am UTC

Reviewer: Alex from Russia

Hi Tom.
I'am change code of your package. Add loading in Oracle data from FoxPro Memo fields (*.fpt) in clob fields.
See full code below. It works! Thank for idea.

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;


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 );
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 ||
') 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;

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
is
l_data varchar2(4000);
l_row rowArray;
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));
else
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 ',';

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;
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;
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( '/' );
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;
f_file varchar2(25);
memo_block 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_sql.parse( g_cursor,
build_insert( p_tname, p_cnames, l_flds ),
dbms_sql.native );
-- 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;

for i in 1 .. l_hdr.no_records loop
l_row := get_row( l_bfile,
l_offset,
l_hdr,
l_flds, f_bfile, memo_block );

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 );
if ( dbms_lob.isopen( f_bfile ) > 0 ) then
dbms_lob.fileclose( f_bfile );
end if;
exception
when others then
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;



HOW TO LOAD DBASE DATA

January 18, 2007 - 2:00 am UTC

Reviewer: Jason from Manila, Philippines

Hi Tom,

Your script is great and works fine. My dbf files are stored in drive H: and using oracle9i.

It is running smooth in a WINDOWS 2000 environment but when I migrated to a unix server. Your package encountered an error, indicating that it could not locate the directory path.

How could we set the directory path in Oracle using the unix server?

Thanks in advance.

Problem with loading FoxPro files (with or without Memo)

July 20, 2007 - 4:05 am UTC

Reviewer: A reader from Ukraine, Kiev

Hi!
I had same issue as many of you - load from dbf(Foxpro) to Oracle and I tried to use dbase_pkg.
I found (and fix myself) incorrect counting of fields amount, when in header a lot of free space after fields description, but packages(dbase_pkg or dbase_fox) doesn't work.
1. p_hdr.hdr_len := to_int( substr( l_data, 9, 2 ) ); works incorrectly (IMHO) - from file to file there are different values displayed - 424(seems OK), 85761 (is not OK).
Example(Hex view through Far):

0000000000: 30 01 08 16 07 00 00 00 | C8 01 87 00 00 00 00 00
0000000010: 00 00 00 00 00 00 00 00 | 00 00 00 00 01 C9 00 00

p_hdr.hdr_len = 85761
For this case it's "C8 01" as I understood? So, why 85761?
and then
2. p_hdr.rec_len = 0
as result
3. p_hdr.no_fields = 2679, but file contains only 5 fields.

I used following format description for my(0x30 - Visual FoxPro files)
http://www.dbf2002.com/dbf-file-format.html
Maybe somebody know, how to fix it.

P.S.
Thanks a lot for answers and this forum - huge oracle knowledge base.

Warning! Case sensitivity in Windows plus DBF HS/ODBC

June 04, 2008 - 10:34 am UTC

Reviewer: Andrew from Hungary, Europe

Dear Tom!

-- Please let me tell you, that Heterogeneous Services (ODBC) could not handle those *.DBF, that contains MEMO fields.
I really tried it through half a year. Little tables (containig less than 500 rows) maybe work for testing as for me but suprise come at 1000 lines. I forgot to mention: this was tested in Windows only.
-- regarding your code: God bless you. Dot.
I have to mention: When creating directory I strongly recommend to use CAPITALS to avoid everybody from days of debugging.
-- I forced to handle *.DBF files containing MEMO fields, so I have to append your code to handle MEMO files (*.DBT). I would share it with everybody if you confirm it.

A very good package.

October 02, 2008 - 8:36 am UTC

Reviewer: Umesh from Bengaluru, Karnataka, India.

Dear Tom,

I tried using your package for loading .dbf files. but there is a small drawback in this package. Package can read upto foxpro ver 2.x but it cannot read Visual Foxpro ver 3.0 and onwards. I was trying by modifying the package, but could not be successfull. I hope you can provide the solution for this.
Tom Kyte

Followup  

October 02, 2008 - 8:44 am UTC

why where you unsuccessful?

Do you have the file format for foxpro 3.0? If yes, then this should be easy - just write the code. If no, that is the cause of not being successful and you should get it and then you can write the code.

export oracle table to .dbf file

February 23, 2009 - 4:50 am UTC

Reviewer: aris s from indonesia

hi tom,

how to export oracle table to .dbf file?
Tom Kyte

Followup  

February 23, 2009 - 4:54 am UTC

write the inverse of the above.

that is, get the specification for a dbf (dbase file) and create a file that matches that specification.


You have access to the spec, you've seen an example of reading from a file that matches that spec and inserting into Oracle - should be rather easy to develop the other way around.

question

April 20, 2009 - 10:14 pm UTC

Reviewer: aa from idn

hai tom, i ask some question.

why i set regional setting on windows to indonesian display invalid number?
Tom Kyte

Followup  

April 21, 2009 - 2:57 pm UTC

this hurt my head.

Thank you for invaluable solution

April 26, 2009 - 6:44 pm UTC

Reviewer: Murug from Toronto, ON Canada

Hi While I am converting clipper to Oracle it was easy to load the data without all the odbc thing especially in Vista environment. Lot of commercial tool also requires OLE DB(etc). I would like to add the following two lines(at the bottom of those in get_row function since some null value stores as *.

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;
l_NumberRow NUMBER; -- by Roman
l_StringRow VARCHAR2(255); -- by Roman
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 ) ));
-- by Roman, jan.2004, Slovenia
-- Because of different NLS_LANG parameters, an error occured with message: INVALID NUMBER.
-- Because the type rowArray is consisted of strings, we have to change to real (system) decimal character.
-- Some could use , and someone could use .
-- So, what I do here:
-- I convert the string value of number to number value and that back to string value.
IF ( p_flds(i).TYPE = 'F' OR p_flds(i).TYPE = 'N' ) THEN -- Only for Float and Number type
IF Substr(l_row(i),1,1) = '*' THEN
l_row(i) :='0';
END IF;

Should be NULL instead of 0 assignment to previous post

April 28, 2009 - 8:55 pm UTC

Reviewer: A reader


Can I get full package (dbase_pkg)

September 24, 2009 - 8:38 am UTC

Reviewer: Sathis Kumar from India

Could some one please update the full package (dbase_pkg)

I have partial package as I need this package to load dbf file to oracle 9i (Unix).
Tom Kyte

Followup  

September 29, 2009 - 7:48 am UTC

here is the bottom bit:

                                        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( '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;
        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;


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;

end;
/


how to export oracle table or view to .dbf file

September 29, 2009 - 5:22 am UTC

Reviewer: Raj from India

We learn a lot from this site,u provide very usefull set of codes to read the file which in .dbf format to Oracle but how to export oracle table or view to .dbf file format? I hope you can provide the solution for this. please kindly elaborate the steps.
Tom Kyte

Followup  

October 02, 2009 - 9:06 am UTC

we don't, you can certainly write the code to do so however, just as I wrote the code to read dbase into Oracle, you can take the same exact approach.

You might be able to use odbc in some fashion to get the data into the other database - assuming they support odbc. see

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358

Thanks for the code

September 30, 2009 - 5:15 am UTC

Reviewer: A reader

It is now working, Thanks a lot Tom.

Oracle to ..dbf fle

October 01, 2009 - 4:19 am UTC

Reviewer: Basavaraj from india

Hi,
I have a select oracle sql query output of this query should saved as .dbf(dBase IV FILE) file . later i will use this output in foxpro database.
Please help me on this..
How we can achieve using pl/sql or forms?


Regards,
Basu


Cannot see the whole code of package

November 19, 2009 - 2:17 am UTC

Reviewer: RotKiv from Hungary

Tom, I'm just facing with the problem to upload data from dbf into oracle 9.2.
I found your solution, but here only the part of the code can be seen (and copy). Could you please help me to provide the whole code or the way I can get it from here?
Thanks a lot.


Tom Kyte

Followup  

November 23, 2009 - 3:20 pm UTC

look up two or three reviews from this one

About migrating the estructure

December 09, 2009 - 5:04 pm UTC

Reviewer: Ivan from Mexico

I don't have enough experience with oracle but I had experience with clipper now my question is?

Why I can't create the new structure in my database

I was trying to run the package and raise me the error

name of column duplicate

Let me explain better:

I dont know if has to be a lower version in which I should
ran the package I have the 9.2 version. or oracle

If you could help me

I will thank you a lot


Tom Kyte

Followup  

December 10, 2009 - 2:33 pm UTC

I'll need a bit more information - you don't run a package to create a new table or anything - you use CREATE TABLE.

I'm not sure what you are trying to do?

About Clipper files

December 10, 2009 - 6:59 pm UTC

Reviewer: Ivan from Mexico

I found that it was my mistake cause
I was not passing the right fields

This file i make it with excell and I save it like dbase III

and make it right


When i tried to import some other file it fails

And I figured out it was a clipper file

Let me tell you

What i did

I create the file in my database with a name

I passed the right fields

but

Now show me a leyend saying

that no have enough values.


Could you help me please

Or if you have the right code for clipper files

I'll be thankful





Tom Kyte

Followup  

December 11, 2009 - 7:20 am UTC

I have no idea what you are doing - or what tool you are using.

You don't show me any sql - without sql, you won't be doing anything in Oracle. To import a clipper file would necessitate either

a) using clipper to generate a report that represents a "flat file view" of the data (i.e. NOT a clipper file, just a plain old file) and then using sqlldr to load it.

b) using odbc and setting up a database link using heterogeneous services so you can select * from the clipper data.

I would suggest (a). Get the data in a neutral format, load it into Oracle.



May 19, 2010 - 6:07 am UTC

Reviewer: vyom from India

How can i know that a particular DBF file contains duplicated data.I have many DBF files on my system and dats y want to remove the duplicated ones.Can u help??
Tom Kyte

Followup  

May 24, 2010 - 10:37 am UTC

define "duplicate data"

once you do that, we will be well on our way to writing the query in question.

As it is - data files do not have "duplicate data", datafiles contain blocks and every block is unique - 100% unique. Therefore you must have a table that you want to de-duplicate, and that would be a query to do that.

If not (a table), then you need to be a tad more clear about what you mean.

facing prob in large size files

September 06, 2010 - 9:31 pm UTC

Reviewer: Aruna from india

Hi tom,
when i am loading 130 MB files it is completed in 5 mins.As per the expectation 300MB files should get completed in nearly 15 mins.our max file size is 1 GB.If i am placing 1 GB files it is going in or hours.i am using oracle 10g.Please help
Tom Kyte

Followup  

September 09, 2010 - 8:05 pm UTC

you give almost no information here at all.

but it sounds like

o at 130mb everything fits in the cache, the load happens and there you go, it is all in memory and we checkpoint it out later.

o as you go up in size, we eventually fill the buffer cache and you start waiting on buffer busy waits - you are waiting for dbwr to checkpoint blocks to disk before you can fill them up.


OR

o at 130mb, you have sufficient redo log to hold all of the loaded changes without having to reuse a redo log

o as you go up in size, we fill all of the redo logs and try to switch into a redo log you've already filled, but the checkpoint initiated by that log switch hasn't completed so you STOP and wait for it

OR

<about an infinite number of reasons here>




so, tell us, what diagnostics/metrics have you considered/looked at so far. any messages in your alert logs? what are you waiting on? what does a sql_trace show you? do you have awr/ash? what do they show you? if you don't have awr/ash - how about statspack - what is it showing you?

I'd guess either of the two things I posted above.

If it is "you filled the cache and now you wait" look into direct path loads (they bypass the cache).

If it is "you are flooding the logs", then add more logs to get you through this peak process or look for ways to reduce the redo log generation - using nologing coupled with direct path operations.

If it is neither, tell us what you are waiting on...

what if data columns get out of line?

November 04, 2010 - 10:25 am UTC

Reviewer: Martin from Houston, TX

Your solution has been one of the most helpful to me ever. Out of seven different dbf file structures, there is only one it is not working for. For this one dbf file, it reads through quite a few records without any problem, but then it runs into an "invalid number" error. By adding dbms_output's, I was able to locate the row number it was failing on and the offending data column. Somehow, on that row and column, it read one byte extra than it should have. Since the extra byte was a character rather than a number (and the field is defined as a number datatype), it caused the "invalid number" error. It is strange to me that it would do this since when I use MS Access 97's Fox Pro Jet Engine to read the same dbf file, it reads it correctly. I'm sure this is because I just don't understand everything about the procedure you use and dbf files in general (my own ignorance).

To attempt to fix this, I first tried to use the fix Roman from Slovenia offered, but it did not work for this (same error). Therefore, I implemented some code to subtract 1 from the l_n variable in the get_row function and put it between if-then statements that would only cause it to be incurred for that one record (p_row_no = 2286) and column where error was occurring (I = 119). Based on the dbms_output of the l_StringRow variable, this solution appears to fix that column and not adversely affect the subsequent columns--- until it gets to the very last column (column 311, defined as VARCHAR2 (24 Byte)), when it spits out an error of "not a valid month". This did not make sense since the datatype was not even a date. Nevertheless, I tried to force a value of "01-JAN-9999", but it then complained of "ORA-01858: a non-numeric character was found where a numeric was expected". Would you know why it is complaining of this and how to fix it? Below is the code I am currently using for the get_row and load_table functions. Below that is a sample of data with the leading header info.

FUNCTION get_row ( p_bfile IN BFILE, p_bfile_offset IN OUT NUMBER, p_hdr IN dbf_header, p_flds IN
field_descriptor_array, p_row_no in number, p_row_spacer in out number ) RETURN rowArray IS
l_data VARCHAR2(4000);
l_row rowArray;
l_n NUMBER DEFAULT 2;
l_NumberRow NUMBER; -- by Roman
l_StringRow VARCHAR2(255); -- by Roman
l_flds varchar2(18); --by Martin
l_length number; --by Martin
err_num number;
l_hdr varchar2(30);
BEGIN
l_data := utl_raw.cast_to_varchar2( dbms_lob.SUBSTR( p_bfile, p_hdr.rec_len, p_bfile_offset )
);
if p_row_no = 2286 then
dbms_output.put_line(' p_row_no = ' || p_row_no);
dbms_output.put_line('result ' || l_data);
end if;
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_row_no = 2286 then
--p_row_spacer := 0;
l_StringRow := l_row(i);
dbms_output.put_line('i: ' || i);
dbms_output.put_line('l_n: ' || l_n);
dbms_output.put_line('l_flds: ' || l_flds);

if I = 119 then
if (regexp_like(l_StringRow, '[A-Z]')) then
dbms_output.put_line('Found error in string: ' || l_StringRow);
dbms_output.put_line('l_n: ' || l_n);
dbms_output.put_line('l_flds: ' || l_flds);
l_n := l_n - 1;
dbms_output.put_line('l_n: ' || l_n);
--p_row_spacer := 1;
/* l_row(i) := RTRIM(LTRIM(SUBSTR( l_data, l_n, p_flds(i).LENGTH-p_row_spacer ) )); */
l_row(i) := RTRIM(LTRIM(SUBSTR( l_data, l_n, p_flds(i).LENGTH ) ));
l_StringRow := l_row(i);
end if;
end if;

if I between 142 and 143 then
--if (regexp_like(l_StringRow, '[0-9]')) then
l_flds := RTRIM(LTRIM(SUBSTR( l_data, l_n, p_flds(i).LENGTH) ));
l_length := length(l_flds);
dbms_output.put_line('l_length: ' || l_length);
if l_length = 1 then
dbms_output.put_line('Found error in string: ' || l_StringRow);
dbms_output.put_line('l_n: ' || l_n);
dbms_output.put_line('l_flds: ' || l_flds);
--p_row_spacer := 1;
/* l_row(i) := RTRIM(LTRIM(SUBSTR( l_data, l_n, p_flds(i).LENGTH-p_row_spacer ) )); */
l_row(i) := RTRIM(LTRIM(SUBSTR( l_data, l_n, p_flds(i).LENGTH ) ));
l_n := l_n + 1;
dbms_output.put_line('l_n: ' || l_n);
end if;
end if;
dbms_output.put_line(' l_StringRow: ' || l_StringRow);

--If we have looped to the last set of date fields (which produces error)
--have the value set to null to avoid error.

if I > 300 then
BEGIN
--IF ( p_flds(i).TYPE = 'D' ) THEN
--l_row(i) := null;
l_row(i) := '01-JAN-9999'; --99990101;
dbms_output.put_line('Found error in string: ' || l_StringRow);
l_StringRow := l_row(i);
dbms_output.put_line(' l_StringRow: ' || l_StringRow);
--end if;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error ORA-01843 found: ' || l_StringRow);
l_row(i) := '01-JAN-9999'; --99990101;
END;
end if;

end if;

-- by Roman, jan.2004, Slovenia
-- Because of different NLS_LANG parameters, an error occured with message: INVALID NUMBER.
-- Because the type rowArray is consisted of strings, we have to change to real (system) decimal character.
-- Some could use , and someone could use .
-- So, what I do here:
-- I convert the string value of number to number value and that back to string value.
/* IF ( p_flds(i).TYPE = 'F' OR p_flds(i).TYPE = 'N' ) THEN
-- Only for Float and Number type
BEGIN
l_StringRow := l_row(i);
--Save to temporary variable
l_StringRow := REPLACE ( l_StringRow , ',' , '.' );
--if p_row_no > 2284 then
--dbms_output.put_line('p_row_no= ' || p_row_no);
--dbms_output.put_line(' l_StringRow: ' || l_StringRow);
--end if;
l_StringRow := regexp_REPLACE ( l_StringRow , '[[:alpha:]]' , '' );

--We change characters
l_NumberRow := TO_NUMBER ( l_StringRow );
-- try to convert to number
l_row(i) := TO_CHAR( l_NumberRow );
--If succeded that we have right number value!
EXCEPTION
WHEN INVALID_NUMBER THEN
dbms_output.put_line(l_StringRow);
--If INSTR(l_StringRow,'=') > 0 THEN
--l_row(i) := '';
--end if;
WHEN OTHERS THEN
l_StringRow := l_row(i);
--Save to temporary variable
l_StringRow := REPLACE ( l_StringRow , '.' , ',' ); --We change characters
l_NumberRow := TO_NUMBER ( l_StringRow );
-- try to convert to number
l_row(i) := TO_CHAR( l_NumberRow );
--If succeded that we have right number value!
END;
END IF;
-- End Roman
*/

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-p_row_spacer;
l_n := l_n + p_flds(i).LENGTH;

END LOOP;
RETURN l_row;
END get_row;

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;
p_row_spacer number :=0;
f_file varchar2(25);
memo_block 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_sql.parse( g_cursor,
build_insert(p_tname,p_cnames,l_flds),
dbms_sql.native );

for i in 1 .. l_hdr.no_records loop --2286 loop
--if I = 2286 or I = 2287 then
--goto skip_invalid_number_error;
--end if;
--if i > 2284 then --Martin added to only get_row if at least row 2286
--dbms_output.put_line('rows i= '||i); --Martin added
--end if; --Martin added
l_row := get_row( l_bfile,
l_offset,
l_hdr,
l_flds,
I,
p_row_spacer );

if ( l_row(0) <> '*' ) -- deleted record
then
p_row_spacer := 0; --Martin added

for i in 1..l_hdr.no_fields loop
--if i/10 = round(i/10) then
--if i between 1 and 9 then --Martin added
--dbms_output.put_line('fields i= '||i); --Martin added
--end if;
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;
--else --Martin added
--dbms_output.put_line('l_row(0)= '||i); --Martin added
end if;
--goto end_row;
--<< skip_invalid_number_error>>
--null;
--dbms_output.put_line('Skipped invalid number error');
--<<end_row>>
--null;
--end if;
end loop;
end if;
commit;
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;

end;
/

õn^K^B^P^W^B'Î^NPT_NOC^A^TBILL_TYPEC^U^CBILL_DATEDSEQ_NOCPROV_NAMEC(^YPRO
V_ADRCA^YPROV_CITYCZ^TPROV_STCn^BPROV_ZIPCp PROV_PHONECy
PROV_FAXCM-^C
PROV_CNTRYCM-^M^BPROV_NPICM-^O^OPAYTO_NAMECM-^^^YPAYTO_ADRC•^YPAYTO_CITYCÐ^PPAYà
^BPAYTO_ZIPCâ^EFLD02DCç^YMED_RCRDC^A^QTAX_IDC^Q^A
SVC_FROMD^[SVC_THRUD#COV_DAYSN+NC_DAYSN.COINS_DAYSN2LRES_DAYSN5
FLD7AC8^A^YFLD7BCQ^A^YPT_IDCj^A^SPT_NAMEC}^A^^PT_STREETCM-^[^A(PT_CITYCÃ^A^^PT
_STCáPT_ZIPCã^A PT_CNTRYCìBIRTHDîSEXCö^A^AADM_DATED÷ADM_HOURCÿ^A
^BADM_TYPEC^AADM_SRCEC^B^B^ADISCH_HOURC^C^B^BPAT_STATUSC^E^B^BCND_CD1C^G^B^B
CND_CD2C ^B^BCND_CD3C^K^B^BCND_CD4C^M^B^BCND_CD5C^O^B^BCND_CD6C^Q^B^BCND_
CD7C^S^B^BCND_CD8C^U^B^BCND_CD9C^W^B^BCND_CD10C^Y^B^BCND_CD11C^[^B^BACDT_STC^]^B
^BFLD30AC^_^B^YFLD30BC8^B^YOC_CD1CQ^B^BOC_DT1DSOC_CD2C[^B^BOC_DT2D]OC_CD3Ce
^B^BOC_DT3DgOC_CD4Co^B^BOC_DT4DqOC_CD5Cy^B^BOC_DT5D{OC_CD6CM-^C^B^BOC_DT6DM-^E
OC_CD7CM-^M^B^BOC_DT7DM-^OOC_CD8CM-^W^B^BOC_DT8DM-^YOC_SPN_CD1C¡^B^BOC_FROM1D£OC
_THRU1D«OC_SPN_CD2C³^B^BOC_FROM2DµOC_THRU2D½OC_SPN_CD3CÅ^B^BOC_FROM3DÇOC
_THRU3DÏOC_SPN_CD4C×^B^BOC_FROM4DÙOC_THRU4DáFLDN37ACé^B^YFLDN37BC^B^C^YRES
P1C^[^C(RESP2CC^C(RESP3Ck^C(RESP4CM-^S^C(RESP5C»^C(VAL_CD1Cã^C^BVAL_AMT1Nå^C^K^A
L_CD2Cð^C^BVAL_AMT2Nò^C^K^BVAL_CD3Cý^C^BVAL_AMT3Nÿ^C^K^BVAL_CD4C
^D^BVAL_AMT4N^L^D^K^BVAL_CD5C^W^D^BVAL_AMT5N^Y^D^K^BVAL_CD6C$^D^BVAL_AMT6N&^D^K
^BVAL_CD7C1^D^BVAL_AMT7N3^D^K^BVAL_CD8C>^D^BVAL_AMT8N@^D^K^BVAL_CD9CK^D^BVAL_AMT
9NM^D^K^BVAL_CD10CX^D^BVAL_AMT10NZ^D^K^BVAL_CD11Ce^D^BVAL_AMT11Ng^D^K^BVAL_CD12C
r^D^BVAL_AMT12Nt^D^K^BTOT_CHGSN^?^D^K^BTOT_NCOVNM-^J^D^K^BINS_PLAN1CM-^U^D
INS_NAME1CM-^_^D^YINS_NPI1C¸^D^OREL_INFO1CÇ^D^ABEN_ASGN1CÈ^D^APRIOR_PAY1NÉ^D^K^S
T_RESP1NÔ^D^K^BINS_OTHID1Cß^D^MINS_PLAN2Cì^D
INS_NAME2Cö^D^YINS_NPI2C^O^E^OREL_INFO2C^^^E^ABEN_ASGN2C^_^E^APRIOR_PAY2N ^E^K^B
EST_RESP2N+^E^K^BINS_OTHID2C6^E^MINS_PLAN3CC^E
INS_NAME3CM^E^YINS_NPI3Cf^E^OREL_INFO3Cu^E^ABEN_ASGN3Cv^E^APRIOR_PAY3Nw^E^K^BEST
_RESP3NM-^B^E^K^BINS_OTHID3CM-^M^E^MINSRD_NAM1CM-^Z^E^YINSRD_REL1C³^E^BINSRD_IDS
GRP_NAME1CÈ^E^NGRP_NO1CÖ^E^QINSRD_NAM2Cç^E^YINSRD_REL2C^F^BINSRD_ID2C^B^F^SGRP_N
AME2C^U^F^NGRP_NO2C#^F^QINSRD_NAM3C4^F^YINSRD_REL3CM^F^BINSRD_ID3CO^F^SGRP_NAME3
Cb^F^NGRP_NO3Cp^F^QAUTH_CD1CM-^A^F^RDCN1CM-^S^F^WEMP_NAME1Cª^F^XAUTH_CD2CÂ^F^RDÔ
^F^WEMP_NAME2Cë^F^XAUTH_CD3C^C^G^RDCN3C^U^G^WEMP_NAME3C,^G^XDX_VERCD^G^ADG_CD1CE
^G^GDG_POA1CL^G^ADG_CD2CM^G^GDG_POA2CT^G^ADG_CD3CU^G^GDG_POA3C\^G^ADG_CD4C]^G^GD
G_POA4Cd^G^ADG_CD5Ce^G^GDG_POA5Cl^G^ADG_CD6Cm^G^GDG_POA6Ct^G^ADG_CD7Cu^G^GDG_POA
7C|^G^ADG_CD8C}^G^GDG_POA8CM-^D^G^ADG_CD9CM-^E^G^GDG_POA9CM-^L^G^ADG_CD10CM-^M^A
10CM-^T^G^ADG_CD11CM-^U^G^GDG_POA11CM-^\^G^ADG_CD12CM-^]^G^GDG_POA12C¤^G^ADG_CDD
G_POA13C¬^G^ADG_CD14C­^G^GDG_POA14C´^G^ADG_CD15Cµ^G^GDG_POA15C¼^G^ADG_CD16C½^G^G
DG_POA16CÄ^G^ADG_CD17CÅ^G^GDG_POA17CÌ^G^ADG_CD18CÍ^G^GDG_POA18CÔ^G^AFLD68ACÕ^G^Y
FLD68BCî^G^YADM_DG_CDC^GVIS_DG_CD1C^GVIS_DG_CD2C^GVIS_DG_CD3C^GPPS_CODEC#
^CECI_DG_CD1C^GECI_POA1C^AECI_DG_CD2C^GECI_POA2C^AECI_DG_CD3C^GECI_POA3C=
^AFLD73C^YPR_CD1C^GPR_DT1PR_CD2C^GPR_DT2PR_CD3C^GPR_DT3PR_CD4C^GPR
_DT4PR_CD5C^GPR_DT5PR_CD6C^GPR_DT6FLD75AC^YFLD75BC^YFLD75CC^YFLD75
DC^YDR_NPI1C^U ^KDR_QUAL1C ^BDR_OTHID1C" ^LDR_LAST1C. ^PDR_FIRST1C>
^LDR_NPI2CJ ^KDR_QUAL2CU ^BDR_OTHID2CW ^LDR_LAST2Cc ^PDR_FIRST2Cs
^LDR_TYPE3C^? ^BDR_NPI3CM-^A ^KDR_QUAL3CM-^L ^BDR_OTHID3CM-^N ^LDR_LA
^PDR_FIRST3Cª ^LDR_TYPE4C¶ ^BDR_NPI4C¸ ^KDR_QUAL4CÃ ^BDR_OTHID4CÅ
^LDR_LAST4CÑ ^PDR_FIRST4Cá ^LREMARKS1Cí 1REMARKS2C^^
1REMARKS3CO
1REMARKS4CM-^@
1CC_QUAL1C±
^BCC_CODE1C³

CC_VAL1C½
^LCC_QUAL2CÉ
^BCC_CODE2CË

CC_VAL2CÕ
^LCC_QUAL3Cá
^BCC_CODE3Cã

CC_VAL3Cí
^LCC_QUAL4Cù
^BCC_CODE4Cû

CC_VAL4C^E^K^LPAYOR_IDC^Q^K^EHOSPITALC^VACCT_STATC^^STATUSC&^K^AINTERNALC'^K
^DCARRIERC+^K^ESENT_DTD0SENT_DT1D8SENT_FILEC@^K^LIMPORT_DTDLIMPORT_DT1DT^K
LOGGED_DTD\LOGGED_DT1DdTOT_CHGS1Nl^K^K^BTOT_NCOV1Nw^K^K^BBILLERCM-^B^K^YCARG
OMM-^[^K
USERCHAR1C¥^KPUSERCHAR2Cõ^KPUSERCHAR3CE^LPUSERCHAR4CM-^U^LPFIN_CLASSCå^L^LSVC_LC
ñ^L^LPT_TYPECý^L^LOVERLAPC ^M^LUSERREM1C^U^MPUSERREM2Ce^MPUSERREM3Cµ^MPUSER
REM4C^E^NPUSERDATE1DUUSERDATE2D]USERDATE3DeUSERNUM1Nm^N^K^BUSERNUM2Nx^N^K
^BUSERNUM3NM-^C^N^K^BHOLD_CODECM-^N^N
ACCT_NOCM-^X^N^TSOURCEC¬^N
PROV_REPC¶^N^X^M 9012345678 111201009301888018 M.D. ANDERSON CANCER CENT1515
HOLCOMBE BLVD HOUSTON TX7703040097137922991
1111111111 M.D. ANDERSON CANCER CENT1515 HOLCOMBE BLVD HOUSTON
TX77030 0123456 123456789 2010092020100926 0
0 0 0 F
SMITHSSS ,MARTIN DAVID 1234 ELMER ST LAREDO
TX12345 US20101104F2010092018171301
MARTIN DAVID SMITHSSS
1234 ELMER ST
LAREDO TX 12345 UNITED STATES
02 0.0080 6.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00 0.00 0.00 26834.05
0.00Z24 BCBS CLAIMS Z24 YY 0.00
26834.05123456789
0.00 0.00
0.00 0.00 SMITHSSS,JOHN D 12345678910
ABCDEFGHIJKLM 12ABCDEFGHIJKL
1234567891
928800 20400 2841 78061 78079 3558 3549 7245
28800 574
1234567891 FRANKLIN JOHN
BCBS CLAIMS Z24 .
P.O. BOX 200555 AUSTIN ,TX 78720
F MCAID20101001 MCAID115.ECS
0.00 0.00Stacy Jonest 7056
0.00 0.00 0.00


Null columns in dbf file

November 24, 2010 - 3:23 am UTC

Reviewer: Max from India

Hi Tom

I am facing a problem while uploading dbf using the above procedure. I am getting certain null columns in the dbf file and so procedure i not able to upload the same.

Kindly help.
Tom Kyte

Followup  

November 24, 2010 - 9:03 am UTC

you have the code
you have your dbf file
you are a programmer (I assume)

please start debugging the process here and figuring out what is wrong. You have access to *everything* here (more so than I do - please don't post your DBF file)

I gave you the concept - the initial implementation - now you adjust it to meet your needs.

that and I don't even know what:

I am
getting certain null columns in the dbf file and so procedure i not able to
upload the same.


means.

field length 0

November 29, 2010 - 12:35 am UTC

Reviewer: A Reader from USA

Can some one please tell that for some dbf files i am getting some of the field lengths as 0, as a result those fields are not getting uploaded in oracle.
I suppose there is something wrong with the below line.

p_flds(i).length := ascii( substr( l_data, 17, 1 ) );

But i dont know how to correct it. Please help me.

Tom Kyte

Followup  

November 29, 2010 - 2:22 am UTC

define what you mean by "those fields are not getting uploaded" - what are your symptoms? what do you want to have loaded if the field lengths are 0????

field length 0

November 29, 2010 - 3:06 am UTC

Reviewer: A Reader from USA

for certain fields in the dbf the length is coming as 0, whereas when i see the dbf in dbf explorer, they have lengths like 10, 5, 3 etc.

i suppose there is something wrong with the below line of code

p_flds(i).length := ascii( substr( l_data, 17, 1 ) );

but i have no clue how to fix it.

i am pasting below the create table script which the procedure is generating.

create table MSA06
(
"ACCOUNT" varchar2(8),
"NAME" varchar2(25),
"ADDR1" varchar2(30),
"ADDR2" varchar2(30),
"CLASS" number(1),
"CATEGORY" number(1),
"MTRSTATUS" number(1),
"MTRTYPE" number(1),
"WRCODE" number(2),
"NBILL" number(2),
"WCONSIZE" number(2),
"WCONDATE" date,
"WATERFEE" number(6),
"MEMONO" varchar2(10),
"MTRINSDATE" date,
"METERNO" varchar2(14),
"MTRWDTH" number(2),
"AVGCONSUM" number(10,3),
"WATERBILL" number(9,2),
"EXCISE" number(9,2),
"PREVREAD" number(10),
"PREVDATE" date,
"PRESREAD" number(0),
"PRESDATE" date,
"BILLNO" varchar2(0),
"RICODE" varchar2(0),
"ENTBY" varchar2(0),
"FLAG" number(0))

/

Kindly help me.

Tom Kyte

Followup  

November 29, 2010 - 4:17 am UTC

you'll have to debug this yourself, I don't have (nor do I want!) your data. I just followed the "specification" I found to read a DBF file.

field length 0

November 29, 2010 - 5:03 am UTC

Reviewer: A Reader from USA

Thanx Tom, but can you give me some link where i could find the specifications to read the dbf files ?

As i am new in this field and i couldnt find any good link for this.

Thanx anyways :)
Tom Kyte

Followup  

November 29, 2010 - 6:49 am UTC

I actually have a link in the original answer above... Did you click it yet?

what if data columns get out of line?

December 01, 2010 - 10:00 am UTC

Reviewer: Martin from Houston, TX

Hi Tom,

I wrote the Nov 4, 2010, review above. Do you have any ideas for what could be causing the issue? Is there anything else you need to know from me? I am dependent on you for help. Thanks for whatever you can do.
Tom Kyte

Followup  

December 07, 2010 - 8:49 am UTC

You'll have to debug it - I just implemented a bit of code to parse a certain format, that was documented. If your file does not conform to the document I used (and linked to above, you can read it), then you'll have to find documentation that describes your format and adjust the code accordingly.

Trying to determine why the # of records is not being read correclty from header.

February 17, 2011 - 10:06 am UTC

Reviewer: Sandy Chopra from McLean, VA USA

We have been using this program successfully for 40+ files that we have been parsing on a scheduled basis. However, for approx. 10 files, the program reads that there are 2164314118 reocrds in the file which there are not. When I open the file in Acess, it only shows 12000 records. Has anyone else run into this problem where there is an issue with determining the #of records?

Not working for me

July 06, 2011 - 11:05 am UTC

Reviewer: Scott from PA, USA

Hi, When I run the code, I get:

Error report:
ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06512: at "SGER.DBASE_PKG2", line 336
ORA-06512: at line 2
01747. 00000 - "invalid user.table.column, table.column, or column specification"
*Cause:
*Action:


I know it is in code:
dbms_sql.parse( g_cursor,
build_insert (p_tname,p_cnames,l_flds),
dbms_sql.native );

My Oracle version is 9i and I am working with a Foxpro .dbf file. The code to show works fine. But when I want to actually import the data, I get this error. All my columns come from the output of the SHOW. Can anybody get me past this error? Thanks, Scott
Tom Kyte

Followup  

July 08, 2011 - 11:56 am UTC

debug it - you have all of the code, put some dbms_output's in there to see what SQL is being generated. Perhaps it will be obvious what is happening here.

I'll guess that the cnames you send in is wrong. If you look at build_insert, it is pretty straight forward:

    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;


if you gave me cnames - I use them - you are probably passing in the wrong columns.

You could let it default if the columns in the table are in the same order as your dbf file

too many values

July 12, 2011 - 9:50 pm UTC

Reviewer: AA from ID

First of all, thanx for the posting. It prove most useful for my existing project where we need to
convert dbf (yes, people do use it still) to oracle.

Question: Is the package able to perform selective load?
Example:
begin
dbase_pkg.load_table( 'MY_FILES',
't1.dbf',
't1',
'cfield, logical,cfield, logical,cfield, logical,cfield, logical,cfield, logical,cfield, logical,cfield, logical,cfield, logical,cfield, logical,cfield, logical' );
end;
/
(I have tried but it failed with 'too many values' error)

Cheers
-AA-
Tom Kyte

Followup  

July 13, 2011 - 8:07 pm UTC

this package can do anything...

because you have the code, and you can alter the code, and you can make the code do anything....


I don't know what a selective load is or means.

I don't understand how 'cfield,logical,cfield,logical....' works? you cannot have the same column name more than once in a table in Oracle.

Migrating .dbf files (Oracle 10g) to oracle 11g database

July 26, 2011 - 5:32 am UTC

Reviewer: Jerry from Bangalore,india

Hi Tom,
Will the above package work for Exporting .dbf files from 10g to 11g?
Regards,
Jerry
Tom Kyte

Followup  

July 28, 2011 - 6:25 pm UTC

ummm, no you would not use this for an Oracle .dbf file.

This was for DBASE files (probably before your time - you might not ever have heard of DBASE - a small DOS based database from the 80's / early 90's).


You just upgrade oracle databases - we don't unload and reload them.

use dbse_pkg in oracle 11g

August 02, 2011 - 11:35 pm UTC

Reviewer: jonas from indonesia

dbase_pkg whether this could be used in oracle 11g?
Tom Kyte

Followup  

August 03, 2011 - 7:33 am UTC

sure, it is just plsql code - very straightforward stuff and all in "clear text" (you can read it)

how to use dbase_pkg in oracle linux

August 05, 2011 - 2:15 am UTC

Reviewer: jonas from indonesia

dbase_pkg in oracle how to use linux, setting up the oracle directory?
Tom Kyte

Followup  

August 05, 2011 - 8:19 am UTC

create or replace directory your_dir_name as '/your/path/to/where/the/file/is';



it is pretty much the same as you would do on any OS, using any release of Oracle, with any bit of code that wanted to use a bfile - nothing special.

Hi tom

September 14, 2011 - 2:06 pm UTC

Reviewer: Randall Garita from Costa Rica

if i try this on oracle enterprise 11g and works excelent, but if i try to do this in oracle XE 10g display this error,
ORA-00928: falta la palabra clave SELECT, can anybody help me.
Tom Kyte

Followup  

September 15, 2011 - 7:28 am UTC

give us a quick demo, it would be neat to learn what line number - what inputs you pass. so, fire up sqlplus - make the call - and cut and paste the error stack in its entirety

December 06, 2011 - 6:15 am UTC

Reviewer: A reader

Great Link ... Awesome PLSQL work !!

February 01, 2012 - 5:19 am UTC

Reviewer: Kapil Gupta from India

Hi Tom,
i read your hole package and reviews given by users. But still i am not able to transfer my data.
In last i get message given below

BEGIN
dbase_pkg.load_table
('TEMPDATA',
'TEMP.dbf',
'TEMP',
'C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,C29,C30,C31,C32,C33,C34,C35,C36,C37,C38,C39,C40,C41,C42,C43,C44,C45,C46,C47,C48,C49,C50,C51,C52,C53,C54,C55,C56,C57,C58,C59,C60,C61,C62,C63,C64,C65,C66,C67,C68,C69,C70,C71,C72,C73,C74,C75,C76,C77,C78,C79'
);
END;
Error at line 2
ORA-01722: invalid number
ORA-06512: at "GEMINI.DBASE_PKG", line 471
ORA-06512: at line 2

please help me. i have to do this work on weekly bases and most urgent for me.
Tom Kyte

Followup  

February 01, 2012 - 7:40 am UTC

what sort of debugging as a programmer did you do on this package? what sort of work did you put into researching this?

for you see - I neither have, nor desire to have, your data. I cannot debug your issue, only you can.....

Error in the last part

May 31, 2012 - 1:09 pm UTC

Reviewer: Alejandra from Mexico,d.f.

Hi Tom,
It looks like it works, but when I tried to run this last part:

begin
dbase_pkg.load_Table('MY_FILES', 'althpd1.dbf','althpd1',p_show => TRUE);
end;
/

sql plus response:

begin
*
ERROR at line 1:
ORA-22288: fallo de la operacion de archivo o LOB FILEOPEN
El sistema no puede encontrar la ruta especificada.
ORA-06512: en "SYSTEM.DBASE_PKG", linea 281
ORA-06512: en linea 2

althpd1.dbf is already in the path C:\export\home\dbf

I was running it at Windows 7 OS, with oracle 11g.

I hope you could help me, pls be gently, this is my first time working with oracle.

Thanks.!
Tom Kyte

Followup  

May 31, 2012 - 1:31 pm UTC

can you let us know what line 281 is for you - for me, using a cut and paste - it is a blank line of code....

select * from user_source to find this out. get us lines 280 - 282

ORA-01722: invalid number

July 18, 2012 - 2:02 pm UTC

Reviewer: Jaime from Mexico

When I run the package send me next error

How can i review the probem?

ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at "OWNER1.DBASE_PKG", line 357
ORA-06512: at line 13
Tom Kyte

Followup  

July 18, 2012 - 3:27 pm UTC

time to start debugging, just add some debug code or get sqldeveloper and run it in the debugger.

I'd start by adding just a few lines of dbms_output.put_line to see what is happening around the code that is failing.

ORA-01722: invalid number

July 19, 2012 - 8:52 am UTC

Reviewer: Jaime from Mexico

Hi Tom

I rewiew the problem, i change my table and all field now are varchar2, and the problem solved but the problem begins quen in the file come information in float field

Thanks for your help, I checked the problem, apparently the problem is when the field is float in the DBF file, trying to load the information is not recognized as a number, change the numeric fields to character and insert with out a problem the information in temporary table, but when i trying inserted into the final table I can not convert the field that came to float to numeric, mark me invalid number, how can i review that fields to convert to numeric

Oracle 2 dbf

April 11, 2013 - 7:16 am UTC

Reviewer: Claudiu from Bucharest, Romania

I reverse Tom's original package and I create a new package to export a SQL query to DBF. Oracle DB version used is 11G.

How to use - exec oracle2dbf.export_dbf('EXPORT_DIR','gen1.dbf','select * from all_objects where rownum<11');

CREATE OR REPLACE PACKAGE oracle2dbf AS

-- export a dbf file
function generate_dbf (p_query IN VARCHAR2) return blob;
procedure export_dbf (p_dir IN VARCHAR2,
p_file IN VARCHAR2,
p_query IN VARCHAR2);

END oracle2dbf;
/


CREATE OR REPLACE PACKAGE BODY oracle2dbf AS

type tp_integer_tab is table of integer;
t_chars tp_integer_tab := tp_integer_tab( 1, 8, 9, 96, 112 );
t_dates tp_integer_tab := tp_integer_tab( 12, 178, 179, 180, 181 , 231 );
t_numerics tp_integer_tab := tp_integer_tab( 2, 100, 101 );

procedure set_date_format(p_format in varchar)
is

begin

execute immediate 'ALTER SESSION SET NLS_DATE_FORMAT='''||p_format||'''';

end;

/*****************************************************************************************/
PROCEDURE export_blob (p_blob IN BLOB,
p_dir IN VARCHAR2,
p_file_name IN VARCHAR2)
is

lf_file utl_file.file_type;
lr_raw_data raw(32767);
ln_buffer_size binary_integer := 32767;
ln_read_offset integer := 1;
ln_blob_len integer;

begin

ln_blob_len := dbms_lob.getlength(p_blob);
ln_read_offset := 1;
lf_file := utl_file.fopen(p_dir, p_file_name, 'wb', ln_buffer_size);
while ln_read_offset < ln_blob_len loop
dbms_lob.read(p_blob, ln_buffer_size, ln_read_offset, lr_raw_data);
utl_file.put_raw(lf_file, lr_raw_data, true);
ln_read_offset := ln_read_offset + ln_buffer_size;
end loop;

utl_file.fclose(lf_file);

EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(lf_file) THEN
UTL_FILE.fclose(lf_file);
END IF;
RAISE;
end;

/*****************************************************************************************/
-- Function to convert a PLSQL number
-- into a binary unsigned integer
function to_bui( p_nr in number, p_len in number ) return varchar2
is
v_rez varchar2(10);
v_nr number(10);
v_rest number(10);

e_Cifre exception;

begin

IF p_len<=0 or p_len>99 then
raise e_cifre;
END IF;

v_rest:=p_nr;

for i in REVERSE 1..p_len loop

v_nr:=trunc(v_rest/power(2,8*(i-1)));
v_rez:=v_rez||CHR(v_nr);
v_rest:=mod(v_rest,power(2,8*(i-1)));

end loop;

select reverse(v_rez) into v_rez from dual;

return v_rez;

exception
when e_cifre then return null;
end;

/*****************************************************************************************/
-- select oracle2dbf.generate_dbf('select * from all_objects where rownum<11') from dual
function generate_dbf (p_query IN VARCHAR2) return blob AS

l_cursor PLS_INTEGER;
l_rows PLS_INTEGER;
l_col_cnt PLS_INTEGER;
l_desc_tab DBMS_SQL.desc_tab;
l_buffer VARCHAR2(32767);
l_enclose varchar2(5);
cnt number:=0;
v_col_nr number:=1;
v_col_name varchar2(32);
l_max_den number(10):=10;
v_max_len number(10);

v_an number(4);
v_luna number(2);
v_zi number(2);

TYPE dbf_rec IS RECORD (
col_type VARCHAr2(1) := 0,
col_name VARCHAR2(32) := '',
col_max_len BINARY_INTEGER := 0,
col_precision BINARY_INTEGER := 0,
col_dec_point BINARY_INTEGER := 0,
col_offset BINARY_INTEGER := 0
);
TYPE dbf_tab IS TABLE OF dbf_rec INDEX BY BINARY_INTEGER;

l_dbf_tab dbf_tab;

v_offset number(10);
v_total number(10);
v_blob blob;
v_blob_ret blob;
v_row varchar2(4000);

e_no_rows exception;

BEGIN

set_date_format('YYYYMMDD');
execute immediate 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''. ''';

l_cursor := DBMS_SQL.open_cursor;
DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);

DBMS_SQL.describe_columns (l_cursor, l_col_cnt, l_desc_tab);

FOR i IN 1 .. l_col_cnt LOOP
DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );
END LOOP;

l_rows := DBMS_SQL.execute(l_cursor);

-- Identify the column details
v_offset:=0;
v_total:=0;
FOR i IN 1 .. l_col_cnt LOOP

case
-- limitation
-- for numeric data use always 18 col max len with 6 precision
when l_desc_tab( i ).col_type member of t_numerics
then l_dbf_tab(i).col_type:='N';
l_dbf_tab(i).col_max_len:=18;
l_dbf_tab(i).col_precision:=6;
l_dbf_tab(i).col_dec_point:=1;
l_dbf_tab(i).col_offset:=v_offset;
v_offset:=v_offset+18;
-- punctul zecimal
v_total:=v_total+18;

when l_desc_tab( i ).col_type member of t_dates
then l_dbf_tab(i).col_type:='D';
l_dbf_tab(i).col_max_len:=8;
l_dbf_tab(i).col_precision:=0;
l_dbf_tab(i).col_dec_point:=0;
l_dbf_tab(i).col_offset:=v_offset;
v_offset:=v_offset+8;
v_total:=v_total+8;

when l_desc_tab( i ).col_type member of t_chars
then v_max_len:=least(254,l_desc_tab(i).col_max_len);
l_dbf_tab(i).col_type:='C';
l_dbf_tab(i).col_max_len:=v_max_len;
l_dbf_tab(i).col_precision:=0;
l_dbf_tab(i).col_dec_point:=0;
l_dbf_tab(i).col_offset:=v_offset;
v_offset:=v_offset+v_max_len;
v_total:=v_total+v_max_len;

else
null;
end case;

IF LENGTH(l_desc_tab(i).col_name)>l_max_den then
v_col_name:=substr(l_desc_tab(i).col_name,1,8)||'_'||v_col_nr;
v_col_nr:=v_col_nr+1;
ELSE
v_col_name:=l_desc_tab(i).col_name;
END IF;

l_dbf_tab(i).col_name:=v_col_name;

END LOOP;

-- create temporary blob
dbms_lob.createtemporary( v_blob, true );

-- output the columns header
FOR i IN 1 .. l_col_cnt LOOP

v_row:=rpad(l_dbf_tab(i).col_name,11,CHR(0))
-- tip coloana
||l_dbf_tab(i).col_type
-- pozitia 0+18
||to_bui(l_dbf_tab(i).col_offset,4)
-- lungime
||CHR(l_dbf_tab(i).col_max_len)
-- zecimale
||CHR(l_dbf_tab(i).col_precision)
||rpad(CHR(0),14,CHR(0));

dbms_lob.append( v_blob, utl_raw.cast_to_raw(v_row));

END LOOP;

-- sfarsit coloane
v_row:= CHR(13);
dbms_lob.append( v_blob, utl_raw.cast_to_raw(v_row));

-- Output the data.
LOOP
EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;
cnt:=cnt+1;
v_row:=' ';

FOR i IN 1 .. l_col_cnt LOOP

DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);

IF l_dbf_tab(i).col_type='N' then
v_row:=v_row||lpad(nvl(trim(to_char(l_buffer,'99999999999.999999')),' '),18,' ');
else
v_row:=v_row||rpad(nvl(l_buffer,' '),l_dbf_tab(i).col_max_len,' ');
END IF;

END LOOP;

-- dbms_output.put_line('*'||v_row||'*');

dbms_lob.append( v_blob, utl_raw.cast_to_raw(v_row));

END LOOP;

-- sfarsit fisier
v_row:= CHR(26);
dbms_lob.append( v_blob, utl_raw.cast_to_raw(v_row));

IF DBMS_SQL.is_open(l_cursor) THEN
DBMS_SQL.close_cursor(l_cursor);
END IF;

IF cnt=0 then raise e_no_rows;
END IF;

v_an:=to_number(to_char(sysdate,'YYYY'));
v_luna:=to_number(to_char(sysdate,'MM'));
v_zi:=to_number(to_char(sysdate,'DD'));

-- header
-- version
v_row:=CHR(3)
-- 1900 + an
||CHR(v_an-1900)
-- luna
||CHR(v_luna)
-- ziua
||CHR(v_zi)
-- numar de randuri
||to_bui(cnt,4)
-- lungime header (formula 32*(nr_rows+1)+1
||to_bui(32*(l_col_cnt+1)+1,2)
-- lungime record (se aduna 19+64+8) - atentie la punctul zecimal
||to_bui(v_total+1,2)
-- caractere pana la 20 pe zero
||rpad(CHR(0),20,CHR(0));

-- creat temporary blob
dbms_lob.createtemporary( v_blob_ret, true );
dbms_lob.append( v_blob_ret, utl_raw.cast_to_raw(v_row));
dbms_lob.append( v_blob_ret, v_blob);
dbms_lob.freetemporary( v_blob );

return v_blob_ret;

EXCEPTION
WHEN e_no_rows then RAISE_APPLICATION_ERROR(-20000, 'No data!!!');
WHEN OTHERS THEN
IF DBMS_SQL.is_open(l_cursor) THEN
DBMS_SQL.close_cursor(l_cursor);
END IF;
RAISE;
END generate_dbf;

/*****************************************************************************************/
-- exec oracle2dbf.export_dbf('EXPORT_DIR','gen1.dbf','select * from all_objects where rownum<11');
procedure export_dbf (p_dir IN VARCHAR2,
p_file IN VARCHAR2,
p_query IN VARCHAR2) AS

v_blob blob;

begin

v_blob:=generate_dbf(p_query);
export_blob(v_blob,p_dir,p_file);
dbms_lob.freetemporary( v_blob );

end;

END oracle2dbf;
/

FETCH DATA FROM DBF FILE INTO ORACLE

May 31, 2013 - 12:42 pm UTC

Reviewer: HARSH from INDIA

RESPECTED TOM SIR,

EXCELLENT CODE FOR INSERTING DATA FROM DBF TO ORACLE DATABASE.
YOUR CONCEPTS AND DISCUSSION HELP US A LOT FOR OUR DAY TO DAY DEVELOPMENT IN ORACLE.

MY BEST WISHES FOR YOU TO GO AHEAD AND SHARE USEFUL R &D.

THANKS,

HARSH SHAH
ORACLE DEVELOPER

Multilingual data not supported

June 19, 2013 - 1:53 pm UTC

Reviewer: Akhilesh from INDIA

Hi tom,
This package is working for me but there are some problem.
1:Multilingual data has been changed.
2:some data of C1 column goes to other column C2 .
Tom Kyte

Followup  

June 19, 2013 - 8:16 pm UTC

you got the code! you have all of it! you can fix it. (for you see, I don't have your data, i don't have a database with your characterset - well, maybe I do, but how do I know) and so on.

you've got the best thing possible here - simple working source code, documentation for the file format...

Oracle XE vs Oracle Enterprise

May 02, 2014 - 12:35 am UTC

Reviewer: Richard Sandoz from New Orleans, LA USA

When I run this to convert a set of 9 different foxpro tables on Oracle Enterprise 11g, this works flawlessly. When I run this with Oracle XE, I found that I am getting various casting errors resulting in gigantic numbers and such. I found the root cause to be the default character set. Specifically, the utl_raw.cast_to_varchar2 is zeroing out numbers 194 or greater. ex. select ascii(chr(194)), ascii(chr(193)) from dual; should return 194,193 (as does in Enterprise 11G) not 0,193 (XE). I started to write my own function to do the job of utl_raw.cast_to_varchar2, but was just curious if there was a quickhackier better easier way.

varchar2 hacking

May 03, 2014 - 3:43 pm UTC

Reviewer: A reader from New Orleans, LA USA

I believe many of the problems are rooted to the hacking of the VARCHAR2 dataype, especially when it comes to the various default character sets the various Oracle installations use. I started using:
utl_raw.cast_to_binary_integer(utl_raw.substr(...

instead of:
ascii( substr(...

with raw datatype and am getting better results. (Just did the header so far):
set serveroutput on
--create or replace DIRECTORY DBF_FILES as '/mnt/dbf';
--create or replace DIRECTORY DBF_FILES as 'F:\dbf';
declare
  l_bfile bfile;

  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
  );

  procedure get_header(p_bfile in bfile, p_hdr in out dbf_header) is
    l_data            raw(32);
    l_hdr_size        number default 32;
    l_field_desc_size number default 32;
  begin
    l_data := dbms_lob.substr(p_bfile, l_hdr_size, 1);
    p_hdr.version    := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 1, 1));
    p_hdr.year       := 1900 + utl_raw.cast_to_binary_integer(utl_raw.substr( l_data, 2, 1));
    p_hdr.month      := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 3, 1));
    p_hdr.day        := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 4, 1));
    p_hdr.no_records := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data,  5, 4),2);
    p_hdr.hdr_len    := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data,  9, 2),2);
    p_hdr.rec_len    := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 11, 2),2);
    p_hdr.no_fields  := trunc((p_hdr.hdr_len - l_hdr_size) / l_field_desc_size);
  end;

  procedure show(p_hdr in dbf_header, p_bfile in bfile) is
  begin
    dbms_output.put_line(chr(9)||p_hdr.version
                       ||chr(9)||p_hdr.year
                       ||chr(9)||p_hdr.month
                       ||chr(9)||p_hdr.day
                       ||chr(9)||p_hdr.no_records
                       ||chr(9)||p_hdr.hdr_len
                       ||chr(9)||p_hdr.rec_len
                       ||chr(9)||p_hdr.no_fields
                       ||chr(9)||dbms_lob.getlength(p_bfile));
  end;

  procedure showtable(p_file in varchar2) is
    l_bfile bfile;
    l_hdr   dbf_header;
  begin
    dbms_output.put(p_file);
    l_bfile := bfilename('DBF_FILES', p_file);
    dbms_lob.fileopen(l_bfile);
    get_header(l_bfile, l_hdr);
    show(l_hdr, l_bfile);
    dbms_lob.fileclose(l_bfile);
  end;

begin
  dbms_output.put_line('DBASE File'
             ||chr(9)||'Version'
             ||chr(9)||'Year'
             ||chr(9)||'Month'
             ||chr(9)||'Day'
             ||chr(9)||'#Recs'
             ||chr(9)||'HdrLen'
             ||chr(9)||'RecLen'
             ||chr(9)||'#Fields'
             ||chr(9)||'Size');
  dbms_output.put_line('=========='
             ||chr(9)||'======='
             ||chr(9)||'===='
             ||chr(9)||'====='
             ||chr(9)||'==='
             ||chr(9)||'====='
             ||chr(9)||'======'
             ||chr(9)||'======'
             ||chr(9)||'======='
             ||chr(9)||'====');
  showtable('names.dbf');
  showtable('comps.dbf');
  showtable('locs.dbf');
  showtable('jobs.dbf');
  showtable('orders.dbf');
end;
/

updated

May 07, 2014 - 9:18 pm UTC

Reviewer: Richard Sandoz from New Orleans, LA

We will be eventually upgrading Oracle to 12 as many will.

The problem with the newer versions of Oracle as well as XE, is that they use AL32UTF8 as the default character set whereas 11G uses WE8MSWIN1252.
    SQL>select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

The problem with the different character sets is that there are 'holes' within ranges that when converted to binary and then back zero out.
    SQL>select ascii(chr(194)), ascii(chr(193)) from dual;
(This returns 194,193 with the WE8MSWIN1252 as default, but 0,193 with the newer AL32UTF8 character set.  This is a problem when trying to use these entities to store binary data that could very well be these values.)

I found that using containers as RAW instead of VARCHAR2 proved better.  I tried changing the original code to use NVARCHAR2, but had better albeit mixed and nonconclusive results.  Rather than trying to adapt the original code, I did a full-on rewrite starting with reading the file data into a raw(32) using just dbms_lob.substr(), rather than the original use of a varchar2(100) and utl_raw.cast_to_varchar2(dbms_lob.substr()) to read it.

    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_filename is the name of a file in that directory
      --            will be the name of the DBASE file
      -- p_colnames 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_rownum boolean that activates an autonum
      --          functionality such that a sequential
      --          numbered virtual column is created
      procedure loadtable(p_filename in varchar2
                        , p_colnames in varchar2 default null
                        , p_rownum in boolean default false);
    
      -- procedure to print (and not insert) what we find in
      -- the DBASE files (not the data, just the info
      -- from the dbase headers....)
      --
      -- p_filename is the name of a file in that directory
      --            will be the name of the DBASE file
      -- p_colnames 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_rownum boolean that activates an autonum
      --          functionality such that a sequential
      --          numbered virtual column is created
      procedure showtable(p_filename in varchar2
                        , p_colnames in varchar2 default null
                        , p_rownum in boolean default false);
    end;
    /
    
    create or replace package body dbase_fox as
      PREFIX     constant varchar2(32) default 'stage_';
      CR         constant varchar(2)   default chr(13)||chr(10);
      MEMODTYPE  constant varchar2(32) default 'varchar2(4000)';
      ROWNUMNAME constant varchar2(32) default '"ROWNUM"';
      FRAMESIZE  constant integer      default 1000;
    
      addrownum  boolean := false;
      colnames   varchar2(255) := '';
      filename   varchar2(32) := '';
      dbfbfile   bfile := null;
      fptbfile   bfile := null;
    
      DBF_HEADER_SIZE constant number default 32;
      type dbf_header_type 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
      );
      dbf_header dbf_header_type := null;
      subtype    dbf_header_data is raw(32);
    
      DBF_FIELD_DESCRIPTOR_SIZE constant number default 32;
      type dbf_field_descriptor_type is record (
          name     varchar2(11)
         ,type     char(1)
         ,length   int    -- 1 byte length
         ,decimals int    -- 1 byte scale
      );
      type dbf_field_descriptor_array is table of dbf_field_descriptor_type index by binary_integer;
      subtype dbf_field_descriptor_data is raw(32);
      dbf_field_descriptor dbf_field_descriptor_array;
    
      type rowarray_type is table of dbms_sql.varchar2_table index by binary_integer;
      rowarray rowarray_type;
    
      subtype raw_type is raw(4000);
      type rawarray_type is table of raw_type index by binary_integer;
      rawarray rawarray_type;
    
      loadcursor binary_integer;
      mblocksize number := 0;
    
      procedure get_header is
        l_data dbf_header_data;
      begin
        l_data := dbms_lob.substr(dbfbfile, DBF_HEADER_SIZE, 1);
        dbf_header.version    := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 1, 1));
        dbf_header.year       := 1900 + utl_raw.cast_to_binary_integer(utl_raw.substr( l_data, 2, 1));
        dbf_header.month      := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 3, 1));
        dbf_header.day        := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 4, 1));
        dbf_header.no_records := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 5, 4),2);
        dbf_header.hdr_len    := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 9, 2),2);
        dbf_header.rec_len    := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 11, 2),2);
        dbf_header.no_fields  := trunc((dbf_header.hdr_len - DBF_HEADER_SIZE) / DBF_FIELD_DESCRIPTOR_SIZE);
      end;
    
      procedure get_header_fields is
        l_data dbf_field_descriptor_data;
      begin
        for i in 1 .. dbf_header.no_fields loop
          l_data := dbms_lob.substr(dbfbfile, DBF_FIELD_DESCRIPTOR_SIZE, 1+DBF_HEADER_SIZE+(i-1)*DBF_FIELD_DESCRIPTOR_SIZE); -- starting past the header
          dbf_field_descriptor(i).name     := rtrim(utl_raw.cast_to_varchar2(utl_raw.substr(l_data,1,11)),chr(0));
          dbf_field_descriptor(i).type     := utl_raw.cast_to_varchar2(utl_raw.substr(l_data, 12, 1));
          dbf_field_descriptor(i).length   := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data, 17, 1));
          dbf_field_descriptor(i).decimals := utl_raw.cast_to_binary_integer(utl_raw.substr(l_data,18,1));
        end loop;
      end;
    
      procedure show_field_header_columns is
      begin
        dbms_output.put_line(CR||'Num'
                   ||chr(9)||'Name       '
                   ||chr(9)||'Type'
                   ||chr(9)||'Length'
                   ||chr(9)||'Decimals');
        dbms_output.put_line('==='
                   ||chr(9)||'====       '
                   ||chr(9)||'===='
                   ||chr(9)||'======'
                   ||chr(9)||'========');
      end;
    
      procedure show_header(p_file_length in integer) is
      begin
        dbms_output.put_line(chr(9)||dbf_header.version
                           ||chr(9)||dbf_header.year
                           ||chr(9)||dbf_header.month
                           ||chr(9)||dbf_header.day
                           ||chr(9)||dbf_header.no_records
                           ||chr(9)||dbf_header.hdr_len
                           ||chr(9)||dbf_header.rec_len
                           ||chr(9)||dbf_header.no_fields
                           ||chr(9)||p_file_length);
      end;
    
      procedure show_fields is
      begin
        for i in dbf_field_descriptor.first .. dbf_field_descriptor.last loop
          dbms_output.put_line(i
                     ||chr(9)||rpad(dbf_field_descriptor(i).name,11,' ')
                     ||chr(9)||dbf_field_descriptor(i).type
                     ||chr(9)||dbf_field_descriptor(i).length
                     ||chr(9)||dbf_field_descriptor(i).decimals);
        end loop;
      end;
    
      function build_insert return varchar2 is
        l_statement long;
      begin
        l_statement := 'insert into ' || PREFIX || filename || ' (';
    
        if colnames is not null then
          l_statement := l_statement || colnames;
        else
          for i in dbf_field_descriptor.first .. dbf_field_descriptor.last loop
            if i <> 1 then
              l_statement := l_statement || ',';
            end if;
            l_statement := l_statement || '"'||  dbf_field_descriptor(i).name || '"';
          end loop;
          if addrownum then
            l_statement := l_statement || ',' || ROWNUMNAME;
          end if;
        end if;
    
        l_statement := l_statement || ') values (';
    
        for i in dbf_field_descriptor.first .. dbf_field_descriptor.last loop
          if i <> 1 then
            l_statement := l_statement || ',';
          end if;
          if dbf_field_descriptor(i).type = 'D' then
            l_statement := l_statement || 'to_date(:bv' || i || ',''yyyymmdd'' )';
          else
            l_statement := l_statement || ':bv' || i;
          end if;
        end loop;
        if addrownum then
          l_statement := l_statement || ',:bv' || (dbf_field_descriptor.last + 1);
        end if;
        l_statement := l_statement || ')';
        return l_statement;
      end;
    
      function build_create return varchar2 is
        l_statement long;
      begin
        l_statement := 'create table ' || PREFIX || filename || ' (';
    
        for i in dbf_field_descriptor.first .. dbf_field_descriptor.last loop
          l_statement := l_statement || CR;
          if i <> dbf_field_descriptor.first then
            l_statement := l_statement ||',';
          else
            l_statement := l_statement ||' ';
          end if;
          l_statement := l_statement || '"'||  dbf_field_descriptor(i).name || '"'||chr(9);
          if dbf_field_descriptor(i).type = 'D' then
            l_statement := l_statement || 'date';
          elsif dbf_field_descriptor(i).type = 'F' then
            l_statement := l_statement || 'float';
          elsif dbf_field_descriptor(i).type = 'N' then
            if dbf_field_descriptor(i).decimals > 0 then
              l_statement := l_statement || 'number('||dbf_field_descriptor(i).length||','|| dbf_field_descriptor(i).decimals || ')';
            else
              l_statement := l_statement || 'number('||dbf_field_descriptor(i).length||')';
            end if;
          elsif dbf_field_descriptor(i).type = 'M' then
            l_statement := l_statement || MEMODTYPE;
          else
            l_statement := l_statement || 'varchar2(' || dbf_field_descriptor(i).length || ')';
          end if;
        end loop;
        if addrownum then
          l_statement := l_statement || CR || ',' || ROWNUMNAME || chr(9) || 'number';
        end if;
        l_statement := l_statement ||CR||');'||CR||'/';
        return l_statement;
      end;
    
      procedure show_header_columns is
      begin
        dbms_output.put_line(CR||'DBASE File'
                   ||chr(9)||'Version'
                   ||chr(9)||'Year'
                   ||chr(9)||'Month'
                   ||chr(9)||'Day'
                   ||chr(9)||'#Recs'
                   ||chr(9)||'HdrLen'
                   ||chr(9)||'RecLen'
                   ||chr(9)||'#Fields'
                   ||chr(9)||'Size');
        dbms_output.put_line('=========='
                   ||chr(9)||'======='
                   ||chr(9)||'===='
                   ||chr(9)||'====='
                   ||chr(9)||'==='
                   ||chr(9)||'====='
                   ||chr(9)||'======'
                   ||chr(9)||'======'
                   ||chr(9)||'======='
                   ||chr(9)||'====');
      end;
    
      procedure loadtablerecord(i in number) is
        l_n      number;
        l_fblock number;
        l_data   raw_type;
      begin
        l_data := dbms_lob.substr(dbfbfile,dbf_header.rec_len,2+DBF_HEADER_SIZE+dbf_header.no_fields*DBF_FIELD_DESCRIPTOR_SIZE+(i-1)*dbf_header.rec_len); -- starting past the header and field descriptors
        rawarray(0) := utl_raw.substr(l_data, 1, 1);
        l_n := 2;
        for j in 1 .. dbf_header.no_fields loop
          rawarray(j) := utl_raw.substr(l_data,l_n,dbf_field_descriptor(j).length);
          if dbf_field_descriptor(j).type = 'F' and rawarray(j) = '.' then
            rawarray(j) := null;
          elsif dbf_field_descriptor(j).type = 'M' then
            if dbms_lob.isopen(fptbfile) != 0 then
              l_fblock := nvl(utl_raw.cast_to_binary_integer(dbms_lob.substr(fptbfile, 4, to_number(trim(utl_raw.cast_to_varchar2(rawarray(j))))*mblocksize+5)),0);
              rawarray(j) := dbms_lob.substr(fptbfile, l_fblock, to_number(trim(utl_raw.cast_to_varchar2(rawarray(j))))*mblocksize+9);
            else
              dbms_output.put_line(filename || '.fpt not found');
            end if;
          end if;
          l_n := l_n + dbf_field_descriptor(j).length;
        end loop;
      end;
      
      procedure loadtablearray(p_cntarr in int) is
        l_bulkcnt number;
      begin
        for j in 1 .. dbf_header.no_fields loop
          dbms_sql.bind_array(loadcursor, ':bv'||j, rowarray(j),1,p_cntarr);
        end loop;
        if addrownum then
          dbms_sql.bind_array(loadcursor, ':bv'||(dbf_header.no_fields+1), rowarray(dbf_header.no_fields+1),1,p_cntarr);
        end if;
        begin
          l_bulkcnt := dbms_sql.execute(loadcursor);
          --dbms_output.put_line('Bulk insert count ' || l_bulkcnt);
        exception
          when others then
            dbms_output.put_line('Bulk insert failed ' || sqlerrm);
            dbms_output.put_line(build_insert);
        end;
      end;
    
      procedure loadtablebulk is
        l_cntrow int default 0;
        l_cntarr int default 0;
      begin
        loadcursor := dbms_sql.open_cursor;
        dbms_sql.parse(loadcursor, build_insert, dbms_sql.native);
    
        for i in 1 .. dbf_header.no_records loop
          loadtablerecord(i);
          if utl_raw.cast_to_varchar2(rawarray(0)) <> '*' then
            l_cntarr := l_cntarr + 1;
            for j in 1 .. dbf_header.no_fields loop
              rowarray(j)(l_cntarr) := trim(utl_raw.cast_to_varchar2(rawarray(j)));
            end loop;
            if addrownum then
              l_cntrow := l_cntrow + 1;
              rowarray((dbf_header.no_fields+1))(l_cntarr) := l_cntrow;
            end if;
            if l_cntarr >= FRAMESIZE then
              loadtablearray(l_cntarr);
              l_cntarr := 0;
            end if;
          end if;
        end loop;
        if l_cntarr > 0 then
          loadtablearray(l_cntarr);
        end if;
    
        dbms_sql.close_cursor(loadcursor);
      exception
        when others then
          if dbms_sql.is_open(loadcursor) then
            dbms_sql.close_cursor(loadcursor);
          end if;
          dbms_output.put_line('loadtable failed for ' || filename);
          dbms_output.put_line('insert ' || build_insert);
      end;
    
      procedure open_dbf is
      begin
        dbfbfile := bfilename('DBF_FILES', filename || '.dbf');
        dbms_lob.fileopen(dbfbfile);
      end;
    
      procedure open_fpt is
      begin
        fptbfile := bfilename('DBF_FILES', filename || '.fpt');
        if dbms_lob.fileexists(fptbfile) != 0 then
          dbms_lob.fileopen(fptbfile);
        end if;
      end;
    
      procedure close_dbf is
      begin
        if dbms_lob.isopen(dbfbfile) > 0 then
          dbms_lob.fileclose(dbfbfile);
        end if;
      end;
    
      procedure close_fpt is
      begin
        if dbms_lob.isopen(fptbfile) > 0 then
          dbms_lob.fileclose(fptbfile);
        end if;
      end;
    
      procedure initialize is
        l_empty_dbf_field_descriptor dbf_field_descriptor_array;
        l_empty_rowarray rowarray_type;
        l_empty_rawarray rawarray_type;
      begin
        dbfbfile := null;
        fptbfile := null;
        dbf_field_descriptor := l_empty_dbf_field_descriptor;
        dbf_header := null;
        rowarray := l_empty_rowarray;
        rawarray := l_empty_rawarray;
        loadcursor := 0;
        mblocksize := 0;
      end;
    
      procedure showtable(p_filename in varchar2, p_colnames in varchar2 default null, p_rownum in boolean default false) is
      begin
        filename := p_filename;
        addrownum := p_rownum;
        colnames := p_colnames;
    
        initialize;
    
        open_dbf;
    
        get_header;
        get_header_fields;
    
        show_header_columns;
        dbms_output.put(filename || '.dbf');
        show_header(dbms_lob.getlength(dbfbfile));
        show_field_header_columns;
        show_fields;
    
        dbms_output.put_line(CR||'Insert statement:');
        dbms_output.put_line(build_insert);
    
        dbms_output.put_line(CR||'Create statement:');
        dbms_output.put_line(build_create);
    
        close_dbf;
      exception
        when others then
          close_dbf;
          raise;
      end;
    
      procedure loadtable(p_filename in varchar2, p_colnames in varchar2 default null, p_rownum in boolean default false) is
      begin
        filename := p_filename;
        addrownum := p_rownum;
        colnames := p_colnames;
    
        initialize;
    
        open_dbf;
        open_fpt;
    
        if dbms_lob.isopen(fptbfile) > 0 then
          mblocksize := utl_raw.cast_to_binary_integer(dbms_lob.substr(fptbfile, 2, 7));
        else
          mblocksize := 0;
        end if;
    
        get_header;
        get_header_fields;
    
        loadtablebulk;
    
        close_dbf;
        close_fpt;
      exception
        when others then
          close_dbf;
          close_fpt;
          raise;
      end;
    
    end;
    /


    declare
    begin
      dbase_fox.showtable('names');
      dbase_fox.showtable('comps');
      dbase_fox.showtable('locs');
      dbase_fox.showtable('jobs');
      dbase_fox.showtable('orders', p_rownum=>true);

      dbase_fox.loadtable('names');
      dbase_fox.loadtable('comps');
      dbase_fox.loadtable('locs');
      dbase_fox.loadtable('jobs');
      dbase_fox.loadtable('orders', p_rownum=>true);
    end;
    /

DBF Format Information

May 22, 2019 - 10:46 pm UTC

Reviewer: Code Novice from USA

I am currently using Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production. This installation has produced 6 DBF files of which I am unable to utilize the DBASE_PKG or the latest DBASE_FOX to read in these files.

Instead of asking Tom to fix this issue in the code I am instead curious to know where I can obtain information as to how I can fix the code. Where do I go in order to understand Oracle's DBF Files. In my initial attempts at reading/importing these DBF files I (tried) used ACCESS and MS Excel which have options of choosing DBF 3 or DBF IV file types. From what I learned these have nothing to do with Oracle's format.

So where do I go from here? How do I even begin to understand how to parse these files or any file?

It was this SO Post that lead me here. It seems many others have found their way to this AskTom question but not all have been able to figure out how to make use of the code here when it doesn't work for them. I am certainly one of these people.
My dbms_output is blank and/or I am getting PL/SQL: numeric or value errors.
https://stackoverflow.com/questions/56248380/how-to-read-a-dbase-file-in-java-or-oracle-pl-sql

Connor McDonald

Followup  

May 27, 2019 - 6:35 am UTC

OK, there are some mixed signals and terminologies here.

DBASE (if memory serves) used to store its data in files suffixed as DBF. Obviously DBASE the program could read these, and people sometimes wanted to be able to read the data from inside those files with *other* programs (eg if you they want to migrate the data to something else).

The Oracle database *also* by default creates files with a DBF extension. But they have absolutely no relationship or anything in common with a DBASE file. In fact, you could call an Oracle database file *antyhing* you want - we just use "DBF" as an acronym for "database file".

So the files that created as part of your express edition installation are *Oracle* files - they are *not* DBASE files.

improving

March 06, 2020 - 1:11 pm UTC

Reviewer: shredder from Russia

thanks for your code.
I added:
1. using sqlloader engine for reading from .dbf thru external table
2. using .dbf codepage (Americans nether care about it :) )

and have just published it on github:
https://github.com/shredder2003/DBASE_FOX
Chris Saxon

Followup  

March 09, 2020 - 10:56 am UTC

Thanks for sharing.