PL/SQL Package Functions in SELECT statements...

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. Received on Wed Sep 06 1995 - 00:00:00 CEST