Solved

URGENT How to export with PL/SQL, data from Oracle tables into DBF files

Posted on 2006-06-16
18
3,744 Views
Last Modified: 2007-12-19
I have the following problem:
I want to export, through PL/SQL, data from a Oracle table directly to a DBF file.

I think that is beeing done with UTL_FILE function, but i don't know how to write the DBF file header.

I want that export to happen at the user request from a form.
The file then will be e-mailed or put on a CD.

Another question: UTL_FILE put the file on the server (i'm using Oracle Database 9.2 and 9iAS 9.2). How to transfer automatically the file to the user's HDD?

2 questions, both hard, i think.

Please help if u have any informations, links, etc.
0
Comment
Question by:mitralier
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 3
  • +2
18 Comments
 
LVL 16

Accepted Solution

by:
MohanKNair earned 250 total points
ID: 16918854
It is possible to read a dbase file using UTL_FILE and PL/SQL package. The package is available in the link below. The package can be modified to write in dbase format.
http://asktom.oracle.com/pls/ask/f?p=4950:8:3567412298036405208::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:711825134415

Xbase File Format Description is available in this link
http://www.clicketyclick.dk/databases/xbase/format/
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16920194
What kind of server do you have?  Unix or Windows?  Either way, the answer isn't simple.  How does the user access the database?  We have a web-based system that we access data through, and that system is responsible for FTPing files directly to users.  The database can't do that.

In your case, you can trigger an FTP from Oracle:

http://www.oracle-base.com/articles/9i/FTPFromPLSQL9i.php
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 250 total points
ID: 16941186
The normal way to transfer data between different types of DBs are the flat files.
In Oracle you can do this either using UTL_FILE package
or SPOOL and SELECT:
SQL>spool c:\some_file.txt
SQL>set heading 0
SQL> select name || ','||address from spme table;
SQL>spool off
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 48

Expert Comment

by:schwertner
ID: 16941190
Second question; invoking ftp.
0
 
LVL 1

Author Comment

by:mitralier
ID: 16948956
OK.
I've done, so far, the following
Using WebUtil for Oracle Forms i'v done a csv file directly from the database, that is saved directly on the client computer.

That's a problem solved for the tranfer of the file.

The remaining problem is how to write the DBF file, because it's a binary file and the header must be extremely exact.
If someone could help at this.............

At the end i will split the points, don't know exactly how :)

P.S. The FTP was not an option from the start, because of the security of the network. WebUtil it's the best for this, i think.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 16948983
Now using Excel create csv file (that is really character, not binary file) and try to read and insert the records into XBase system.
0
 
LVL 1

Author Comment

by:mitralier
ID: 16950024
That's my problem schwertner, i don't know how to write in a dBASE system file.
I need a procedure that write the header and another procedure that write the actual data. I don't know what to use in any of them. The problem is that i will have to use WebUtil to build those procedures, because the file is written in Oracle Forms, not on a stored procedure.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 16950848
It is a bad idea to try 'to write in a dBASE system file'!
Every system has tools to read flat files and to put the records into the dbf files.
I personally did in 1997 the reverse task: In Clipper 5 I created flat file with Oracle "Insert" statements.
May be Dbase has its insert statement. If so it is easy to format the Oracle output to fit
the Dbase input.

Another option:
http://www.sharewareplaza.com/Data-Loader-download_26345.html

Description:
Data Loader is a simple yet powerful tool to import and export Data between many common formats. Current Data Loader Ver. 1.0 supports Oracle, MS Access, FoxPro, DBF's and Delimited or Flat Files. You can use Data Loader to transfer data from one database format to another database format. While transferring you can filter columns and specify WHERE conditions.
It supports Long , Picture Data types. In a jiffy you can copy data without any coding.
Supported O/s: Win 2003, Win 2000, XP, Win ME,Win98 &Win95
0
 
LVL 1

Author Comment

by:mitralier
ID: 16951570
Ok, i think it's a misundersting here.

The actual problem is:

I have a form that call a stored procedure.
The stored procedure return the array with the data of the file.
The form writes the array into a .CSV file. For writing the file i use "client_text_io.putf" from WebUtil. That command create and write into a file on the client computer.

Now, i want that .CSV file to be a .DBF file, and after the export from my form to import it in FoxPro.
I don't know how to write the header of the DBF file and how to write the actual data. In CSV the data are comma delimited. In DBF.......HOW?


0
 
LVL 1

Author Comment

by:mitralier
ID: 16951594
Oh, and i can't use a tool, the EXPORT it's gonna be integrated into an application, it's not for a one time use.
The user  will pick a table and then export it. I have that functionality, but the file is CSV. I want the file to be .DBF now.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16967029
Instead of CSV file create a tab delimited file. This is accessible by Microsoft tools like excel.  
The comma character CSV file can be converted to TAB character by using another PL/SQL code.
0
 
LVL 1

Author Comment

by:mitralier
ID: 16982498
I need a binary file, not a character file. Not tab delimited, comma delimited or any kind of delimited.
And i don't want to open it with Excel.
I want to open that file directly with FoxPro, after the export.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 16983908
There are such convertor programs, but most of them are paid.
Search using Google ...
0
 
LVL 1

Author Comment

by:mitralier
ID: 16999107
Schwertner, i want to build such a program in PL/SQL and Forms, not using one.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 16999794
I see and understand. This is what I found:


How to Create a DBase File from Reports

Report Builder supports the following destination formats (DESFORMAT):
PDF, HTML, HTMLCSS, HTMLCSSIE, RTF, XML, DELIMITED. So there is no direct way
to create dBase file from Reports.

It is however possible to create DELIMITED report output and then
import/convert this to dBase. MS Excel can for example open delimited file and
then save the data as a *.DBF (dBase) file. This process (conversion from
delimited to *.dbf using MS Excel) can be automated using an OLE2 package.

Another method is to use the EXEC_SQL package to connect to ODBC data sources
(including dBase file) via the Open Client Adapter (OCA), which is supplied
with Developer. One can then programmatically manipulate that data source using
regular SQL statements (insert, update, delete, select). But Reports cannot
write it's output to such a datasource directly.



How to Obtain a Merant/Data Direct dBASE Driver for OFSA Import/Export


Importing and Exporting IDs

You can use import/export functionality of OFSA to:

  Transport IDs from one FDM database to another
  Maintain external backup copies of key IDs
  Load data into FDM from an external database (.DBF) source

OFSA provides two methods of importing and exporting IDs:
  Individual ID import/export
  Dependent ID import/export

Individual ID Import/Export

IDs that do not contain other embedded IDs, such as Leaf Characteristics ID and
Transfer Pricing ID, are imported and exported using the Individual ID method.
These IDs are exported into a dBase file with the.DBF extension and can be
imported only from a dBase file with the .DBF extension.


For OFSA 4.5.3 and Higher Versions:
===================================
Visit the following web site to purchase or download a trial version of the 32-
bit Data Direct dBase driver:

http://www.datadirect-technologies.com/

Go to:
-- "Download database drivers" (left side under Fast Access)
-- "DataDirect Connect for ODBC Windows"
-- Fill out form, selecting the following:
   --> Client workstation
   --> Under "Flat" files --> "dBASE"

After agreeing to the license agreement and hitting submit follow the download
instructions for "DataDirect Connect for ODBC 4.2 Windows Download".

Download the zip file for "Connect for ODBC Software for Windows, Windows
NT/2000/XP/Me/98 - Standard Pack" and double-click on the setup.exe to run
the install Wizard.  Choose to install a Desktop Single Driver.  Select the
dBASE driver.

For instructions on configuring the driver, please see  the following:
How to Setup 32-bit Merant/Data Direct dBASE Driver for Import and Export Functionality

For OFSA 4.5.2x and Lower Versions:
===================================
Use the 16-bit Merant/Data Direct Dbase Driver version 2.5.3 FA-dbase IV to
import and export OFSA IDs.

The 16-bit Merant/Data Direct Dbase Driver is no longer available for purchase.

If you already own the 16-bit Dbase driver, contact Data Direct to renew or add
more licenses.

To obtain a copy of the 16-bit Dbase driver software, a third-party vendor must
be located.  Look for vendors selling programmers and developers software and
tools and inquire about the 16-bit Merant dbase IV driver for Windows 3.x.

Note: The Merant driver was formerly known as the Intersolv driver.  It is now
called the Data Direct driver.

0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16999852
See the link below. procedure dump_table in the package can be used to dump a file to disk in DBASE file format.

http://asktom.oracle.com/pls/ask/f?p=4950:8:15796147866611609094::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6379798216275#8651991032899



0
 
LVL 1

Expert Comment

by:Computer101
ID: 20294463
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question