#

| Chapter 13 PLVfile: Reading and Writing Operating System Files |

## 13.7 Copying File Contents

PLVfile offers several different programs to copy the contents of a file. You can copy from one file to another, from a file to a PL/SQL table, and from a PL/SQL table to a file. These programs are explained below.

### 13.7.1 Copying File to File

You can copy the entire contents of one file to another file with a single program call via the `fcopy` procedure; its header is shown here:

>     PROCEDURE fcopy
>        (ofile_in IN VARCHAR2,
>         nfile_in IN VARCHAR2,
>         start_in IN INTEGER := 1,
>         end_in IN INTEGER := NULL);

The `ofile_in` string is the name of the "original" file. The `nfile_in` string is the name of the "new" file. You can also specify which lines from the original file you want to copy. The `start_in` value is the number of the first line to copy. The `end_in` argument is the last line of the file to copy. If the `end_in` argument is NULL, then all lines from the `start_in` line to the end of the file are copied.

### 13.7.2 Copying File to PL/SQL Table

You can copy the contents of a file directly into a PL/SQL table with the `file2pstab` program. The header for the procedure is:

>     PROCEDURE file2pstab
>        (file_in IN VARCHAR2,
>         table_inout IN OUT PLVtab.vc2000_table,
>         rows_out OUT INTEGER);

You specify the name of the file and provide the procedure with a PL/SQL table declared using the PLVtab package. Each line of the file is then copied into consecutive rows in the PL/SQL table. The `rows_out` argument contains the total number of rows set in the table. If the file is empty, the `rows_out` argument will be set to 0.

Once you have moved the file contents to a PL/SQL table, you can access the information in any PL/SQL program.

### 13.7.3 Copying File to List

You can copy the contents of a file directly into a PL/Vision list, implemented using the PLVlst package. The header for the procedure is:

>     PROCEDURE file2list (file_in IN VARCHAR2, list_in IN VARCHAR);

where `file_in` is the file name and `list_in` is the name of the PL/Vision list. This list is implemented in a PL/SQL table.

If the list does not exist, it is initialized by `file2list`. If the list already exists, the lines from the file are appended to the end of the list.

The combination of `PLVfile.file2list` and the full set of list management programs in PLVlst offers you a powerful means of storing lists of information in operating system files and then integrating that information into PL/SQL-based applications.

### 13.7.4 Copying PL/SQL Table to File

You can perform a "bulk" write to a file with the `pstab2file` procedure. This program transfers the contents of a VARCHAR2 PL/SQL table into the specified file. The header for this procedure is:

>     PROCEDURE pstab2file
>        (table_in IN PLVtab.vc2000_table,
>         rows_in IN INTEGER,
>         file_in IN VARCHAR2,
>         mode_in IN VARCHAR2 := c_write);

where the first argument is `table_in`, the PL/SQL table (defined using the PLVtab package). The `rows_in` parameter provides the number of rows in the table (`pstab2file` assumes that the PL/SQL table is populated sequentially from row 1). The string `file_in` provides the file name. Finally, you can also provide the file operation mode in the `mode_in` argument. The default value for `mode_in` is `PLVfile.c_write`, which means that any existing roles in the specified file will be replaced by the contents of the PL/SQL table.

You can also request that the PL/SQL table be transferred in "append mode." In this case, all PL/SQL table data will be appended to the end of the file. This approach is shown below:

>     PLVfile.pstab2file
>        (my_table, rows_in_tab, 'temp.sql', PLVfile.c_append);

If you try to execute `pstab2file` with the read-only mode, `PLVfile.c_read`, you will receive an error.

If `pstab2file` encounters any undefined rows between 1 and `rows_in` - 1, it will trap the NO_DATA_FOUND exception and continue past that error. It will, as a result, transfer as many rows as possible from the PL/SQL table.

The following script (stored in the file `dumpprog.sql`) shows how to use the `pstab2file` to generate a source code file for the specified program.

>     BEGIN
>        /* Set the current object from user. */
>        PLVobj.setcurr ('&1');

>        /* Read from ALL_SOURCE, write to PL/SQL table. */
>        PLVio.asrc;
>        PLVio.settrg (PLV.pstab);

>        /* Copy the program source to the PL/SQL table. */
>        PLVio.src2trg;

>        /* Write the contents of the PL/SQL table to a file. */
>        PLVfile.pstab2file
>           (PLVio.target_table, PLVio.target_row, '&2');
>     END;
>     /

Here is the command you would execute in SQL*Plus to copy the stored source code of the body of the PLVvu package to a file named `PLVvu.spp`:

>     SQL> @dumpprog b:PLVvu PLVvu.spp

13.6 Writing to a File |  | 13.8 Displaying File Contents

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