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

"sybrandb" <sybrandb_at_gmail.com> schrieb im Newsbeitrag news:1185361352.400004.114090_at_q75g2000hsh.googlegroups.com... On Jul 24, 11:30 am, "Florian Reiser" <florian.rei..._at_ra-bc.de> wrote:
> 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

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


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.

With kind regards
Florian Reiser