Re: pragma RESTRICT_REFERENCES

Connor McDonald wrote:
>
> Thomas Kyte wrote:
> >
> [snip]
>
> Thomas,
>
> Thanks for your detailed reply which has been most helpful...
>
> Interestingly, further investigation to yield that the cause of
> my problem seems to be the use of PL/SQL table attributes...
>
> Consider the following example...
>
> ====================================================================
> SQL*Plus: Release 3.3.3.0.0 - Production on Wed Jun 10 18:40:32 1998
>
> Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
>
> Connected to:
> Oracle7 Server Release 7.3.3.5.0 - Production Release
> With the distributed, replication and parallel query options
> PL/SQL Release 2.3.3.5.0 - Production
>
> SQL> show user
> user is "SYSTEM"
>
> SQL> create or replace
> 2 package demo as
> 3 pragma restrict_references(demo,wnds);
> 4 function get_plsql_tab_index return binary_integer ;
> 5 pragma restrict_references(get_plsql_tab_index,wnds);
> 6 end;
> 7 /
>
> Package created.
>
> =
> = So all is fine so far. Both package and package proc defined
> = with WNDS. (The package pragma just in there for completeness)
> =
>
> SQL> create or replace
> 2 package body demo as
> 3 type defn_record_type is record (
> 4 varcol varchar2(5),
> 5 numcol number);
> 6 type defn_tab_type is table of defn_record_type
> 7 index by binary_integer;
> 8 s_def defn_tab_type;
> 9 function get_plsql_tab_index return binary_integer as
> 10 begin
> 11 return s_def.prior(123123);
> 12 end;
> 13 end;
> 14 /
>
> Warning: Package Body created with compilation errors.
>
> SQL> show errors
> Errors for PACKAGE BODY DEMO:
>
> LINE/COL
> ERROR
> --------
> -----------------------------------------------------------------
> 0/0 PL/SQL: Compilation unit analysis
> terminated
> 8/3 PLS-00452: Subprogram 'GET_PLSQL_TAB_INDEX' violates
> its
> associated
> pragma
>
> =
> = A most odd error considering that the only thing function
> = "get_plsql_tab_index" is doing is getting the 'prior' attribute
> = for a pl/sql table...
> =
> = If I change the function to eliminate the reference to 'prior'...
> =
>
> SQL> create or replace
> 2 package body demo as
> 3 type defn_record_type is record (
> 4 varcol varchar2(5),
> 5 numcol number);
> 6 type defn_tab_type is table of defn_record_type
> 7 index by binary_integer;
> 8 s_def defn_tab_type;
> 9 function get_plsql_tab_index return binary_integer as
> 10 begin
> 11 return 123123;
> 13 end;
> 14* end;
> SQL> /
>
> Package body created.
>
> =
> = all is fine...
> =
>
> Thoughts anyone ???
>
> Cheers
> Connor
>
> --
> ==========================================
> Connor McDonald
> BHP Information Technology
> Perth, Western Australia
> "These views mine not BHP..etc etc"
>
> "The only difference between me and a madman is that I am not mad."

The problem is that you are trying to assign a value to s_def.prior, the pragma WNDS means Write NO Database State, and you are trying to write to a variable (change a Database State), thus violating the pragma.

The only way to prevent this is to return the value as you have in the second example.

Hope this helps,

Andy Received on Wed Jun 10 1998 - 07:31:55 CDT