cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle PL/SQL Question

SOLVED
Go to solution
Tommy Brown
Respected Contributor

Oracle PL/SQL Question

We are having problems using the UTL_FILE in a PL/SQL script.
Error messages indicate it must be defined.. We think we have defined it .
What we are trying to accomplish is to unload the table to a unix text file.. The table is about 1.5 Million rows.. Anyone have any Ideas or a sample script using UTL_FILE
I have attached the script we are trying to use with the errors.
Thanks,
Tommy Brown
I may be slow, but I get there !
9 REPLIES
John Palmer
Honored Contributor

Re: Oracle PL/SQL Question

Hi Tommy,

I've never used this facility but I do know that you have to define the UNIX directory where files will be written with the 'utl_file_dir' Oracle database init file parameter. Have you defined it?

Regards,
John
Tommy Brown
Respected Contributor

Re: Oracle PL/SQL Question

Thanks, John
It is defined in the init file, but the script errors indicate that it isn't..Unless we misunderstand the errors.
Tommy
I may be slow, but I get there !
Steve Slade
Frequent Advisor

Re: Oracle PL/SQL Question

UTL File is bit of a strange beastie, as Oracle is not very verbse about configuration problems with it.

Firstly you have to define the directories that the files are in. The format is like the following, with one entry per directory :

UTL_FILE_DIR = /u01/oradata/admin/housekeep/log/
UTL_FILE_DIR = /u02/mydata/admin/log/

Secondly, have to ensure that Oracle has access to these directories and the actual files.

You usually get an error message if you miss step 1, however, Oracle is usually pretty quiet if you forget the second step.

We use this facility in our application, and had hours of endless 'fun?' when we were setting it up.

If at first you do not succeed. Destroy all evidence that you even attempted.
Tommy Brown
Respected Contributor

Re: Oracle PL/SQL Question

we have since learned a little regarding the UTL...However, what we are now experiencing is problems trying to grant privileges to a user.. We believe there may be a bug on 8.05 concerning grant privileges on packages.. Steve, what version of Oracle are you running?
I may be slow, but I get there !
Steve Slade
Frequent Advisor

Re: Oracle PL/SQL Question

Sorry Tommy,

Just re-read your question. If you want to unload your table to a text file - do not use UTL_FILE. Believe it or not the quickest method (other than maybe PRO C++), is to use sqlplus and/or a spool file. I worked on a project with similar requirements, and they were recommended this by Oracle.

Something along the lines of the following in a script will work ...

sqlplus -s / > $OUTFILE <
If at first you do not succeed. Destroy all evidence that you even attempted.
Steve Slade
Frequent Advisor

Re: Oracle PL/SQL Question

Tommy,

I have found no differences between UTL_FILE on Oracle 7 or Oracle 8.

Although if you are using Oracle 8.0.5, you will find a number of PL/SQL bugs. Oracle have released a patchset to take you to Oracle 8.0.5.2.2, which will cure most of these. Any other bugs in Oracle 8.0.5, Oracle recommend 8.0.6, as they are not supplying any more patchsets for 8.0.5.
If at first you do not succeed. Destroy all evidence that you even attempted.
Tommy Brown
Respected Contributor

Re: Oracle PL/SQL Question

Steve, your are getting warmer.. I am not sure about the spoolfile.. Our database is on HPUX. Do you mean spoolfile as in WORKSHEET??? or outputting to a text file on the same HP server as the database??
Thanks,
Tommy
I may be slow, but I get there !
Steve Slade
Frequent Advisor
Solution

Re: Oracle PL/SQL Question

Tommy,

What I was trying to say was that if you want to simply export all of the data from a large table into a text file (comma delimited, for example), then do not use PL/SQL as it is very slow. The quickest option, without much re-coding is to use SQLPLUS - the default tool for accessing the database on your server. If you have not used this facility before - if maybe your are using SQL Worksheet, or TOAD, or some other PC based tool to access your database - its just a simple reporting tool. However, you need to use the version on the server and not on your PC.

You can run sqlplus directly, or you could add it to a script that you might want scheduled via cron. Either way, you simply type in you SQL query and the results are displayed on the screen. You can, however, use other options to make the output go to just a text file, and also set the pagesize etc, (these are the options I had above). To make the output go to a spool file (a simple text file). You just type spool . Where is your text file. From then onwards everything that is returned from sqlplus is also writen to the text file. You turn this facility off by typing spool off;. Another way of achieving the same effect from within a script is to call sqlplus and direct its output to a text file:

sqlplus -s / > $OUTFILE <
If at first you do not succeed. Destroy all evidence that you even attempted.
Tommy Brown
Respected Contributor

Re: Oracle PL/SQL Question

Thanks, Steve.. Your input has been a great help.. I had heard of SQL*Plus, but we never used it in class and had therefore forgotten about it..
Have you done anything between Oracle to Informix via their "NET"?? products??
moving data directly from Oracle to Informix via SQL statements?
Tommy
I may be slow, but I get there !