| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-06571 HELP!! HELP!! HELP!! HELP!!
On Fri, 10 Oct 1997 23:53:58 -0700, "Fred Hirschfeld" <johndoe_at_microsoft.com> wrote:
>>>Hi All,
>>>
>>> I have a problem with a Function. I´m using a function that INSERT
>>>records in a Table.
>>>The function is in a Package, the function Return a NUMBER. When I
>>>called this function from
>>>an SQL expression, such as:
>>> SELECT <function-name> FROM DUAL;
>>>
>The key to this is the function is in a package... We had a similar problem,
>and fixed it by making the packaged function a stand alone function and it
>then worked. I don't know why though... anyone care to enlighten us?
the reason is that functions called from SQL must at the very least gaurantee to not update the database. For standalone functions, the compiler will automatically determine the 'purity' level of a function and store that in the data dictionary. For packaged functions, this automatic assertion of the purity level does not take place (the overhead for 1 function to determine this is small, for a package with hundreds of functions, this would be quite large so they don't do it automatically for packages).
anyway, in the package specification, for those functions you wish to call from sql, you would code:
...
function foo return number;
pragma restrict_references( foo, WNDS );
...
that tells the compiler that you believe the function foo
Writes No Database State
^ ^ ^ ^
See the application developers guide, "using packages and procedures", The section on calling stored functions from SQL. In there it talks about controlling side effects via the use of this pragma.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |