#

| Chapter 12 PLVio: Reading and Writing PL/SQL Source Code |

## 12.9 Cleaning Up Source and Target

PLVio provides several programs so that you can clean up after yourself when using the package. These programs are described below.

### 12.9.1 Closing the Source

When you are done reading from the source repository, you should close it. The header for the `closesrc` procedure is:

>     PROCEDURE closesrc;

If the source is a database table, `closesrc` closes the dynamic SQL cursor. If the source is a file, the procedure closes the file. For a string or PL/SQL table source, no action is taken.

It is extremely important that you close your source; otherwise, a cursor or file will remain open for the duration of your session. This could lead to errors or unnecessary memory utilization.

The `closesrc` program will also automatically restore the PLVio settings if they were saved (i.e., if `PLVio.saving_src` returns TRUE).

### 12.9.2 Closing the Target

When you are done writing to the target repository, you should close it. The header for the `closetrg` procedure is:

>     PROCEDURE closetrg;

If the target is a database table, then `closetrg` calls `PLVcmt.perform_commit` to save your writes to the target (you can disable the commit with a call to `PLVcmt.turn_off`). If the source is a file, the procedure closes the file. For a string or PL/SQL table source, no action is taken.

When your target is a database table or a file, it is extremely important that you close your target. If you skip this step for a file, for example, that file might remain open for the duration of your session. You could also have outstanding transactions (the inserts to the target table) which are wiped out by a subsequent and perhaps unrelated rollback. This could lead to errors or unnecessary memory utilization.

The `closetrg` program will also automatically restore the PLVio settings if they were saved.

### 12.9.3 Clearing the Target

Before you write to a target repository, you may want to make sure that it is empty. The `clrtrg` procedure performs this action; its header is shown below:

>     PROCEDURE clrtrg
>        (program_name_in IN VARCHAR2 := NULL,
>         program_type_in IN VARCHAR2 := NULL);

The two arguments provide the name and type of program to be removed from the target source repository. These arguments are used only when the target is a database table. If the supplied values are NULL (the default), then the table identified in the call to `settrg` will be truncated using `PLVdyn`.

If you do provide a name and/or type, `clrtrg` uses those values to construct a WHERE clause so that only the specified program and type will be removed from the database table.

Remember that the default target database table is structured to hold the source code for one or more programs (it looks just like USER_SOURCE).

Suppose that I have called `settrg` as follows:

>     PLVio.settrg (PLV.pstab, 'new_source');

This means that I will be writing my text out to a table with this structure:

>     SQL> desc new_source
>      Name       Null?    Type
>      ---------- -------- --------------
>      NAME       NOT NULL VARCHAR2(30)
>      TYPE                VARCHAR2(12)
>      LINE       NOT NULL NUMBER
>      TEXT                VARCHAR2(2000)

This first call to `clrtrg`, then, will remove all records from the `new_source` table:

>     PLVio.clrtrg;

This next call to `clrtrg` will remove all package bodies stored in the table:

>     PLVio.clrtrg (program_type_in => 'PACKAGE BODY');

And this last call to `clrtrg` will remove the code for the `calc_totals` procedure:

>     PLVio.clrtrg ('calc_totals', 'procedure');

Currently, `clrtrg` only operates on database table targets.

12.8 Saving and Restoring Settings |  | 13. PLVfile: Reading and Writing Operating System Files

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