Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cannot call function in query

Re: Cannot call function in query

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/06/04
Message-ID: <3395a56f.41964230@www.sigov.si>#1/1

On Wed, 4 Jun 1997 17:28:47 +1000, rowanm_at_intercoast.com.au (Rowan McCammon) wrote:

>PL/SQL: I've declared a function in a package that simply
>returns a constant.
>
>When I use this function in a SQL query I get an error
>message that tells me something like "Cannot perform
>query as database may be updated. Cannot guarantee non-
>update."
>
>If I then define a copy of this function outside the
>package (i.e.. simply a stored function) and call it from
>within a query, the query works fine.
>
>Can anyone explain this to me?

It is called "purity level of function". Stored functions can have side effects on database objects they access during execution and server must be sure this "unwanted" side effects will not happen. For standalone functions server can check function body to decide if function will behave normaly during execution. For package functions, however, server sees only function declaration, not its body.

To overcome this problem in packaged functions, you must use RESTRICT_REFERENCES pragma in function declaration of package specification. As arguments to this pragma you specify the purity level of the function, that is, does it read or write database tables or package variables. Now, the server can check the behavior of function during its compilation and if its side effects are not as defined in RESTRICT_REFERENCES pragma in package specification, the compilation will fail.

When calling package function from SQL, server now looks at function declaration in package specification and checks its purity level in RESTRICT_REFERENCES pragma. If it is "pure enough" for usage in desired action, it will execute it, otherwise it will return error message.

For more informations look at PL/SQL Guide and Aplication Deeveloper's Guide.

Regards,


Jurij Modic                             Republic of Slovenia
tel: +386 61 178 55 14                  Ministry of Finance
fax: +386 61  21 45 84                  Zupanciceva 3
e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000
Received on Wed Jun 04 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US