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.