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