1. Oracle Utl_file Package
2. Pl Sql Utl File Example

UTLFILE subprograms are listed as below. FOPEN - Opens a file for input or output. FOPEN takes the following parameters: the File Location, Filename, OPENMODE and the MaxLinesize. FCLOSE - Closes a file. FCLOSEALL - Closes all open file handles. FCOPY - Copies a contiguous portion of a file to a newly created file. Takes the following parameters: srclocation, srcfilename, destlocation, destfilename, startline, and endline.

• 211 UTL_MAIL. The UTL_MAIL package is a utility. You must both install UTL_MAIL. You define the SMTP_OUT_SERVER parameter in the init.ora rdbms initialization file.
• Oracle 11g Access Control List for External Network Services. 1g Access Control List ( ACL ) for network packages like UTL. From Oracle 1.

Oracle Database - Enterprise Edition - Version 11.1.0.6 and laterHow to Verify the Installation of UTL_DBWS Package in Oracle Database 11g.

FFLUSH - Physically writes all pending output to a file. FGETATTR - Reads and returns the attributes of a disk file.

Returns the following items about the file: location, filename, fexists (a boolean), filelength (in bytes), and blocksize. The location must be either an existing directory on the server AND be in the utlfiledir parameter, or it may be a directory. FGETPOS - Returns the current relative offset position within a file, in bytes as a binaryinteger. FOPENNCHAR - Opens a file in Unicode for input or output.

FREMOVE - Deletes a disk file, assuming that you have sufficient privileges. Takes the following parameters: location and filename. FRENAME - Renames an existing file to a new name, similar to the UNIX mv function. FRENAME takes the following parameters: the srclocation, the srcfilename, the destlocation, the destfilename, and overwrite (a boolean). The overwrite parameter determines whether or not the file, if it already exists, will be overwritten. FSEEK - Adjusts the file pointer forward or backward within the file by the number of bytes specified. FSEEK takes the following parameters: the file, the absoluteoffset (a binaryinteger), and the relativeoffset (a binaryinteger).

Oracle Utl_file Package

GETLINE - Reads text from an open file. GETLINE takes the following parameters: the file (record), buffer (varchar2), and len (a binaryinteger).

Pl Sql Utl File Example

GETLINENCHAR - Reads text in Unicode from an open file. GETLINENCHAR takes the following parameters: the file (record), buffer (nvarchar2), and len (a binaryinteger). GETRAW - Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read. GETRAW takes the following parameters: file (record), buffer (raw), len (a binaryinteger). ISOPEN - Determines if a file handle refers to an open file.

NEWLINE - Writes one or more operating system-specific line terminators to a file. NEWLINE takes the following parameters: file (record), lines (a binaryinteger). PUT - Writes a string to a file. PUT takes the following parameters: file (record), buffer (a varchar2). PUTLINE - Writes a line to a file, and also appends an operating system-specific line terminator.

If a line was already written, it starts the line with CR/LF. This implies that the file, when being written into, does not end with CR/LF. In Oracle 9i the maximum line length that can be written is 32K. PUTLINE takes the following parameters: file (record), buffer (a varchar2). PUTLINENCHAR - Writes a Unicode line to a file. PUTLINE takes the following parameters: file (record), buffer (a nvarchar2), autoflush (a boolean).

PUTNCHAR - Writes a Unicode string to a file. PUT takes the following parameters: file (record), buffer (an nvarchar2). PUTF - A PUT procedure with formatting. PUTFNCHAR - A PUTNCHAR procedure with formatting, and writes a Unicode string to a file, with formatting. PUTRAW - Accepts as input a RAW data value and writes the value to the output buffer. SQL DECLARE 2 FileHandler UTLFILE.FILETYPE; 3 BEGIN 4 FileHandler:= UTLFILE.FOPEN(' TESTDIRR', 'TESTFILE.txt', 'W'); 5 UTLFILE.PUTF(FileHandler, 'Writing TO a file n'); 6 UTLFILE.FCLOSE(FileHandler); 7 EXCEPTION 8 WHEN UTLFILE.INVALIDPATH THEN 9 RaiseApplicationError(-20000, 'ERROR: Invalid PATH FOR file.'

); 10 END; 11 / DECLARE. ERROR at line 1: ORA-20000: ERROR: Invalid PATH FOR file. ORA-06512: at line 9 /.

We got Error Because TESTDIRR doesn't Exist./ SQL DECLARE 2 FileHandler UTLFILE.FILETYPE; 3 BEGIN 4 FileHandler:= UTLFILE.FOPEN(' TESTDIR', 'TESTFILE.txt', 'W'); 5 UTLFILE.PUTF(FileHandler, 'Writing TO a file n'); 6 UTLFILE.FCLOSE(FileHandler); 7 EXCEPTION 8 WHEN UTLFILE.INVALIDPATH THEN 9 RaiseApplicationError(-20000, 'ERROR: Invalid PATH FOR file.' ); 10 END; 11 / PL/SQL procedure successfully completed. Example 3: SQL DECLARE 2 Fhandle UtlFile.FileType; 3 Begin 4 Fhandle:= UtlFile.Fopen( 'TESTDIR','FILEDATA.txt','W'); 5 6 /.

TESTDIR: File Location FILEDATA.txt: File Name W: Open Mode: W- Write./ 7 8 UtlFile.Put(Fhandle, 'How Are You Doing Today' CHR(10)); 9 UtlFile.Put(Fhandle, 'I am Doing Good'); 10 UtlFile.Fclose(Fhandle); 11 Exception 12 When Others Then 13 DbmsOutput.PutLine('ERROR: ' SQLCODE ' - ' SQLERRM); 14 Raise; 15 END; 16 / PL/SQL procedure successfully completed. The directory specified must exist on the Oracle server when the script is run.

The ‘create directory’ command does not validate that the directory specified actually exists, nor does it create the directory on the server. It is the developer’s responsibility to specify a valid directory path. A typical mistake is to specify a.local. machine folder as the UTLFILE directory when the Oracle database server is on another machine – this will not work. The UTLFILE directory must exist on the.server.

machine.