Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00231:
PLS-00231: [message #654837] Mon, 15 August 2016 15:26 Go to next message
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 #654838 is a reply to message #654837] Mon, 15 August 2016 15:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am declaring a function in package specification, and using the same function in a cursor in the package spec
do not do that.

BTW, what happens when select myfunc(a.col1, a.col2) calculate_col from tabl a; returns more than 1 row?
Re: PLS-00231: [message #654839 is a reply to message #654838] Mon, 15 August 2016 15:46 Go to previous messageGo to next message
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 #654840 is a reply to message #654839] Mon, 15 August 2016 15:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
desmond30 wrote on Mon, 15 August 2016 13:46
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
since no WHERE clause exists, one row is returned when only one rows is contained in the table.
When 2 or more rows exist in the table, all the rows are returned.

What problem are you really trying to solve?

How will I know when correct answer has been posted here?
Re: PLS-00231: [message #654842 is a reply to message #654837] Mon, 15 August 2016 17:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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 #654844 is a reply to message #654837] Tue, 16 August 2016 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you want accurate help you MUST copy and paste the whole code so we can reproduce what you did and got.

Re: PLS-00231: [message #654888 is a reply to message #654842] Tue, 16 August 2016 07:40 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68006
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.

Previous Topic: Update or create function paramater
Next Topic: keep only numeric values in a string
Goto Forum:
  


Current Time: Mon Oct 25 20:17:49 CDT 2021