reading data from dbf
balu, June 18, 2001 - 2:03 am UTC
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 ...
Mattia Rossi, November 06, 2001 - 9:23 am UTC
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
Deepali Aggarwal, November 17, 2001 - 6:17 am UTC
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
mick barlow, August 19, 2002 - 10:06 am UTC
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.
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
Shyampaliyath, August 30, 2002 - 6:25 am UTC
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
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
k ullur, August 30, 2002 - 1:15 pm UTC
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...
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
Shyampaliyath, September 02, 2002 - 12:58 am UTC
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.
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
Shyampaliyath, September 05, 2002 - 3:57 am UTC
atlast it worked. Thank u for u r help
Vice Versa
Mick Barlow, September 11, 2002 - 4:29 am UTC
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
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
andrew, December 05, 2002 - 2:05 pm UTC
Thanks, but what is the best way to import DBF
Edgar Chupit, December 23, 2002 - 8:37 am UTC
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.
Output
Steve, March 06, 2003 - 9:59 am UTC
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
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
Steve, March 06, 2003 - 5:17 pm UTC
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..
Vikas Gupta, August 01, 2003 - 4:21 am UTC
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.
August 01, 2003 - 7:59 am UTC
reading data from .dbf file into oracle 8i -- HOW TO LOAD DBASE data", version oracle 8i
Dulal Sarkar, September 21, 2003 - 1:42 am UTC
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.
padmaja, November 19, 2003 - 12:49 am UTC
the info is quite useful . but how to load memo fields into Oracle Database (8i) .
HOW TO LOAD DBASE data
Vijay, December 06, 2003 - 10:36 am UTC
Given info is very useful. but what about memo field in the dbf file. Please advise.
Thanks in advance
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
Steve, December 09, 2003 - 10:07 am UTC
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
A reader, January 06, 2004 - 2:01 pm UTC
Corrected function because of different NLS_LANG parameter!
Roman Frelih, January 07, 2004 - 4:26 am UTC
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?
W. Leong, January 28, 2004 - 10:00 pm UTC
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-
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
Henk Koekkoek, February 24, 2004 - 10:36 am UTC
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.
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!
Robert Massey, March 08, 2004 - 1:23 pm UTC
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!!
Reynaldo Vial, April 23, 2004 - 11:46 am UTC
This package is perfect to mirate data from dbase to oracle.
Great Package
WK, June 23, 2004 - 3:06 am UTC
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
Antony Pyatkov, July 08, 2004 - 9:27 am UTC
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?
July 08, 2004 - 9:41 am UTC
you might be able to use the convert() builtin function -- documented in the sql reference.
Thanks!
Antony Pyatkov, July 09, 2004 - 1:16 am UTC
It's working! Excuse me please for such simple question.
data from .dbf file into oracle 8i
Michael diPilla, October 12, 2004 - 9:10 am UTC
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?
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
Hvezdosvit, October 22, 2004 - 4:01 am UTC
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?
Tony, October 29, 2004 - 3:42 am UTC
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
October 29, 2004 - 8:21 am UTC
to load just text -- EXTERNAL TABLES if you have 9i, sqlldr if you don't
Two Errors
T Farewell, November 15, 2004 - 7:03 am UTC
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
November 15, 2004 - 1:24 pm UTC
is D:\oraclestuff a directory on the DATABASE SERVER machine? (it has to be)
Badry, February 01, 2005 - 11:03 am UTC
Harry, February 05, 2005 - 10:49 pm UTC
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
Quinn Sinnott, May 04, 2005 - 11:32 am UTC
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
cmchelp, June 14, 2005 - 1:57 am UTC
please tell me how to insert only specific fileds using this package
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
ksncmc, June 15, 2005 - 6:44 am UTC
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.
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...
Jon Waterhouse, July 08, 2005 - 2:47 pm UTC
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
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')
A reader, January 17, 2006 - 10:50 am UTC
dbf to Oracle
ajay, April 11, 2006 - 12:16 am UTC
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
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"
Doug Case, April 11, 2006 - 6:03 pm UTC
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
Ajay Bhat, April 18, 2006 - 9:56 pm UTC
Thanks Tom and Doug for the prompt response.
utl_raw.cast_to_varchar2 problem
Florin, May 17, 2006 - 5:44 am UTC
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
Lakshmi Kanth, June 29, 2006 - 8:09 am UTC
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
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
Alex, October 31, 2006 - 6:07 am UTC
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
Jason, January 18, 2007 - 2:00 am UTC
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)
A reader, July 20, 2007 - 4:05 am UTC
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
Andrew, June 04, 2008 - 10:34 am UTC
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.
Umesh, October 02, 2008 - 8:36 am UTC
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.
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
aris s, February 23, 2009 - 4:50 am UTC
hi tom,
how to export oracle table to .dbf file?
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
aa, April 20, 2009 - 10:14 pm UTC
hai tom, i ask some question.
why i set regional setting on windows to indonesian display invalid number?
April 21, 2009 - 2:57 pm UTC
this hurt my head.
Thank you for invaluable solution
Murug, April 26, 2009 - 6:44 pm UTC
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
A reader, April 28, 2009 - 8:55 pm UTC
Can I get full package (dbase_pkg)
Sathis Kumar, September 24, 2009 - 8:38 am UTC
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).
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
Raj, September 29, 2009 - 5:22 am UTC
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.
Thanks for the code
A reader, September 30, 2009 - 5:15 am UTC
It is now working, Thanks a lot Tom.
Oracle to ..dbf fle
Basavaraj, October 01, 2009 - 4:19 am UTC
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
RotKiv, November 19, 2009 - 2:17 am UTC
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.
November 23, 2009 - 3:20 pm UTC
look up two or three reviews from this one
About migrating the estructure
Ivan, December 09, 2009 - 5:04 pm UTC
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
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
Ivan, December 10, 2009 - 6:59 pm UTC
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
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.
vyom, May 19, 2010 - 6:07 am UTC
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??
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
Aruna, September 06, 2010 - 9:31 pm UTC
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
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?
Martin, November 04, 2010 - 10:25 am UTC
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
Max, November 24, 2010 - 3:23 am UTC
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.
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
A Reader, November 29, 2010 - 12:35 am UTC
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.
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
A Reader, November 29, 2010 - 3:06 am UTC
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.
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
A Reader, November 29, 2010 - 5:03 am UTC
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 :)
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?
Martin, December 01, 2010 - 10:00 am UTC
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.
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.
Sandy Chopra, February 17, 2011 - 10:06 am UTC
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
Scott, July 06, 2011 - 11:05 am UTC
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
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
AA, July 12, 2011 - 9:50 pm UTC
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-
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
Jerry, July 26, 2011 - 5:32 am UTC
Hi Tom,
Will the above package work for Exporting .dbf files from 10g to 11g?
Regards,
Jerry
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
jonas, August 02, 2011 - 11:35 pm UTC
dbase_pkg whether this could be used in oracle 11g?
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
jonas, August 05, 2011 - 2:15 am UTC
dbase_pkg in oracle how to use linux, setting up the oracle directory?
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
Randall Garita, September 14, 2011 - 2:06 pm UTC
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.
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
A reader, December 06, 2011 - 6:15 am UTC
Great Link ... Awesome PLSQL work !!
Kapil Gupta, February 01, 2012 - 5:19 am UTC
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.
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
Alejandra, May 31, 2012 - 1:09 pm UTC
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.!
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
Jaime, July 18, 2012 - 2:02 pm UTC
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
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
Jaime, July 19, 2012 - 8:52 am UTC
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
Claudiu, April 11, 2013 - 7:16 am UTC
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
HARSH, May 31, 2013 - 12:42 pm UTC
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
Akhilesh, June 19, 2013 - 1:53 pm UTC
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 .
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
Richard Sandoz, May 02, 2014 - 12:35 am UTC
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
A reader, May 03, 2014 - 3:43 pm UTC
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
Richard Sandoz, May 07, 2014 - 9:18 pm UTC
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
Code Novice, May 22, 2019 - 10:46 pm UTC
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
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
shredder, March 06, 2020 - 1:11 pm UTC
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
March 09, 2020 - 10:56 am UTC
Thanks for sharing.