Re: PL/SQL Function Name Declaration

I have created a test script and tried running it - it ran successfully. Only thing you need to keep in mind is to declare the function before the procedure or function that calls it. If you do otherwise, it usually gives an error at runtime.
CREATE OR REPLACE package test_i
AS
 PROCEDURE main(p_parameter IN varchar2); END;
/

show errors

CREATE OR REPLACE package body test_i
AS

 FUNCTION test(p_string varchar2)
 RETURN varchar2
 IS
 BEGIN
  dbms_output.put_line(p_string);
  return lower(p_string);
 END; -- function test

 PROCEDURE main(p_parameter IN varchar2
 --Procedure test

               )
IS
l_string varchar2(10);
BEGIN
  l_string:=test(p_parameter);
  dbms_output.put_line('L_ST: '||l_string); END; END;
/
show errors

exec test_i.main('SUPER');
SUPER
L_ST: super

PL/SQL procedure successfully completed.

kong wrote:

>Hi,
>
>I have created a function and coded it within a Package. At first, I
>declared this function name at package body. It prompted error when i
>tried to use this function in a sql (within that package). The error
>is "This function may not be used in sql".
>
>However, it is working fine when i declare the function at package
>specification. May I know why such occurrence happened? Any solutions
>for this as I do not want to declare it at package specification.
>
>Thanks in advance.
>
>
Received on Wed May 19 2004 - 10:34:23 CDT