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.

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

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:

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

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

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:

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

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

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

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

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
:

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.

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.

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: