Re: Can't use FUNCTION in SQL?

A copy of this was sent to Norris <johnnie_at_cooper.com.hk> (if that email address didn't require changing) On 28 Aug 1999 19:06:10 GMT, you wrote:

>What should we do in Oracle 8i?
>

just do it:

tkyte_at_8i> create or replace package demo_pkg   2 as
  3 function foo return number;   4 end;
  5 /

Package created.

tkyte_at_8i>
tkyte_at_8i> create or replace package body demo_pkg   2 as
  3 function foo return number is begin return 1; end;   4 end;
  5 /

Package body created.

tkyte_at_8i>
tkyte_at_8i> drop table t;

Table dropped.

tkyte_at_8i> create table t ( x int );

Table created.

tkyte_at_8i>
tkyte_at_8i> select demo_pkg.foo from dual;

       FOO

         1

tkyte_at_8i> insert into t values ( demo_pkg.foo );

1 row created.

no more need to assert the purity level in most all cases.

>Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>: A copy of this was sent to hlh_nospam_at_excite.com (Howard Lee Harkness)
>: (if that email address didn't require changing)
>: On Sat, 28 Aug 1999 16:31:03 GMT, you wrote:
>
>:>In Owens' book on PL/SQL, he states that a function "evaluates to the
>:>value of some datatype which can be substituted in any place where a
>:>variable of the same type can be placed."
>:>
>:>However, I tried to use a function in an insert statement in place of
>:>a variable, and the resulting package would not compile. A simplified
>:>example:
>
>: [snip]
>
>: in Oracle8.0 and before you have to assert the purity level of packaged
>: procedures and functions.
>
>: In your package specification, add:
>
>: function SplitAssay(....);
>: PRAGMA RESTRICT_REFERENCES( SplitAssay, WNDS );
>
>:
>: the pragma tells the compiler to verify that the function does NOT write to the
>: database state (minimal requirement to be callable from SQL)
>
>
>: --
>: See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
>: Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>:
>: Thomas Kyte tkyte_at_us.oracle.com
>: Oracle Service Industries Reston, VA USA
>
>: Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
>--

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Aug 28 1999 - 15:12:49 CDT