Re: How can I use PRAGMA?

From: Michael Kustermann <MKUSTERM_at_postoffice.worldnet.att.net>
Date: 1996/09/28
Message-ID: <324D8C12.4FF5_at_postoffice.worldnet.att.net>#1/1


Marcelo A. Collao Huper wrote:
>
> I create the following package:
>
> Create or Replace Package Add_Pkg As
> oldNumber Number(3);
> /* Prototipo de la funcion Add_Fun */
> Function Add_Fun (addNumber in Number) Return Number;
> End Add_Pkg;
> /
>
> Create or Replace Package Body Add_Pkg As
> Function Add_Fun (addNumber in Number)
> Return Number Is
> Pragma "Write No Database State"; /* ???????? */
> Begin
> If Add_Pkg.oldNumber Is Null Then
> Add_Pkg.oldNumber := 0;
> End If;
> Add_Pkg.oldNumber := Add_Pkg.oldNumber + addNumber;
> Return(Add_Pkg.oldNumber);
> End Add_Fun;
> End Add_Pkg;
> /
>
> I use the Add_Pkg.Add_Fun function, but the following error appers:
>
> SQL> select Add_Pkg.Add_Fun(n) from test;
> select Add_Pkg.Add_Fun(n) from test
> *
> ERROR at line 1:
> ORA-06571: Function ADD_FUN does not guarantee not to update database
>
> Add table is:
> SQL> create table test (n number(3));
>
> Table created.
>
> SQL> insert into test values (7);
>
> 1 row created.
>
> Can I use PRAGMA to avoid this error?
>
> Thanks...
> ---
> Marcelo A. Collao Huper.
> ORDEN S. A.. Miraflores 388,
> Santiago, Chile. | e-mail : mailto:mcollao_at_orden.cl
> Tel: (52 2) 68 69 135 | intranet: http://baco.holding.cl/~mcollao/page
> Fax: (52 2) 68 69 126 | internet: http://www.dcc.uchile.cl/~mcollao

Here's what you do...
  After the function declaration in the package header enter...

PRAGMA RESTRICT_REFERENCES (<function_name>, WNDS [,WNPS] [,RNDS] [,RNPS]);

Where <function name> is your function

WNDS - Write no Database state - Required
WNPS - Write no Package state - doesn't update package vars
RNDS - Read no Database state
RNPS - Read no Package state.

Basically the rules for using a stored function in a select statement are... - The function may not insert/update/delete table data - Remote or Parallelized functions cannot read or write the value of

   package variables.
- Only functions called from the SELECT, VALUES, or SET clause may write the

   values of package variables

Hope this helps,
Michael Kustermann Received on Sat Sep 28 1996 - 00:00:00 CEST

Original text of this message