[Chapter 12] 12.9 Cleaning Up Source and Target Chapter 12PLVio: Reading and Writing PL/SQL Source Code 12.9 Cleaning Up Source and TargetPLVio 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 SourceWhen 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 TargetWhen 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 TargetBefore 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.Special Notes on PLVioHere are some factors to consider when working with PLVio:The PLVio package comes in two flavors, depending on the version of the database you are using. The PLVio.sps and PLVio.spb files contain the PLVio package compatible with PL/SQL Release 2.2 and earlier. The PLVio23.spb file makes use of the UTL_FILE builtin package and can only be used with PL/SQL Release 2.3 (it is called in the plvins23.sql installation script).When the target is a database table, the put_line program will issue a commit by calling PLVcmt.increment_and_commit, as specified by the PLVcmt. commit_after procedure. If you do not want any commits to occur from within PLVio, call the commit_after program as follows:PLVcmt.commit_after (0); This command will turn off incremental commits, but it will still allow a PLVcmt.perform_commit to save changes. To turn off committing entirely, execute this command:PLVcmt.turn_off 12.8 Saving and Restoring Settings13. PLVfile: Reading and Writing Operating System Files Copyright (c) 2000 O'Reilly & Associates. All rights reserved.