Re: Pragma definition for PL/SQL Packages

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/24
Message-ID: <3518c4b7.90459543@192.86.155.100>#1/1

A copy of this was sent to gaverill_at_chsra.wisc.edu (Gerard M. Averill) (if that email address didn't require changing) On Tue, 17 Mar 98 22:35:02 GMT, you wrote:

>In article <350D9861.426DA5A5_at_snet.net>, Mark Rothstein <xxxmarkr_at_snet.net> wrote:
>>I followed each of the functions declarations with its own pragma
>>statement (even though it was the same statement in each case), e.g.
>>
>> FUNCTION func1(arg1 IN VARCHAR2, arg2 IN NUMBER) RETURN NUMBER;
>> PRAGMA RESTRICT_REFERENCES(func1, WNDS, WNPS);
>> FUNCTION func1(arg1 IN NUMBER, arg2 IN NUMBER) RETURN NUMBER;
>> PRAGMA RESTRICT_REFERENCES(func1, WNDS, WNPS);
>>
>
>Does this really work? (that is, can you really use all versions of the
>overloaded function in SQL statements?) The Oracle documentation seems to
>say no (Oracle7 Server Application Developer's Guide):
>
>"PL/SQL lets you overload packaged (but not standalone) functions. That is,
>you can use the same name for different functions if their formal parameters
>differ in number, order, or datatype family.
>
>However, a RESTRICT_REFERENCES pragma can apply to only one function
>declaration. So, a pragma that references the name of overloaded functions
>always applies to the nearest foregoing function declaration."
>

What the documentation is saying here is not that you cannot use overloaded functions with pragma restrict_references but that you must supply a pragma for each version of the overloaded function you want to call. For example, the following works in 7.1, 7.2, 7.3, and 8.0:

create or replace package demo
as

    function myfunc( arg1 in varchar2, arg2 in number ) return varchar2;     pragma restrict_references( myfunc, WNDS, RNDS, WNPS, RNPS );

    function myfunc( arg1 in number, arg2 in number ) return varchar2;     pragma restrict_references( myfunc, WNDS, RNDS, WNPS, RNPS );

    function myfunc( arg1 in date, arg2 in number ) return varchar2;     pragma restrict_references( myfunc, WNDS, RNDS, WNPS, RNPS );

    pragma restrict_references( demo, WNDS, RNDS, WNPS, RNPS ); end;
/

create or replace package body demo
as

function myfunc( arg1 in varchar2, arg2 in number ) return varchar2 is
begin

    return 'This is version 1';
end;

function myfunc( arg1 in number, arg2 in number ) return varchar2 is
begin

    return 'This is version 2';
end;

function myfunc( arg1 in date, arg2 in number ) return varchar2 is
begin

    return 'This is version 3';
end;

end;
/
column c1 format a20
column c2 format a20
column c3 format a20
set arraysize 1

select demo.myfunc( 'A string', 1 ) c1,
       demo.myfunc( 5, 1 ) c2,
       demo.myfunc( sysdate, 1 ) c3

from dual;

C1                   C2                   C3
-------------------- -------------------- --------------------

This is version 1 This is version 2 This is version 3

>Just wondering if anyone has _actually_ succeeded in doing this.
>
>Gerard
>
>----
>Gerard M. Averill, Associate Researcher
>CHSRA, University of Wisconsin - Madison
>GAverill_at_chsra.wisc.edu
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Mar 24 1998 - 00:00:00 CST