Re: pragma RESTRICT_REFERENCES

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Thu, 11 Jun 1998 10:42:30 -0800
Message-ID: <35802516.564F@bhp.com.au>

Andy Carruthers wrote:
>
> 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

Thanks for your posting....

From the App Developer guide I got the following...

• WNDS means "writes no database state" (does not modify database tables)
• WNPS means "writes no package state" (does not change the values of packaged variables)

Given that the only thing my function is doing is returning the "PRIOR" attribute of a PL/SQL table (not a database table) I can't see how it is violating WNDS...In fact, the entire package makes no reference to any database
table whatsoever....???

Comments ???

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." Received on Thu Jun 11 1998 - 13:42:30 CDT