#

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

## 12.4 The Source WHERE Clause

PLVio provides a set of programs used within PLVio and also available to you to modify the contents of the WHERE clause of the SELECT statement for a database table source. These programs must be called after the call to `setsrc` and before the call to `initsrc`.

The default WHERE clause for the database source is:

>     WHERE name = PLVobj.currname
>       AND type = PLVobj.currtype

This WHERE clause reflects the relationship between the current object of PLVobj and the default PLVio source database table, `user_source`. It is stored directly in the `srcrep.select_sql` field and is set in the call to `setsrc`. Additional WHERE clause information is stored in the `where_clause` field of the same `srcrep` record (see Section 12.2.2, "Database Source or Target" earlier in this chapter).

You can modify this WHERE clause in two ways: replace it completely or add additional elements to that clause. The `set_srcselect` will do either of these actions. The `set_line_limit` applies additional elements to the WHERE clause. `rem_srcselect` and `rem_line_limit` remove elements from the WHERE clause. The `srcselect` function displays the current SELECT statement.

Each of these programs is explained below.

### 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.

Constant | Action
--- | ---
`c_first` | Retrieve lines >= specified line number
`c_last` | Retrieve lines <= specified line number
`c_before` | Retrieve lines > specified line number
`c_after` | Retrieve lines < specified line number

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);

12.3 Managing the Source Repository |  | 12.5 Managing the Target Repository

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