PLS-00231: [message #654837] |
Mon, 15 August 2016 15:26 |
desmond30
Messages: 41 Registered: November 2009
|
Member |
|
|
I have code like following
I am declaring a function in package specification, and using the same function in a cursor in the package spec
i am getting PLS-00231: function may not be used in SQL.
How do I get around this ? As a good programming practice, I know having stand alone functions is bad...
CREATE OR REPLACE PACKAGE mypkg
IS
FUNCTION myfunc(p1 in varchar2,p2 number)
return NUMBER;
cursor c1 is
select
myfunc(a.col1, a.col2) calculate_col
from tabl a;
end my pkg;
PLS-00231: function myfunc may not be used in SQL
|
|
|
|
Re: PLS-00231: [message #654839 is a reply to message #654838] |
Mon, 15 August 2016 15:46 |
desmond30
Messages: 41 Registered: November 2009
|
Member |
|
|
if i declare that function as a stand alone ? no, it gets me one row only, if not, i have exception when others then return 1
|
|
|
|
Re: PLS-00231: [message #654842 is a reply to message #654837] |
Mon, 15 August 2016 17:07 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you are going to use a packaged function in a sql select statement, then you need to preface the function with the package name (mypkg.myfunc). Otherwise, it looks for a standalone function with that name. Please see the demonstration below. I don't know what your overall goal is, so I am just providing enough code to show that what you asked can be done, not recommending it as a method for anything.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE tabl
2 (col1 VARCHAR2(5),
3 col2 NUMBER)
4 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO tabl VALUES ('3+2', 4)
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO tabl VALUES ('5-2', 4)
2 /
1 row created.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE mypkg
2 IS
3 FUNCTION myfunc
4 (p1 in varchar2,
5 p2 number)
6 return NUMBER;
7 cursor c1 is
8 select mypkg.myfunc(a.col1, a.col2) calculate_col
9 from tabl a;
10 PROCEDURE test_proc;
11 end mypkg;
12 /
Package created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PACKAGE BODY mypkg
2 IS
3 FUNCTION myfunc
4 (p1 in varchar2,
5 p2 number)
6 return NUMBER
7 IS
8 v_result NUMBER;
9 BEGIN
10 EXECUTE IMMEDIATE
11 'SELECT (' || p1 || ')*' || p2 || ' FROM DUAL'
12 INTO v_result;
13 RETURN v_result;
14 END myfunc;
15 PROCEDURE test_proc
16 IS
17 BEGIN
18 FOR r1 IN c1 LOOP
19 DBMS_OUTPUT.PUT_LINE (r1.calculate_col);
20 END LOOP;
21 END test_proc;
22 END mypkg;
23 /
Package body created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> EXEC mypkg.test_proc
20
12
PL/SQL procedure successfully completed.
|
|
|
|
Re: PLS-00231: [message #654888 is a reply to message #654842] |
Tue, 16 August 2016 07:40 |
desmond30
Messages: 41 Registered: November 2009
|
Member |
|
|
Thank you, it worked, I did not include packagename.function_name in the package hence the error.
The overall goal was to compile the package spec and body with no errors.
|
|
|
Re: PLS-00231: [message #654895 is a reply to message #654888] |
Tue, 16 August 2016 08:08 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:The overall goal was to compile the package spec and body with no errors.
Is it? Most people have the goal to always have errors during compilation of their packages.
|
|
|