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 Tue, 08 Jun 1999 01:05:32 GMT, you wrote:

>I have recently been trying to create an ad-hoc style query in SQL*Plus
>that a user can open in Data Browser. Due to a complex table structure
>I have two columns in the view's select statement that are derived from
>PL/SQL function calls.
>
>I have created the pragma properly etc...everything is working fine
>UNTIL the user goes to place some sort of where clause restriction on
>one of those function derived columns. (eg., WHERE fn_col IS NOT NULL)
>
>The error 'ORA-06573 Function XXXX modifies package state, cannot be
>used here' is returned although the pragma is defined as:
>
> FUNCTION xxxx(parameter_1 IN NUMBER
> ,parameter_2 IN VARCHAR2)
> RETURN VARCHAR2;
> PRAGMA RESTRICT_REFERENCES (xxxx, WNDS);
>
>and therefore stating that the package *may* be altered. Correct?
>

[Quoted] 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.

>Anyway, I think I understand the problem and have been trying all sorts
>of things to trick SQL*Plus into allowing the where clause. Views,
>views of other views, in-line views in the FROM clause, even copying the
> function derived column into a second column by using initcap and
>trying to restrict the *copied* column but the error still occurs.
>Maybe this just can't be done? Perhaps the query is too complex to do
>without using PL/SQL cursors and therefore having to develop in Oracle
>Reports?
>
>I'm tearing my hair out and would greatly appreciate any help the group
>might have. Thanks. (I'll go back to lurking now!)

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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