Re: Selecting function derived columns

A copy of this was sent to Justin Wigg <justinwigg_at_yahoo.com> (if that email address didn't require changing) On Wed, 09 Jun 1999 00:50:10 GMT, you wrote:

>In article <3761148a.3952573_at_newshost.us.oracle.com>,
> tkyte_at_us.oracle.com wrote:
>
>> try adding:
>>
>> pragma restrict_references( xxxxx, wnds, WNPS );
>>
>> if your function WRITES to the package state (has side effects,
>> modifies global variables) you will not be able to call it in a where
>> clause. If it has no side effects -- you can.
>
>Hmmmmm. All the package does is a secondary select statement and
>concatenates its several columns into a single returned VARCHAR2 (used
>for followup client names and addresses). Reading from the database
>isn't considered a 'side-effect' is it? Certainly not any globals
>involved.
>

[Quoted] In order to call the function from a WHERE clause it must promise NOT TO:

[Quoted]
• write to the database (WNDS)
• write to the package state (WNPS)

nothing about reading from the database -- it can read from the database no problem. If it modifies any PACKAGE variables (variables declared outside of the procedures/functions in the package) it cannot be called from the WHERE clause.

did you try the above pragma ?? did it work?

>The versions being used are:
>
>Oracle 7.3.2.3.0
>PL/SQL 2.3.2.3.0
>SQL*Plus 3.3.2.0.2
>Data Browser 2.0.10.1.0
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation