Exforsys

H I D E

Home arrow Technical Training arrow Oracle 11g Tutorial

Table of Contents

 UTL_FILE
 UTL_FILE - Page 2
 UTL_FILE - Page 3
 UTL_FILE - Page 4
 UTL_FILE - Page 5

UTL_FILE Page - 3

Page 3 of 5
Author: Saurabh Gupta     Published on: 10th Aug 2011

UTL_FILE

PUT Procedure

Subprogram Name: PUT Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.PUT (
  2. file IN FILE_TYPE,
  3. buffer IN VARCHAR2)
Copyright exforsys.com


Description: The procedure writes a text in the file, which is under Write operation. Note that is has ability to just write a piece of text into the file, but fails to change the line. Actually, it writes the text to the file buffer, which can hold maximum of 32767 bytes.

PUT_LINE Procedure

Subprogram Name: PUT_LINE Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.PUT_LINE (
  2. file IN FILE_TYPE,
  3. buffer IN VARCHAR2,
  4. autoflush IN BOOLEAN DEFAULT FALSE)
Copyright exforsys.com


Description: The procedure achieves the same purpose as that by PUT procedure. Only difference is that it appends the text with the line terminator. As a pre-requisite, the file must be in Write mode.

PUT_LINE_NCHAR Procedure

Subprogram Name: PUT_LINE_NCHAR Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.PUT_LINE_NCHAR ( file IN FILE_TYPE,
  2. buffer IN NVARCHAR2)
Copyright exforsys.com


Description: The procedure is the analogous of PUT_LINE to support national character set.

PUT_NCHAR Procedure

Subprogram Name: PUT_NCHAR Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.PUT_NCHAR ( file IN FILE_TYPE,
  2. buffer IN NVARCHAR2)
Copyright exforsys.com


Description: The procedure is the analogous of PUT to support national character set.

PUTF Procedure

Subprogram Name: PUTF Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.PUTF (
  2. file IN FILE_TYPE,
  3. format IN VARCHAR2,
  4. [arg1 IN VARCHAR2 DEFAULT NULL,
  5. . . .
  6. arg5 IN VARCHAR2 DEFAULT NULL])
Copyright exforsys.com


Description: The procedure is a formatted form of PUT with many options like new line and substitution in main text to be written into the file. Argument 1 to 5 in the Syntax denotes the substitutable text in the main text. All occurrences of %s in the main text would be replaced by these texts in sequence. For example, first occurrence would be replaced by Argument 1, second with Argument 2 and so on. The main text ‘Format’ can also have new line character ‘n’.

PUTF_NCHAR Procedure

Subprogram Name: PUTF_NCHAR Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.PUTF_NCHAR (
  2. file IN FILE_TYPE,
  3. format IN NVARCHAR2,
  4. [arg1 IN NVARCHAR2 DEFAULT NULL,
  5. . . .
  6. arg5 IN NVARCHAR2 DEFAULT NULL])
Copyright exforsys.com


Description: The procedure is national character set supported version of PUTF.

PUT_RAW Function

Subprogram Name: PUTF_RAW Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.PUT_RAW (
  2. fid IN utl_file.file_type,
  3. fir IN RAW,
  4. autoflush IN BOOLEAN DEFAULT FALSE)
Copyright exforsys.com


Description: Like PUT function, it accepts RAW value and writes to the buffer, which in turn would be written into the file. If the ‘Auto flush’ is true, buffer content would be immediately written into the file and buffer would be flushed off.

FFLUSH Procedure

Subprogram Name: FFLUSH Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.FFLUSH (file IN FILE_TYPE)
Copyright exforsys.com


Description: The procedure forcefully writes the buffered data immediately into the file.

FCOPY Procedure

Subprogram Name: FCOPY Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.FCOPY (
  2. src_location IN VARCHAR2,
  3. src_filename IN VARCHAR2,
  4. dest_location IN VARCHAR2,
  5. dest_filename IN VARCHAR2,
  6. start_line IN BINARY_INTEGER DEFAULT 1,
  7. end_line IN BINARY_INTEGER DEFAULT NULL)
Copyright exforsys.com


Description: The UTL_FILE procedure copies a portion of content from one file (source) to another file (target). The portion of the content is specified using ‘Start Line’ and ‘End Line’ of the source file. The source file must be opened in Read mode, while the target file should be in Write mode.

Close

The section lists the UTL_FILE subprograms which are used to close a file.

FCLOSE Procedure

Subprogram Name: FCLOSE Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.FCLOSE (file IN OUT FILE_TYPE)
Copyright exforsys.com


Description: The procedure closes a file, which is currently held by the input file handler.

FCLOSE_ALL Procedure

Subprogram Name: FCLOSE_ALL Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.FCLOSE_ALL
Copyright exforsys.com


Description: The procedure is used to close all the file handlers of the current session.

Common tasks

The section lists the UTL_FILE subprograms which perform useful activity related to file operations.

FGETATTR Procedure

Subprogram Name: FGETATTR Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.FGETATTR(
  2. location IN VARCHAR2,
  3. filename IN VARCHAR2,
  4. fexists OUT BOOLEAN,
  5. file_length OUT NUMBER,
  6. blocksize OUT BINARY_INTEGER)
Copyright exforsys.com


Description: The procedure is used to retrieve the file statistics on the storage disk.

FGETPOS Function

Subprogram Name: FGETPOS Subprogram Type: Function Syntax:

Sample Code
  1. UTL_FILE.FGETPOS ( fileid IN file_type) RETURN PLS_INTEGER
Copyright exforsys.com


Description: The function returns the offset position in a file under operation.

FREMOVE Procedure

Subprogram Name: FREMOVE Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.FREMOVE (
  2. location IN VARCHAR2,
  3. filename IN VARCHAR2)
Copyright exforsys.com


Description: The procedure deletes the given file from the specified directory location.

FRENAME Procedure

Subprogram Name: FRENAME Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.FRENAME (
  2. location IN VARCHAR2,
  3. filename IN VARCHAR2,
  4. dest_dir IN VARCHAR2,
  5. dest_file IN VARCHAR2,
  6. overwrite IN BOOLEAN DEFAULT FALSE)
Copyright exforsys.com


Description: In UNIX, we have ‘MV’ command, whose ability to move the file contents between locations, is applied to rename an existing file. FRENAME is an analogous of MV command in oracle. It takes source file specification and destination file specification as inputs. Destination file specification is the new directory and name of the file.

‘Overwrite’ parameter allows overwriting of contents in a file, if the new file created by the program already exists in the location. By default, it is FALSE.

FSEEK Procedure

Subprogram Name: FSEEK Subprogram Type: Procedure Syntax:

Sample Code
  1. UTL_FILE.FSEEK (
  2. fid IN utl_file.file_type,
  3. absolute_offset IN PL_INTEGER DEFAULT NULL,
  4. relative_offset IN PLS_INTEGER DEFAULT NULL)
Copyright exforsys.com


Description: The procedure moves the file pointer within a file. It either moves to an absolute position (absolute offset) or moves from its current position in terms of bytes (relative offset).

Read Next: Manage Oracle dependencies


 

Oracle 11g Tutorial

  1. Oracle 11g Result Cache
  2. Compound Triggers in Oracle 11g
  3. Oracle 11g Invisible Index
  4. Oracle 11g Virtual Columns
  5. Oracle 11g Collections
  6. Oracle Functions and Procedures
  7. Oracle 11g Exception Handling
  8. Oracle 11g Read Only Tables
  9. Oracle 11g Subprogram Overloading
  10. Conditional compilation in Oracle PL/SQL
  11. Oracle Pragma
  12. UTL_FILE
  13. Manage Oracle dependencies
  14. Oracle 11g SecureFiles
  15. Oracle VPD implementation
  16. PL/Scope and PL/SQL Hierarchical Profiler
  17. Oracle Partitioning Overview
  18. Oracle Analytic Enhancements
  19. Oracle XML Storage
 

Comments

 

Ads

Exforsys Connect

Ads

Exforsys e-Newsletter

ebook