SQL & PL/SQL

Morning you all,

Please, don't ask me about not making this kind of function because I've cut the parts that are not important relating to my question. Thanks.

Package:

function PL_Fun (

  P_COD1  IN VARCHAR2,

  P_COD2 IN NUMBER,

  P_COD3 IN NUMBER)  RETURN VARCHAR2;

PROCEDURE PL_Prod (P_CURSOR1     OUT C_RETURN);

Package Body:

Function PL_Fun (

   P_COD1      IN VARCHAR2,

   P_COD2      IN NUMBER,

   P_COD3      IN NUMBER) RETURN VARCHAR2 AS

  SALIDA     VARCHAR2(3000);

BEGIN     

   salida:='XXXXX1;YYYYY1; S |

               XXXXX2;YYYYY2; S |

               XXXXX3;YYYYY3; S |

               XXXXX41;YYYYY4; S |' ; 

   RETURN SALIDA;

END PL_FUN;

PROCEDURE PL_Prod ( P_CURSOR1 OUT C_RETURN) IS

  nothing EXCEPTION;

  V_CODERROR varchar2(3);

  V_DESERROR varchar2(300);

BEGIN

    OPEN P_CURSOR1 FOR

            SELECT PL_Fun (table1.v_cod1,

                                       table1.v_cod2,

                                       table1.v_cod3) as descrip

               FROM TABLE1;

EXCEPTION

      WHEN nothing THEN

         V_CODERROR   := '001';

         V_DESERROR   := 'Nothing';

         RAISE_APPLICATION_ERROR (-20000

                                , V_CODERROR || ':' || V_DESERROR

                                 );

      WHEN OTHERS THEN

         V_CODERROR   := 'SQL';

         V_DESERROR   :=

            'ERROR ON PL_Fun (' || SQLCODE || ') ' || SUBSTR (SQLERRM

                                                                                   ,1

                                                                                   ,300

                                                                                    );

         RAISE_APPLICATION_ERROR (-20000

                                , V_CODERROR || ':' || V_DESERROR );

   END PL_Prod;

Error:

Error: PLS-00231: function 'PL_Fun' may not be used in SQL

Text: , PL_Fun (P_COD_1, P_COD_2, P_COD_3) as descrip

Error: PL/SQL: ORA-00904: : not valid identifier

Text: , PL_Fun (P_COD_1, P_COD_2, P_COD_3) as descrip

Error: PLS-00323: subprogram or cursor 'PL_Fun' is declared in a package specification and must be defined in the package body

Text: );

What did I try?

As Ask Tom reffers, I have changed the postion at the package body,

from:

   Function PL_Fun (); 

   PROCEDURE PL_Prod;

to:

   PROCEDURE PL_Prod;

   Function PL_Fun (); 

But it didn't work; I have the same problem.

And, as I have read on another forum, I have used the PRAGMA RESTRICT_REFERENCES as the following, but it didn't work either:

CREATE PACKAGE xxxx AS

Function PL_Fun (

   P_COD1      IN VARCHAR2,

   P_COD2      IN NUMBER,

   P_COD3      IN NUMBER) RETURN VARCHAR2;

  PRAGMA RESTRICT_REFERENCES (PL_Fun, WNDS, WNPS);

PROCEDURE PL_Prod ( P_CURSOR1 OUT C_RETURN);

END xxxx;

Any suggestions?

Thank you guys.