Re: PRAGMA errors

On Sun, 01 Feb 1998 02:35:02 -0500, Leslie_B_DreyerKalra <lbd_at_techiesinc.com> wrote:

[snipped pragma problems]

>I think I found this one myself (I wasn't making sure that all of the
>functions called by my function were also pure). Now I'm having another
>problem, though:
>
>I have a function that builds two dynamic SQL queries (both SELECTs --
>no UPDATEs, INSERTs or DELETEs), loads the results of one into a PL/SQL
>table, then compares the results of the second against the PL/SQL table.
>I define a simple WNDS pragma for this function (it really doesn't write
>to any database tables, nor does it call any other functions of mine),
>but I'm getting the "violates its associated pragma" message.

[snip]

>I *really* need to use this function in a WHERE clause, and I'm getting
>desperate. Does anyone have any idea how I can get this to work???????
>
>Oracle 7.3.3.3, Sparc Solaris 2.5.1, FYI...
>

Hi Leslie,

you cannot use restrict_references for a function that calls dbms_sql. The purity level of a function must be clear at compile time, but Oracle cannot look into your use of dynamic sql statements at compile time, so it won't allow it at all.

BTW, regarding your other problem: Obviously, there is a pl/sql bug with pragmas in certain Oracle releases. I had a very simple function some days ago with (wnds, wnps, rnps). It compiled fine against 7.3.4.0.1 on Solaris, but failed with 'violates its pragma' on 7.3.2.3 on HP/UX. For the latter, I could work around it by just taking rnps out. Funny although, there were really no package globals involved, and no call to any other pl/sql whatsoever. Perhaps Oracle support may have some information about it.

Regards
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net