Re: strange PRAGMA RESTRICT_REFERENCES Violation

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 10 May 2002 15:23:26 -0700
Message-ID: <abhh8u01t0j@drn.newsguy.com>

In article <abha6l$64q$1_at_reader1.panix.com>, "R says...
>
>Below is a simple function that returns first 6 digits of phone#
>No problem compiling...when nothing is called in Body's exec. section....
>
>But when I call a procedure in Body's exec. section
>I get PLS-00452...Oracle complains about the function violating its
>pragma....
>(That procedure has nothing to do with the pragma function)
>What gives ????
>Thanks
>
>Rob
>8.1.7.2/AIX
>
>
>-- Declared in Pkg Spec
>FUNCTION area_code_exchange
> (phone_in IN customer.phone%TYPE)
> RETURN NUMBER;
> PRAGMA RESTRICT_REFERENCES (area_code_exchange, WNDS);
>
>-- In Body
> FUNCTION area_code_exchange
> (phone_in IN customer.phone%TYPE)
> RETURN NUMBER
> IS
> BEGIN
> RETURN nvl(to_number(substr(phone_in,1,6)), 0);
> END;
>
>

First -- in 817, restrict_references is somewhat pendantic, not needed.

Here is a complete example showing that if you have JUST the above -- it works. Then I modify it to show how to "break it" and then how to modify it to see where it breaks:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> drop table customer; Table dropped.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table customer( phone varchar2(20) ); Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package foo   2 as
  3
  4 FUNCTION area_code_exchange
  5 (phone_in IN customer.phone%TYPE)   6 RETURN NUMBER;
  7 PRAGMA RESTRICT_REFERENCES (area_code_exchange, WNDS);   8 end;
  9 /

Package created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package body foo   2 as
  3 -- In Body
  4 FUNCTION area_code_exchange
  5 (phone_in IN customer.phone%TYPE)   6 RETURN NUMBER
  7 IS
  8 BEGIN
  9 RETURN nvl(to_number(substr(phone_in,1,6)), 0); 10 END;
11 end;
12 /

Package body created.

So, the stuff you posted -- works great. Now, lets modify the package body, not changing the function at all:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package body foo   2 as
  3 -- In Body
  4 FUNCTION area_code_exchange
  5 (phone_in IN customer.phone%TYPE)   6 RETURN NUMBER
  7 IS
  8 BEGIN
  9 RETURN nvl(to_number(substr(phone_in,1,6)), 0); 10 END;
11
12 begin
13 update customer set phone=phone; 14 end;
15 /

Warning: Package Body created with compilation errors.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show err Errors for PACKAGE BODY FOO:

LINE/COL ERROR

-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
4/3      PLS-00452: Subprogram 'AREA_CODE_EXCHANGE' violates its
         associated pragma

And there is your error. In order to see it is really not area_code_exchange failing but something higher up, we can:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package foo   2 as
  3 pragma restrict_references( foo, WNDS ); <<<========   4
  5 FUNCTION area_code_exchange
  6 (phone_in IN customer.phone%TYPE)   7 RETURN NUMBER;
  8 PRAGMA RESTRICT_REFERENCES (area_code_exchange, WNDS);   9 end;
10 /

Package created.

Warning: Package Body created with compilation errors.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> show err Errors for PACKAGE BODY FOO:

LINE/COL ERROR

-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
3/2      PLS-00452: Subprogram 'FOO' violates its associated pragma

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

And that tells us to look up a level.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Fri May 10 2002 - 17:23:26 CDT