Re: PRAGMA errors

Leslie_B_DreyerKalra wrote:
>
> Leslie_B_DreyerKalra wrote:
>
> 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.
>
> It does call DBMS_SQL functions, and I can't vouch for their purity, and
> the EXISTS function for PL/SQL tables. If I take out the EXISTS
> function, it still violates, so EXISTS isn't the culprit. Other than
> that, it's self-contained. I can't see how it's violating its pragma!
>

Okay, I've got this working now, but instead of using one generic function to do the work for all four tables that have to be looked at, I have to have four separate functions, one for each table.

The DBMS_SQL functions, as Graham and Peter pointed out, destroy the purity of a function. Because the SQL statement is constructed at run time, the compiler has no idea what it does and can't bless it with a RESTRICT_REFERENCES pragma. Makes sense, and it had crossed my mind, but I had hoped that two such useful things would not be mutually exclusive.

Anyway, I re-wrote the function as four functions and removed the EXISTS function (which also violates the pragma) and it's working now. It took lots longer than I thought, so my schedule is off now, but I've learned from my mistake...:)

Thanks, everyone, for the quick help! I was able to get it fixed over the weekend, which I had resigned myself to losing. Thank heavens for UseNet folks!

Leslie Dreyer Kalra
Techies, Inc.
lbd_at_techiesinc.com Received on Mon Feb 02 1998 - 00:00:00 CST