[Chapter 5] 5.11 PLVfile: Operating System I/O Manager

5.11 PLVfile: Operating System I/O Manager

The PLVfile (PL/Vision FILE) package manages operating system I/O by providing a layer of code around Oracle's builtin UTL_FILE package. See Chapter 13, PLVfile: Reading and Writing Operating System Files for details.

5.11.1 Package constants and exceptions

max_line_size CONSTANT INTEGER := 1000;: The maximum size of a line allowed to be read or written with PLVfile.
max_line VARCHAR2(1000);: I had to "hard code" the 1000 again in this declaration because you must supply a literal when you declare a length for a VARCHAR2 string. Predefined variable you can use to anchor declarations of local variables in your own programs that will hold the maximum length line allowed in PLVfile.
c_append CONSTANT VARCHAR2(1) := 'A';
c_read CONSTANT VARCHAR2(1) := 'R';
c_write CONSTANT VARCHAR2(1) := 'W';: The different types of file access allowed with the UTL_FILE builtin package (A = append to existing lines in file, R = read-only from file and W = write over existing contents of file).
c_unixdelim CONSTANT VARCHAR2(1) := '/';
c_dosdelim CONSTANT VARCHAR2(1) := '\';: Predefined operating system directory/path delimiters for UNIX and MS-DOS .

5.11.2 Trace PLVfile activity

PROCEDURE show;: Turns on the trace of PLVfile activity.
PROCEDURE noshow;: Turns off the trace of PLVfile activity (default setting).
FUNCTION showing RETURN BOOLEAN;: Returns TRUE if you are currently tracing PLVfile activity.

5.11.3 Setting the operating system delimiter

PROCEDURE set_delim (delim_in IN VARCHAR2);: Sets the string to be used as the operating system delimiter (the character that goes between the file location and the file name).
FUNCTION delim RETURN VARCHAR2;: Returns the current operating system delimiter.

5.11.4 Setting the default directory or location

PROCEDURE set_dir (dir_in IN VARCHAR2);: Sets the default directory for the file you are managing with PLVfile. If you specify the directory with set_dir , you will not have to provide it in each call to PLVfile programs.
FUNCTION dir RETURN VARCHAR2;: Returns the current default directory.

5.11.5 Creating files

FUNCTION fcreate
(loc_in IN VARCHAR2, file_in IN VARCHAR2, line_in IN VARCHAR2)
RETURN UTL_FILE.FILE_TYPE;: Specify file location and name separately, as well as the single line of text to place in the file. The fcreate procedure will create the file and return the handle to the file.
FUNCTION fcreate
(file_in IN VARCHAR2, line_in IN VARCHAR2 := NULL)
RETURN UTL_FILE.FILE_TYPE;: Create the file without explicitly providing the file location.
PROCEDURE fcreate
(loc_in IN VARCHAR2, file_in IN VARCHAR2, line_in IN VARCHAR2);: Create the file but do not return the handle to the file.
PROCEDURE fcreate
(file_in IN VARCHAR2, line_in IN VARCHAR2 := NULL);: Create the file without explicitly providing the file location and do not return the handle to the file.

5.11.6 Checking for file existence

FUNCTION fexists (loc_in IN VARCHAR2, file_in IN VARCHAR2)
RETURN BOOLEAN;: Provide location and name separately; function returns TRUE if PLVfile is able to open the file with read-only access.
FUNCTION fexists (file_in IN VARCHAR2) RETURN BOOLEAN;: Returns TRUE if PLVfile is able to open the specified file with read-only access.

5.11.7 Opening a file

PROCEDURE fopen
(loc_in IN VARCHAR2, file_in IN VARCHAR2, mode_in IN VARCHAR2);: Opens a file for the specified mode (location and name provided separately) and does not return the handle to the file.
PROCEDURE fopen
(file_in IN VARCHAR2, mode_in IN VARCHAR2 := c_append);: Opens a file for the specified mode and does not return the handle to the file.
FUNCTION fopen
(file_in IN VARCHAR2, mode_in IN VARCHAR2 := c_append)
RETURN UTL_FILE.FILE_TYPE;: Opens a file for the specified mode and returns the handle to the file.
FUNCTION fopen
(loc_in IN VARCHAR2, file_in IN VARCHAR2, mode_in IN VARCHAR2)
RETURN UTL_FILE.FILE_TYPE;: Opens a file for the specified mode (location and name provided separately) and returns the handle to the file.

5.11.8 Closing a file

PROCEDURE fclose (file_in IN UTL_FILE.FILE_TYPE);: Closes the specified file.
PROCEDURE fclose_all;: Closes all open files.

5.11.9 Reading from a file

PROCEDURE get_line
(file_in IN UTL_FILE.FILE_TYPE,
line_out IN OUT VARCHAR2,
eof_out OUT BOOLEAN);: Retrieves the next line from the specified file (by file handle). Returns a flag indicating whether the end of the file has been reached.
FUNCTION line (file_in IN VARCHAR2, line_num_in IN INTEGER)
RETURN VARCHAR2;: Returns the n th line from the specified file. This program opens, reads from, and closes the file.
FUNCTION infile
(loc_in IN VARCHAR2,
file_in IN VARCHAR2,
text_in IN VARCHAR2,
nth_in IN INTEGER := 1,
start_line_in IN INTEGER := 1,
end_line_in IN INTEGER := 0,
ignore_case_in IN BOOLEAN := TRUE)
RETURN INTEGER;: UTL_FILE-version of INSTR. Finds the n th appearance of a string (text_in ) within the specified range of lines.
FUNCTION infile
(file_in IN VARCHAR2,
text_in IN VARCHAR2,
nth_in IN INTEGER := 1,
start_line_in IN INTEGER := 1,
end_line_in IN INTEGER := 0,
ignore_case_in IN BOOLEAN := TRUE)
RETURN INTEGER;: UTL_FILE-version of INSTR. Finds the n th appearance of a string (text_in ) within the specified range of lines. In this version you do not have to provide the location of the file separately from the name.

5.11.10 Writing to a file

PROCEDURE put_line
(file_in IN UTL_FILE.FILE_TYPE,line_in IN VARCHAR2);: Adds a line to the end of the specified file (by file handle). This file must already be opened for write or append access.
PROCEDURE append_line (file_in IN VARCHAR2, line_in IN VARCHAR2);: Add a line to the end of the specified file. This program opens the file for append access, writes to the file using put_line , and then closes the file.

5.11.11 Copying a file

PROCEDURE fcopy
(ofile_in IN VARCHAR2, nfile_in IN VARCHAR2,
start_in IN INTEGER := 1, end_in IN INTEGER := NULL);: Copies the contents of the old file (ofile_in ) to the new file (nfile_in ) for all lines within the specified range.
PROCEDURE file2pstab
(file_in IN VARCHAR2,
table_inout IN OUT PLVtab.vc2000_table,
rows_out OUT INTEGER);: Copies the contents of the file to the PL/SQL table.
PROCEDURE file2list (file_in IN VARCHAR2, list_in IN VARCHAR2);: Copies the contents of the file to the PLVlst list specified by the list name.
PROCEDURE pstab2file
(table_in IN PLVtab.vc2000_table,
rows_in IN INTEGER,
file_in IN VARCHAR2,
mode_in IN VARCHAR2 := c_write);: Copies the contents of the PL/SQL table to a file. You can open the file in either write mode or append mode (in which case the rows are added to the current contents of the file).

5.11.12 Displaying the contents of a file

PROCEDURE display
(file_in IN UTL_FILE.FILE_TYPE,
header_in IN VARCHAR2 := NULL,
start_in IN INTEGER := 1,
end_in IN INTEGER := NULL);: Displays the contents of the file (specified by file handle) using the p.l procedure. This version of display assumes that the file has been opened.
PROCEDURE display
(file_in IN VARCHAR2,
header_in IN VARCHAR2 := NULL,
start_in IN INTEGER := 1,
end_in IN INTEGER := NULL);: Displays the contents of the file (specified by file name using the p.l procedure. This version of display assumes that the file must be opened before reading the contents.

5.11.13 Miscellaneous operations

PROCEDURE parse_name
(file_in IN VARCHAR2, loc_out IN OUT VARCHAR2,
name_out IN OUT VARCHAR2);: Parses a file specification (directory, name, and extension) into two separate strings: the location or directory and the file name itself.