User defined functions in SQL: is it possible?

From: Dave Wotton <Dave.Wotton_at_it.no-spam.camcnty.gov.uk>
Date: 23 Jun 1998 16:04:31 GMT
Message-ID: <01bd9ebf$4add4840$7501020a@res1181.Camcnty.gov.uk>

Hi,

I'm trying to write a user-defined function which I can call from SQL. eg.

CREATE FUNCTION HR_LOOKUP ( .... ) to be called in a select statement as follows:

     SELECT  HR_LOOKUP('MARITAL_STATUS',PP.MAR_STAT)
          FROM PER_PEOPLE_F PP

where the function HR_LOOKUP decodes the MAR_STAT status code using a lookup of a database table.

If I do it exactly as described above, it works ( in Oracle 7.1.6 too! ). This is despite the "PL/SQL User's Guide and Reference, Version 2.0, page 6-7 saying it won't work.

So my next experiment was to include the function in a package. The package compiles ok, but I get the error ORA-6571, when I try to execute a select statement which calls my packaged function.

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

It looks like it might work. Does anyone have any idea what this pragma might be? I can find very little documentation about pragma statements.

Dave.
--
To reply, please remove the no-spam bit from my email address Received on Tue Jun 23 1998 - 11:04:31 CDT