Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 12.3 Managing the Source RepositoryChapter 12
PLVio: Reading and Writing PL/SQL Source Code
Next: 12.5 Managing the Target Repository
 

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


Previous: 12.3 Managing the Source RepositoryAdvanced Oracle PL/SQL Programming with PackagesNext: 12.5 Managing the Target Repository
12.3 Managing the Source RepositoryBook Index12.5 Managing the Target Repository

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference