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:
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.
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:
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.
Retrieve lines >= specified line number
Retrieve lines <= specified line number
Retrieve lines > specified line number
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:
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.