#

| Chapter 5 PL/Vision Package Specifications |

## 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 nth 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 nth 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 nth 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.

5.10 PLVexc: Exception Handling |  | 5.12 PLVfk: Foreign Key Interface

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.