- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Oracle PL/SQL Question
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-21-2000 06:48 AM
тАО10-21-2000 06:48 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-21-2000 09:06 AM
тАО10-21-2000 09:06 AM
Re: Oracle PL/SQL Question
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-23-2000 05:04 AM
тАО10-23-2000 05:04 AM
Re: Oracle PL/SQL Question
It is defined in the init file, but the script errors indicate that it isn't..Unless we misunderstand the errors.
Tommy
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-27-2000 06:50 AM
тАО10-27-2000 06:50 AM
Re: Oracle PL/SQL Question
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-27-2000 06:59 AM
тАО10-27-2000 06:59 AM
Re: Oracle PL/SQL Question
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-27-2000 07:01 AM
тАО10-27-2000 07:01 AM
Re: Oracle PL/SQL Question
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 <
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-27-2000 07:05 AM
тАО10-27-2000 07:05 AM
Re: Oracle PL/SQL Question
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-27-2000 10:57 AM
тАО10-27-2000 10:57 AM
Re: Oracle PL/SQL Question
Thanks,
Tommy
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-28-2000 03:01 AM
тАО10-28-2000 03:01 AM
SolutionWhat 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
sqlplus -s / > $OUTFILE <
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-02-2000 02:01 PM
тАО11-02-2000 02:01 PM
Re: Oracle PL/SQL Question
Have you done anything between Oracle to Informix via their "NET"?? products??
moving data directly from Oracle to Informix via SQL statements?
Tommy