12.4.1 Viewing the Current Source SELECT

First, use the srcselect function to retrieve the current structure of the SELECT statement for the source repository. In the following example, I use p.l to display the current SELECT.

SQL> exec p.l(PLVio.srcselect);
SELECT text, line  FROM user_source WHERE instr (text, 'RAISE') > 0 AND
name = 'PLVEXC' ORDER BY line

This string is an example of a SELECT in which the WHERE clause was substituted completely by a call to set_srcwhere. The following session in SQL*Plus sets the source to the ALL_SOURCE view. The srcselect function returns the default (and more normal) kind of SELECT built and executed by PLVio.

SQL> exec PLVio.asrc
SQL> exec p.l(PLVio.srcselect);
SELECT text, line  FROM all_source WHERE name = :name AND type = :type
 AND owner = :owner ORDER BY line

12.4.2 Changing the WHERE Clause

To modify directly the WHERE clause of the SELECT statement, you will call the set_srcwhere procedure, whose header is:

   PROCEDURE set_srcwhere (where_in IN VARCHAR2);

This procedure modifies the WHERE clause according to the following rules:

1. If the string starts with AND, then the string is simply concatenated to the current WHERE clause.

2. If the string starts with WHERE, then the entire current WHERE clause is replaced with the string provided by the user.

3. In all other cases, the core part of the WHERE clause (containing the bind variables for PLVobj.currname and PLVobj.currtype) is preserved, but any other additional elements are replaced by the specified string.

A few examples will demonstrate this procedure's impact. In each case, I initialize the SELECT statement with a call to PLVio.asrc so that the select_stg contains this information:

SELECT text, line  
  FROM all_source
 WHERE name = :name 
   AND type = :type
   AND owner = :owner
 ORDER BY line

Let's see what happens when I use set_srcselect to change the WHERE clause:

1. Add a clause to request that only lines 1 through 5 are read from ALL_SOURCE:

   PLVio.set_srcselect ('AND line BETWEEN 1 AND 5');

   The srcselect now looks like this:

   SELECT text, line  
     FROM all_source
    WHERE name = :name 
      AND type = :type
      AND owner = :owner
      AND line BETWEEN 1 AND 5
    ORDER BY line

2. Add the same clause as in Example 1 and then replace it with an element that limits rows retrieved to those that start with the keyword IF.

   PLVio.set_srcselect ('AND line BETWEEN 1 AND 5');
   PLVio.set_srcselect ('LTRIM (text) LIKE ''IF%''');

   The srcselect now looks like this:

SELECT text, line  

  FROM all_source
 WHERE name = :name 
   AND type = :type
   AND owner = :owner
   AND LTRIM (text) LIKE 'IF%'
 ORDER BY line

3. The following script displays all the lines currently stored in the USER_SOURCE data dictionary view that contain the keyword RAISE.

   DECLARE
      line PLVio.line_type;
      numlines NUMBER;
   BEGIN
      PLVio.setsrc (PLV.dbtab);
      PLVio.set_srcwhere
         ('WHERE instr (text, ''RAISE'') > 0');
      PLVio.initsrc;       
      LOOP
         PLVio.get_line (line, numlines);
         exit when line.eof;
         p.l (line.text);
      END LOOP;
   END;
   /

   Notice that the string I pass to set_srcwhere begins with the WHERE keyword. This signals to PLVio that the entire WHERE clause is to be discarded and replaced with the argument string so, in this case, srcselect would display this string:

   SELECT text, line  
     FROM all_source
    WHERE instr (text, 'RAISE') > 0
    ORDER BY line

12.4.3 Setting a Line Limit

The final program you can use to change the WHERE clause is the set_line_limit procedure. The header of set_line_limit is:

   PROCEDURE set_line_limit
      (line_in IN INTEGER, loc_type_in IN VARCHAR2 := c_first);

The first argument, line_in, is the line number involved in the restriction. The loc_type_in argument dictates how the line number is used to narrow down the rows retrieved. There are four possible location types; the impact of each of these is explained in the table below.

Here are some examples of the impact of set_line_limit:

1. Request that only lines greater than 100 be retrieved:

   PLVio.set_line_limit (100, PLVio.c_after);

   which adds the following element to the WHERE clause:

   /*LL100*/ AND line > 100 /*LL100*/

   The comments which bracket the AND statement are included so that the entire element can be identified and removed as needed.

2. Request that only lines less than or equal to 27 be retrieved:

   PLVio.set_line_limit (27, PLVio.c_last);

   This call adds the following element to the WHERE clause:

   /*LL100*/ AND line <= 27 /*LL100*/

   The set_line_limit procedure is used by initsrc to process the "starting at" and "ending at" arguments. The string version of initsrc also makes use of the line_with function to convert a "starting at" string into the appropriate line number, which is then passed to the integer version of initsrc, which then calls set_line_limit. Review that code for more pointers about how to use both of these line-restricter programs.

12.4.4 Cleaning Up the WHERE Clause

You can also remove elements from the WHERE clause using the rem_srcwhere and rem_line_limit procedures. The rem_srcwhere program sets the srcrep.where_clause string to NULL, which means that the entire SELECT statement will be determined by the contents of the srcrep.select_sql field. The rem_srcwhere procedure takes no arguments so you would call it simply as follows:

PLVio.rem_srcwhere;

It is important to remember that rem_srcwhere only NULLs out the srcrep.where_clause. If you have previously called set_srcwhere with a string that started with WHERE, then the text of the srcrep.select_sql field itself is modified. This change is not corrected in any way by a call to rem_srcwhere. Instead, in this situation you will have to re-execute setsrc (and consequently, initsrc) to get back to the default SELECT statement.

The rem_line_limit will remove an element from the WHERE clause that was added by a call to set_line_limit. The header of this procedure is:

PROCEDURE rem_line_limit (line_in IN INTEGER);

You specify the same line number of the line limit passed to set_line_limit, and the appropriate chunk of text is extracted from the srcrep.where_clause string.

Suppose I called set_line_limit to ask that I only retrieve rows where the line number is greater than 10:

PLVio.set_line_limit (10, PLVio.c_after);

Then the following call to rem_line_limit will take out this restricting factor:

PLVio.rem_line_limit (10);