I tried the following package code (without specifing progma stuff) on
oracle 8.1.5 and it did not give error. Anyone could explain to me why?
Thanks.
SQL*Plus: Release 8.0.3.0.0 - Production on Fri Jan 26 10:59:22 2001
(c) Copyright 1997 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
SQL> drop package pragma_test;
Package dropped.
SQL> ------------------------------------------------------------------
SQL> CREATE OR REPLACE PACKAGE pragma_test
2 IS
3 FUNCTION my_tochar(ad_val IN DATE,
4 as_format IN VARCHAR2) RETURN VARCHAR2;
5
5 END pragma_test;
6 /
Package created.
SQL> ------------------------------------------------------------------
SQL> CREATE OR REPLACE PACKAGE BODY pragma_test
2
2 IS
3 ------------------------------------------------------------------
4 FUNCTION my_tochar(ad_val IN DATE,
5 as_format IN VARCHAR2) RETURN VARCHAR2
6 IS
7
7 BEGIN
8 RETURN TO_CHAR(ad_val, as_format);
9 END;
10
10 END pragma_test;
11 /
Package body created.
SQL>
SQL>
SQL> -- now I run sql statement that calls the package:
SQL>
SQL> SELECT pragma_test.my_tochar(sysdate,'DD-MON-YY') FROM DUAL;
PRAGMA_TEST.MY_TOCHAR(SYSDATE,'DD-MON-YY')
26-JAN-01
SQL>
SQL>
In article <yLPb6.146$at3.183991_at_nnrp3.sbc.net>,
"Spencer" <spencerp_at_swbell.net> wrote:
> the reason i specify PRAGMA RESTRICT_REFERENCES
> is to allow package functions to be used in DML statements
> (e.g. SELECT )
>
> in order to use a PL/SQL function in a DML statement, Oracle
> will check whether the function is guaranteed not to perform
> certain operations. Oracle will automatically check this for a
> "standalone" function, but not for functions in a "package".
>
> the pragma restrict_references allows you to assert the purity
> level of the function, and that means that the package function
> can be used in DML statements.
>
> don't think you can outsmart the PL/SQL compiler, though.
> it does check to see if the assertion is valid when the body
> is compiled.
>
> you can check this easily by developing your own package
> which includes a simple function, for example:
>
> my_tochar(ad_val IN DATE, as_format IN VARCHAR2)
> IS
> BEGIN
> RETURN TO_CHAR(ad_val, as_format);
> END;
>
> and test it by using the function in a DML statement, e.g.
>
> SELECT mypackage.my_tochar(sysdate,'DD-MON-YY')
> FROM DUAL;
>
> try it again after adding the pragma restrict_references
> and recompiling.
>
> HTH
>
> <gmei_at_my-deja.com> wrote in message news:94o76r$fv9
$1_at_nnrp1.deja.com...
> > Hi:
> >
> > Could someone tell me what is the "advantage" of using PRAGMA
> > RESTRICT_REFERENCES, such as WNDS and WNPS, and why do you "want
to" use
> > it in certain case? I am reading this part in Steven Feuerstein's
Oracle
> > PL/SQL Programming book and would like to clarify it a bitter.
> >
> > Is there an example that one would want to use the PRAGMA in the
package
> > code and why?
> >
> > TIA.
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
>
Sent via Deja.com
http://www.deja.com/
Received on Fri Jan 26 2001 - 10:00:48 CST
