Re: Pragma Violation

markag wrote
>The function works fine when taken out of the package
>and tested. The problem is when I try to compile the
>package body, I get this error:
>
>PLS-00452: SUBPROGRAM 'GET_PROVIDERHASH'
>violates it's associated pragma

For a stand-alone function, you do not need to specify the pragma, as Oracle will decide on runtime whether or not the function is allowed in a query. So, it is not too strange that the function runs fine outside a package as it indeed does not write to the database.

However, when making the function part of a package, you need to specify the restrict_references, as Oracle uses some early binding and therefore needs to check at compile time what is happening.

You specified a restrict_references, but your function uses other packages that appearantly do not confirm to the this level of restrict_references. In fact, using dbms_sql spoils it. There is no way that Oracle can tell at compile time if your dynamic SQL statement really does not write to the database, so dbms_sql does not have restrict_references set to WNDS. Therefore, no function that uses dbms_sql can confirm to WNDS, whether you say so or not.

To get rid of the dynamic SQL, you need to get rid of the

    AND PFT.BATCH_ID IN (' || IN_BATCH_IDS || ')'; So, how much entries do you expect in in_batch_ids? If it is only a few then consider using simply more optional parameters and something like

• if in_batch_id1 is null, then always true: and ( (pft.batch_id = nvl(in_batch_id1, pft.batch_id) or (pft.batch_id = in_batch_id3) or (pft.batch_id = in_batch_id3) or (pft.batch_id = in_batch_id4) ... )

Then you could simply execute the query in PL/SQL, without the need for dynamic SQL.

By the way? Is this code somehow generated? Arjan. Received on Mon Mar 08 1999 - 14:13:01 CST