Re: Problem: Function doesn't guarantee not to update database (ORA-06571)
Date: 1996/08/09
Message-ID: <4ufpoi$goe_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <4uec9p$ot4_at_nnrp1.news.primenet.com>, vscotta_at_primenet.com (V. Scott Anders) writes:
|> 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.
|> >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.
|>
You are looking for the PRAGMA RESTRICT_REFERENCES statement. It is documented in the PL/SQL manual, version 2.2 or higher.
|> I hope this helps.
|>
|> Scott Anders
|> MC Squared Inc.
|> Phoenix, Arizona
|>
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Fri Aug 09 1996 - 00:00:00 CEST