Re: Selecting function derived columns

I'm guessing here but is your function trying to read variables, read from database etc? You may need additional pragmas (WNPS, RNDS etc). Also, what version of oracle and pl/sql are you using? Maybe your version doesn't support functions in where clauses.

Mark

Justin Wigg wrote in message <7jhq8n$6qm$1_at_nnrp1.deja.com>...
>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?
>
>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!)
>--
>The trouble with common sense | Justin Wigg - Hobart, AUSTRALIA
>Is that it's not that common. | Reply: justin.wigg_at_dpiwe.tas.gov.au
>
>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Tue Jun 08 1999 - 10:20:53 CEST