Oracle PL/SQL Question

[Tommy Brown]

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

[John Palmer]

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]

Thanks, John
It is defined in the init file, but the script errors indicate that it isn't..Unless we misunderstand the errors.
Tommy

[Steve Slade]

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.

[Tommy Brown]

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?

[Steve Slade]

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 <

[Steve Slade]

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.

[Tommy Brown]

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

[Steve Slade · Accepted solution]

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 <

[Tommy Brown]

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