Selecting function derived columns

From: Justin Wigg <justinwigg_at_yahoo.com>
Date: Tue, 08 Jun 1999 01:05:32 GMT
Message-ID: <7jhq8n$6qm$1_at_nnrp1.deja.com>

[Quoted] [Quoted] 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] 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 - 03:05:32 CEST