advanced reference article about oracle UTL_FILE package – for the new oracle dba





a new oracle dba can refer the below oracle metalink article about advanced UTL_FILE concepts. you should have access to oracle metalink to access other documents mentioned in this article.

 

FAQ and Known Issues While Using UTL_FILE. [ID 730774.1]  


 

  Modified 04-FEB-2010     Type FAQ     Status PUBLISHED  
       

In this Document
  Purpose
  Questions and Answers
  References


Applies to:

PL/SQL – Version: 9.0.1.0 to 11.1.0.6
Information in this document applies to any platform.
Checked for Relevance on 03-FEB-2010

Purpose

This article contains FAQ and Known issues while using UTL_FILE.

Questions and Answers

With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.Foremost of these is the set of directory objects that have been granted to the user

In the past (before 10g release), accessible directories for the UTL_FILE functions were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is no longer recommended after the 10g release. Oracle recommends that you instead use the directory object feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools.  Please see the following note for further information, Note.196939.1 Using CREATE DIRECTORY Instead of UTL_FILE_DIR init.ora Parameter.

On UNIX systems, the owner of a file created by the FOPEN function is the owner of the shadow process running the instance. Normally, this owner is ORACLE. Files created using FOPEN are always writable and readable using the UTL_FILE subprograms. However, non-privileged users who need to read these files outside of PL/SQL may need access from a system administrator.

1)How to use UTL_FILE Package to Perform File I/O (UNIX) ?
Note 44307.1: Using the UTL_FILE Package to Perform File I/O (UNIX)

2)How To Set the Permission of the Files Created Using UTL_FILE?
Note.463312.1: How To Set the Permission of the Files Created Using UTL_FILE?
Note.74268.1 Using utl_file, how file permissions are determined, working sample

3)How To Save Table Data From an Oracle DB Into a User-Readable Text File ?
Note 443395.1: How To Save Table Data From an Oracle DB Into a User-Readable Text File (.txt Extension).

4)How to write CLOB data > 32K out to a file. ?
Note.358641.1:How to write CLOB data > 32K out to a file.

5)How To Write Out Multiple Clob Fields > 32k To A File in 4K chunks?
Note 358781.1: How To Write Out Multiple Clob Fields > 32k To A File in 4K chunks?

6)How To display the DBMS_OUTPUT From The Remote Procedure on Local Database ?
Note 453325.1: How To display the DBMS_OUTPUT From The Remote Procedure on Local Database.

7)How to use UTL_FILE to work on WINDOWS (Local drive and Network Share Drive) ?
Note 45172.1 : Running UTL_FILE on Windows NT

8) How to use UTL_FILE to work with different characterset ?
Note 227531.1  :  Character set conversion when using UTL_FILE

9) How to add more then one utl_file_dir entry
Note.202159.1 How to specify multiple utl_file_dir entries when using a server side parameter file (spfile)

10) Uses of UTL_FILE
Note 352685.1 How To Output Text From Pl/Sql Procedure Via A Job

Below are the list of Known issues :

1.Note 298700.1 : ORA-29283: Invalid file operation

2.Note 292961.1 : ORA-29280: Invalid Directory Path With UTL_FILE.FOPEN

3.Note 403205.1 : When Database Is Upgraded To 9.2.0.8.0 On HP-UX Platforms, SYS.UTL_FILE Becomes Invalid as the Script which Creates it, PRVTFILE.PLB, is of Zero Bytes (0b)

4.Note 458641.1 : UTL_FILE.FRENAME Fails With ORA-29283

5.Note 303828.1 : Invalid UTL_FILE after migrating/upgrading from 8.1.7.4 to 9.2.0.6 on AIX 5L

6.Note 470104.1 : Loss of data in OS file while try to append from two or more sessions using UTL_FILE package.

7. Note 358441.1 : What is The Maximum Number of Files That Can Be Opened Using UTL_FILE.FOPEN()
The maximum number of concurrent files that can be opened by utl_file is 50 files which was increased from 10 since 8.0.6.
Work around:
If the maximum open files are being hit, in the emergency cases only; add the command “UTL_FILE.FCLOSE_ALL;

8. Oracle throws exception when using UTL_FILE package on WINDOWS to access network share drive.
Note 329464.1 : Invalid Path Error When Accessing a File On The Remote Machine With Utl_File Package
Note 1034188.6: INVALID_OPERATION Exception from UTL_FILE when Writing to/from Network Drive
Solution : Start the Oracle service as a user who has the same permissions as SYSTEM, and also who has access to the shared directory.

9.Note 734105.1: Private Memory Leak Leads to ORA-4030 (KOH-KGHU SESSI,
PMUCALM COLL) Using UTL_FILE.FOPEN in PLSQL Loop
Solution : The issue is reproducible in 10.2.0.4 and 11.1.0.6. Apply Patch 7197637

10. Note 742387.1 : ORA-22288 From DBMS_LOB.FILEOPEN Using UNC Notation on Windows XP / 2003 but UTL_FILE Works
Solution : Upgrade the database to 9.2.0.8 or 10.2.0.2 or 10.1.0.5.0

11. Note.1063519.6 ‘ORA-29280 invalid directory path’ is returned when using spaces within the directory

12. Note.432598.1 Utl_file.Fopen Fails when Sqlcase is set to Upper
Solution : Set SQLPlus parameter sqlcase=mixed

13. Note.1026951.6 READ/WRITE Exceptions When Using UTL_FILE Due to Size Restrictions 

Known Bugs :

Bug 3354447 UTL_FILE.FGETATTR returns ORA-01426 for files larger than 2Gb
Component: PLSQL
Fixed Ver(s): 9206 10103 1020
Symptom(s):
– ORA-1426 is thrown when UTL_FILE.FGETATTR is used to get the size of a file over 2GB in size.
Available Workaround(s): None
References:
Note 3354447.8 Bug 3354447 – UTL_FILE.FGETATTR returns ORA-1426 for files larger than 2Gb

Bug 2968928 ORA-29280 from UTL_FILE in an INVOKERS RIGHTS function called from SQL
Component: PLSQL
Fixed Ver(s): 9205 1010
Symptom(s):
– UTL_FILE.FOPEN() may fail with an invalid directory path error(ORA-29280) when called from a function with
invoker rights which is in turn called from a SQL statement.
Available Workaround(s):
1. Disable direct invocation.
2. Instead of using directory object, use the old method of utl_file_dir=…
References:
Note 2968928.8 Support Description of Bug 2968928

Bug 2539128 Win: UTL_FILE.FSEEK does not work on Windows
Generic Platform: No, Windows only.
Component: PLSQL
Fixed Ver(s): 9205 1010
Symptom(s):
– UTL_FILE.FSEEK does not work on some platforms.
In particular this does not work on Windows platforms failing with INVALID OFFSET errors.
Available Workaround(s): None
References:
Note 2539128.8 Support Description of Bug 2539128

Bug 1429299 ORA-06510 / ORA-06512 in UTL_FILE.OPEN when connected to DB2
Fixed Ver(s): 8171 9010
Symptom(s):
– ORA-06510 ORA-06512 in UTL_FILE.OPEN, when connected over MTS and select DB2 table
Available Workaround(s):
1) Do not close the database link when in session

Bug 1347123 UTL_FILE.FOPEN allows multi-character file mode strings
Fixed Ver(s): 8174 9010
Symptom(s):
– UTL_FILE.FOPEN silently accepts multi-character mode strings.The file mode is truncated to a single character.
eg: FHANDLE := UTL_FILE.FOPEN(mydir.dir,’b134712o.pdf’,’WB’, 32767);
is allowed, even though there is no support for binary-mode operations.
Available Workaround(s):
1) Limit your mode strings to a single character.
References:
Note 168000.1 BLOB Columns are Empty in PDF

 

References

BUG:7197637 – MEMORY LEAK LEADING TO ORA-4030 (KOH-KGHU SESSI,PMUCALM COLL) USING UTL_FILE
NOTE:1026951.6 – READ/WRITE Exceptions When Using UTL_FILE Due to Size Restrictions
NOTE:1034188.6 – INVALID_OPERATION Exception from UTL_FILE when Writing to/from Network Drive on Windows
NOTE:1063519.6 – ‘ORA-29280: invalid directory path’ is returned when using spaces within the directory
NOTE:202159.1 – How to specify multiple utl_file_dir entries when using a server side parameter file (spfile).
NOTE:227531.1 – Character set conversion when using UTL_FILE
NOTE:292961.1 – ORA-29280: Invalid Directory Path With UTL_FILE.FOPEN
NOTE:298700.1 – ORA-29283: invalid file operation
NOTE:303828.1 – Invalid UTL_FILE after migrating/upgrading from 8.1.7.4 to 9.2.0.6 on AIX 5L
NOTE:329464.1 – Invalid Path Error When Accessing a File On The Remote Machine With Utl_File Package
NOTE:352685.1 – How To Output Text From Pl/Sql Procedure Via A Job
NOTE:358441.1 – What is The Maximum Number of Files That Can Be Opened Using UTL_FILE.FOPEN()
NOTE:358641.1 – How to Write CLOB Data > 32K out to a File?
NOTE:358781.1 – How To Write Out Multiple Clob Fields > 32k To A File in 4K chunks?
NOTE:403205.1 – When Database Is Upgraded To 9.2.0.8.0 On HP-UX Platforms, SYS.UTL_FILE Becomes Invalid As the Script Which Creates It – PRVTFILE.PLB Is Of Zero Bytes ( 0 b )
NOTE:432598.1 – Utl_file.Fopen Fails when Sqlcase is set to Upper
NOTE:44307.1 – Top Articles Using UTL_FILE Package to Perform File I/O (UNIX)
NOTE:443395.1 – How To Save Table Data From an Oracle DB Into a User-Readable Text File ( .txt Extension) .
NOTE:45172.1 – Running UTL_FILE on Windows NT
NOTE:453325.1 – How To display the DBMS_OUTPUT From The Remote Procedure on Local Database ?
NOTE:458641.1 – UTL_FILE.FRENAME Fails With ORA-29283
NOTE:463312.1 – How To Set the Permission of the Files Created Using UTL_FILE ?
NOTE:470104.1 – Loss of data in OS file while try to append from two or more sessions using UTL_FILE package.
NOTE:734105.1 – Private Memory Leak Leads to ORA-4030 (KOH-KGHU SESSI,PMUCALM COLL) Using UTL_FILE.FOPEN in PLSQL Loop

 Related


Products


  • Oracle Database Products > Oracle Database > Application Development > PL/SQL

Keywords


UTL_FILE

Errors


ORA-1426; ORA-22288; ORA-6512; ORA-29280; ORA-4030; ORA-6510; ORA-29283

 

Back to top

Rate this document 

Article Rating

Rate this document
Excellent
Good
Poor
 
Did this document help you?
Yes
No
Just browsing
 
How easy was it to find this document?
Very easy
Somewhat easy
Not easy

 

  Comments

 
Cancel    

 

 

Author: admin