PLS-00231: Function "calculate_1" may not be used in SQL

[Florian Reiser]

Hello,

I try to create a packaged set returning function.
When trying to compile the package header, I get the PLS-00231 error.

I've anonymized the source because of confidentiallity.
The relevant source of the package header is as follows:

create or replace
PACKAGE test_1 AS

-- Returns value3 if criteria match, else 0.0
FUNCTION calculate_1(value1 IN CHAR(5 CHAR),
value2 IN CHAR(5 CHAR),
value3 IN NUMBER)
RETURN NUMBER;

-- Cursor doing the calculations
CURSOR test_cursor (datum IN DATE) IS
SELECT
[cut]
SUM(caculate_1(value1, value2, value3)) "header"
FROM
[else cut];

END test_1;

The function calculate_1 simply does some comparisons. If these match,
then it returns value3, else 0.0

When I try to compile this package header, then Oracle gives my an error
PLS-00231
It's not clear to me, why I cant use this function in the sql, since the
database is not altered in it.
Do I have to use some pragma instructions?

OS: W2K
Oracle 10gR2

With kind regards

Florian Reiser

--
http://www.ra-bc.de
RA Unternehmensberatung
Führen durch präzise Daten

[sybrandb]

> --http://www.ra-bc.de

> RA Unternehmensberatung
> Führen durch präzise Daten

You can't use length specifications in formal parameter declarations.
One would have expected a different message though.
Strange thing is you seem to have re-invented the CASE statement. Why
can't you use this one?

--
Sybrand Bakker
Senior Oracle DBA

[Florian Reiser]

"sybrandb" <sybr...@gmail.com> schrieb im Newsbeitrag
news:1185361352.4...@q75g2000hsh.googlegroups.com...

Hello Sybrand,

thanks for your reply.
Indeed I could have done it using the CASE-statement.
But the same logic is used several times in the query.
So I wanted to externalize it in a SP. Then I could
change it with less effort, if necessary at some time in the future.