O'Reilly logo

Advanced Oracle PL/SQL Programming with Packages by Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 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') ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required