This discussion is archived
8 Replies Latest reply: Sep 10, 2008 8:03 AM by 659130 RSS

Error PLS-00231

32685 Expert
Currently Being Moderated
Hello

In the select statement of a cursor I need to perform some reasonably complex calculations on a variety of columns. I was hoping to use a set of functions defined in a package to perform the calculations, but I am getting the
PLS-00231 function 'string' may not be used in SQL
Error, is the only way round this to take the functions out of the package and have them as separate functions, or will it work if I simply put them in a separate package from the one that contains the cursor?

Cheers

David
  • 1. Re: Error PLS-00231
    27876 Newbie
    Currently Being Moderated
    Try adding the PRAGMA to the function.
    FUNCTION function_name(...) RETURN VARCHAR2 ;
        PRAGMA RESTRICT_REFERENCES(function_name, WNDS, RNDS, WNPS,RNPS);
  • 2. Re: Error PLS-00231
    APC Oracle ACE
    Currently Being Moderated
    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
  • 3. Re: Error PLS-00231
    32685 Expert
    Currently Being Moderated
    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
  • 4. Re: Error PLS-00231
    446518 Newbie
    Currently Being Moderated
    Great!!

    Helped me a lot

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

    Krishna
  • 5. Re: Error PLS-00231
    564378 Newbie
    Currently Being Moderated
    Wow, that cost me some time.
    Thanks a lot, it saved my day.
  • 6. Re: Error PLS-00231
    450441 Journeyer
    Currently Being Moderated
    My god, users who search the forum before posting.

    Surely it's a sign of the End of Days.
  • 7. Re: Error PLS-00231
    Amritpal Newbie
    Currently Being Moderated
    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
  • 8. Re: Error PLS-00231
    659130 Newbie
    Currently Being Moderated
    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