Re: PL/SQL Package Functions in SELECT statements...

Peter Derrick (pld) wrote:
: I have created a PL/SQL package containing several functions to convert
: column data as part of a view. When I try to use the functions in a simple
: query such as:
 

: select funcs.funcX(columnX) from dual;
 

: I get the following response from oracle:
 

: ORA-06571: Function FUNCX does not guarantee not to update database
 

: The Oracle error messages manual stopped at number 6570. So I delved into
: the file $ORACLE_HOME/rdbms/mesg/oraus.msg and came up with the following
: text:
 

: 06571, 00000, "Function %s does not guarantee not to update database"
: // *Cause: A plsql function that does not have a pragma asserting that it
: // does not write any database state is referenced in a sql
: statement.
: // Such functions cannot be used in sql statements.
: // *Action: Recreate the function and/or the functions it calls with the
: pragma
 

: Having found this I thought that I was finally onto the fix, but when I
: looked in the PL/SQL Users Guide and Reference there was little
 information on the use
: of "pragma". So I'm now left with the question what pragma! Is
: EXCEPTION_INIT
: with some magic arguments the go or should it be FIPSFLAG or some other
: obscure
: pragma.
 

: I seem to recall a problem like this being posted some time ago, so
: apologies. Any help you may have will be appreciated. E-mail me direct or
: post to the newsgroup.
 

: Thanks in advance
 

: Peter Derrick.

Hi Peter,

You'll need to use the PRAGMA
RESTRICT_REFERENCES(function_name or package name, WNDS,WNPS,RNDS,RNPS). This pragma needs to be defined in a package specification, after defining the function or package. This pragma asserts that no db changes will be done. Refer to Chapter 2 on the PL/SQL 2.1 and Oracle Precompilers, Release 1.6 Addedndum, First edition for more information.

--
---------------------------------------------------------------------------
Kannan 
Email: skannan_at_fs.com
Mastech Systems Corporation

The above are my own comments and opinion. They do not purport that of 
anybody else.
       ** Different is Not Better. Better is not Different **
---------------------------------------------------------------------------