Re: DBMS_SQL and PRAGMA RESTRICT_REFERENCES

Hi,

Jonathan Waland wrote:

> DBMS_SQL and PRAGMA RESTRICT_REFERENCES
>
> I wish to use a function in a select statement, the function input
> parameter is a column in which a SQL query resides as a text string, the
> output will be a numeric.
>
> I need to use DBMS_SQL to execute the IN parameter as a SQL statement, I
> also need to use PRAGMA RESTRICT_REFERENCES (WNDS) as the function will
> be called in a SELECT, however it appears that I can't use both!!!
>
> When I try to compile my package the following is returned
>
> PLS-00452: Subprogram 'XXXXXX_XXXXXX' violates its associated pragma
>
> Even though my SQL Query is a simple SELECT COUNT(*)
>
> Any ideas? Does DBMS_SQL write to the database?

Potentially of course. DBMS_SQL can potentially do any statement against the database andtherefore you will never be able to do a problem solution like you intended with the above.
It's a very wise decision from Oracle to check pl/sql procedures for violation against any pragma.

So if you do a call in your procedure to another procedure that doesn't have the same or even more restrictions set as your procedure Oracle will always and for good reasons come up with this message ! In your case YOU know that you will not harm the database, put the PL/SQL engine could never find out !!, because it would be absurd if dbms_sql would be implemented with any restriction !!

Regards
  Robo.

Received on Sat Jun 13 1998 - 13:20:24 CDT