Re: Pragma Restrict_references combined with NO_DATA_FOUND exception problem.

On 7 Jul 1998 16:39:57 GMT, duggan_at_risky.ecs.umass.edu (John F Duggan) wrote:

>create or replace package sysuaf_pack as
> pragma restrict_references ( sysuaf_pack, wnds );
>
> function UGROUP (IN_username in varchar2) RETURN varchar2;
> pragma restrict_references ( ugroup, wnds );
>
>end sysuaf_pack;
>/
>
>create or replace package body sysuaf_pack AS
>function UGROUP (IN_username in varchar2)
> return VARCHAR2
> is U_GROUP VARCHAR2(32);
>begin
> select (UPPER(UIC_GROUP)) INTO U_GROUP
> FROM SYSUAF_TABLE WHERE UPPER(USERNAME) = UPPER(IN_USERNAME);
> return ( U_GROUP );
> exception
> when no_data_found then
> Raise_application_error ( -100, 'Username does not exist' );
>end UGROUP;
>end sysuaf_pack;
>/

The problem is that raise_application_error is a procedure in dbms_standard and it does have any pragmas defined. Therefore, you can't use it in a function with

   pragma restrict_references ( ugroup, wnds );

Maybe try returning some dummy value instead of raising an error.

Alternately, if you are very brave (and/or foolish) you could edit the dbms_standard package header and include the necessary pragma. Not recommended (Oracle Corp will likely disavow any knowledge of your existence) but I did it on a PO7 instance that we were experimenting with and it seemed to work.

You could move up to Oracle 8. Not sure exactly which version the pragma showed up in, but it was definitely there in 8.0.3.

Have fun.....

Pat. Received on Wed Jul 08 1998 - 10:46:18 CDT