We help IT Professionals succeed at work.

Need Oracle 9.2 query to export data to a .DBF format

I need to write an Oracle 9.2 query to export data to a .DBF format.  How do I get the .DBF file header in place (how do I write binary data)?  What is the field delimiter?
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Many products use the DBF file extension.  What are you wanting to do with the file?

Author

Commented:
Import into Excel spreadsheet.  Is pipe delimited sufficient or is the user just asking for DBF because he's used to using Access?
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Depending on the length of the columns it's pretty simple to create a delimited file:

Where you get into trouble is if the concatinated columns exceed 4000 characters.  If this might happen, just post back.  There are a few work-arounds.

an example sql*plus script:
------------------------------------
set pages 0
set feedback off
set linesize 32000
set trimspool on

spool somefile.txt
select col1 || '|' || col2 || '|' col3 from table where ...
spool off

Author

Commented:
I guess what I was trying to determine (poor phrasing of the question  I suppose) was what makes the DBF different, I know how to do a pipe-delimited sql statement but not how to export from Oracle to a DBF format.  I found the www.clicketyclick.dk/databases/xbas/format/dbf.html site that probably will answer these questions.  Thanks for your help.
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Sorry.  I mis-read your post a little.

I'm guess he is used to importing from Access into Excel.  I doubt you'll ever get the binary layout of an Access database and even if you could, it would be a HUGE amount of work to go through just to get data into Excel.

If you want to give it to them in Access, just create a linked table using ODBC.  Then you can create a new table from the linked table and give him the Access database.