Re: Problem: Function doesn't guarantee not to update database (ORA-06571)

jtdennis_at_atl.mindspring.com (John Dennis) wrote:

>I thought being able to use my own function in my select statments
>would a great time saver. However, there seems to be the limitation
>that the fucntion must quarantee not to update the database. I'm
>trying to identify ways around this or at least identify what to
>avoid.
 

>Here's what I got so far:
 

>Functions (called from selects) cannot be in a package. I didn't
>think this was true til I called the one and only function in a
>package that only had a return statement in it.
 

>Functions (called from selects) cannot call so standard packages like
>raise_application_error.
 

>Any thought or insights on this?
 

>Thanks in advance!
 

>Virtually,
 

>John Dennis
>
>John Dennis
>Tactics, Inc
>Atlanta, GA

John,
Functions can be called from the select statement when they are part of a package. I ran into the same problem and there is a solution in the documentation. I wish that I remembered the exact text, but I will try and point you in the right direction. In the reference manual, there is a reference to "Pragma Init References" or something along those lines. Like I said it has been over a year since I had this problem. Anyway, there are four variables you must set to false in your package declaration. They are all four characters long and start with a "W". I apologize for not remembering the exact text. I will try and look it up tomorrow at work. I do know that once I did this I did not have the problem anymore. You are right, by the way, I did notice a speed improvement when calling a function from within the select statement.

I hope this helps.

Scott Anders
MC Squared Inc.
Phoenix, Arizona Received on Thu Aug 08 1996 - 00:00:00 CEST