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.
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
PROCEDURE set_srcwhere (where_in IN VARCHAR2);
This procedure modifies the WHERE clause according to the following rules:
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:
SELECT text, line
FROM all_source WHERE name = :name AND type = :type AND owner = :owner AND LTRIM (text) LIKE 'IF%' ORDER BY line
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 :
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:
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:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.