Re: PL/SQL Function Name Declaration

Hi IB,

Thanks for ur effort.
But actually i want to use this function within my sql statement.

Which is like the "test" function you created, i want to use it in my sql. E.g.:select * from tableA where test(name)='abc'

Back to the point:
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".

regards,
kong

IB <irb61_at_yahoo.com> wrote in message news:<WlLqc.36$j04.114_at_news.oracle.com>...
> 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 - 22:18:11 CDT