Re: How can I use PRAGMA?
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