Re: PL/SQL Function Name Declaration

kong,

you may not need a package for what you are doing (see below)

you are experiencing normal behavior (i.e., that is how it's designed to work) when you create a function in a package body but don't declare it in the spec.

no matter how much you 'don't want to' include it in the spec, the rules are that you must if you want it to be accessible outside of the package.

however, if all you want to do is create a stand-alone function that can be called from SQL, just create it outside a package using CREATE OR REPLACE FUNCTION ... also, you may want to read up a bit on packages and what additionally functionality they provide compared to stand-alone functions and procedures

• mcs

"kong" <kongkong99_at_yahoo.com> wrote in message news:cc50806d.0405191918.44476eda_at_posting.google.com...
| 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 Fri May 21 2004 - 05:43:27 CDT