DevOps, CI/CD and Automation

Try adding the PRAGMA to the function.

FUNCTION function_name(...) RETURN VARCHAR2 ;
    PRAGMA RESTRICT_REFERENCES(function_name, WNDS, RNDS, WNPS,RNPS);

There are a number of things it could be.

My #1 candidate is, we can only use functions in SQL statements that are public i.e. declared in a package spec. This is the case even for SQL statements executed within the same Package Body. The reson is that SQL statements are executed by a different engine which can only see publicly declared functions.

Cheers, APC

Hi Andrew

That's exactly it! I moved the functions back to the original package, published them in the spec and it worked. Excellent!

Cheers

David

Great!!

Helped me a lot

Though the message - function XX may not be used in SQL, is not relevent.

Krishna

Wow, that cost me some time.
Thanks a lot, it saved my day.

My god, users who search the forum before posting.

Surely it's a sign of the End of Days.

Thanks a lot, I just Googled the error PLS-00231, the very first link brought me here, and you were spot on. Declare the functions in the Package Spec to make them Global, and that solved the issue.

Thanks.
Amrit

I also have this problem.

Making the function public helps ... but I can not make the function public. This violates the design.

The PRAGMA also says that it must be right behind the function definition AND in the package specification.


Is there a other - a private - way?

(Oracle 8i / PLSQL 8.1.7.4.0)



I also can not split the call from the SQL Statement, because it is part of a cursor loop:

FOR a IN (SELECT asd,
                         asaa,
                         asdasda,
                         h.sssde,
                         HLP_FNC_GET_BLABLUBB(j.adasdassss) dasdasssss
                  FROM   T_fhexxxsadfast h,
                         t_FFasee112w_SA   j
                  WHERE  h.soasde = j.soasde) 
        LOOP

anonymized code.

Edited by: user5828099 on 10.09.2008 07:57

Edited by: user5828099 on 10.09.2008 08:02