4.4.1 Special Handling for PLVdyn

As I explain in Chapter 19, PLVdyn and PLVfk: Dynamic SQL and PL/SQL, you may want to install a copy of PLVdyn in the account of each developer who will be using this functionality. This step will avoid many potential headaches. If you are going to do this, you should take the following steps after installing PL/Vision and creating all synonyms:

1. Connect to the account in which you wish to install a private copy of PLVdyn. Let's call it the DEV account.

2. Drop the private synonym for PLVdyn in your user account with the following command.

   SQL> drop synonym plvdyn;

   If you have created public synonyms, you can skip this step.

3. Install the PLVdyn package in the DEV account. Run these two scripts:

   SQL> @plvdyn.sps
   SQL> @plvdyn.spb

PLVdyn is now installed in and owned by the DEV account. You can also follow these same steps to move the PLVdyn1 package to a local account.

4.4.2 A PL/Vision Initialization Script for SQL*Plus

If you plan to use PL/Vision from within SQL*Plus, you will want to consider creating a login.sql (or modifying your current file) that sets up your session from a PL/Vision perspective. PL/Vision provides two different scripts for your use: the login.sql and login23.sql files. Use the login.sql script if you're using PL/SQL Release 2.2 or earlier. Use the login23.sql script if you're using PL/SQL Release 2.3 and beyond.

Here are the contents of login.sql:

set feedback off
exec plvgen.set_author ('Sam I Am');
@ssoo
set feedback on

where ssoo.sql is a script that enables output from the DBMS_OUTPUT package. The call to PLVgen.set_author defines the default author used in the headers of generated PL/SQL program units. Feel free to change the string passed to set_author.

The contents of login23.sql are almost the same:

set feedback off
exec plvgen.set_author ('Sam I Am');
exec plvfile.set_dir ('c:/plv');
@ssoo
set feedback on

The PLVfile.set_dir program sets the default directory for file I/O. You will probably want to change the string passed to set_dir, but it is very useful to set some value on startup of your SQL*Plus session.

4.4.3 Converting Scripts to PL/SQL Programs

Since SQL*Plus is a very common interactive execution platform for PL/SQL code, I make use of it throughout the book and in scripts provided on the disk. If you do not use SQL*Plus, it is generally very easy to convert SQL*Plus scripts to PL/SQL procedures. These procedures can then be executed in your environment to achieve the same effect as the scripts. Consider the following script (found in the file dumpemp.sql in the use subdirectory on the companion disk):

BEGIN
   PLVio.settrg (PLV.pstab);
   FOR emp_rec IN 
       (SELECT ename FROM emp WHERE deptno = &1)
   LOOP
      PLVio.put_line (emp_rec.ename);
   END LOOP;
   PLVio.disptrg;
END;
/

This script uses PLVio to display all of the employee names in the specified department. The &1 in the fourth line is a substitution parameter in SQL*Plus. To make this script work in PL/SQL, you can transform the &1 into an actual PL/SQL parameter and wrap the script inside a procedure header as follows:

PROCEDURE dumpemp (dept_in IN emp.deptno%TYPE)
IS
BEGIN
   PLVio.settrg (PLV.pstab);
   FOR emp_rec IN 
       (SELECT ename FROM emp WHERE deptno = dept_in)
   LOOP
      PLVio.put_line (emp_rec.ename);
   END LOOP;
   PLVio.disptrg;
END;

4.4.4 A Warning About Partial Installation

Do not attempt to install and use just a portion of PL/Vision. Why might you even consider such a thing? You might, for example, only want to use a single package, such as PLVvu to view your compile errors more effectively. You might balk at having to install all of those PL/Vision packages. However, almost every package in PL/Vision relies on many different PL/Vision packages in order to provide you with the highest possible level of functionality.

As a result, you must install all packages, following the directions provided in this chapter, whenever you want to use any portion of the product.

4.4.5 Uninstalling PL/Vision

To uninstall PL/Vision, you need to drop all synonyms, drop PL/Vision tables, and remove the packages. You can use PL/Vision to perform some of these steps.

To remove all the PL/Vision tables from an account, connect to that account in SQL*Plus and issue this command:

SQL> exec PLVdyn.drop_object ('table', 'PLV%');

All tables beginning with "PLV" will be dropped. This will take care of PL/Vision tables. You should, of course, ensure that there are no other tables that have this same naming pattern before you execute this command.

You can also execute the following script to remove all PL/Vision tables:

SQL> @plvtrem

To remove all the public synonyms for PL/Vision packages, connect to the PLV account in SQL*Plus (or a different DBA account if PLV is not a DBA account) and issue this command:

SQL> @plvdpsyn <connect_string>

The connect string is described in the section called "Installing PL/Vision."

To drop private synonyms for each of the PL/Vision packages, execute the plvdsyn.sql script from the account that has the private synonyms as follows:

SQL> @plvdsyn 

You are now ready to remove the packages that make up PL/Vision. To do this, connect to the PLV account and issue the following command:

SQL> @plvprem

All packages and standalone programs will then be dropped.