CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperstored-functionsora-06550
umitkilic
umitkilic

Reputation: 355

PL/SQL : i have a function but there is an error : "in a procedure,RETURN can not contain an expression"

Here is my code:

CREATE OR REPLACE FUNCTION customer_city_function(city_in IN VARCHAR2)
RETURN NUMBER
AS
  number_cus NUMBER := 0;

  CURSOR cus_cur IS
    SELECT COUNT(*)
      FROM customer
     WHERE customer_city = city_in;
BEGIN
  IF city_in IS NOT NULL THEN
    OPEN cus_cur;
    FETCH cus_cur INTO number_cus;
    CLOSE cus_cur;
  END IF;

  RETURN number_cus;
END;
/

and here is warnings:

Error starting at line : 1 in command -
CREATE OR REPLACE FUNCTION customer_city_function(city_in IN VARCHAR2)
RETURN NUMBER
AS
  number_cus NUMBER := 0
Error report -
SQL Command: functıon CUSTOMER_CITY_FUNCTION
Failed: Warning: executing is completed with a warning


Error starting at line : 5 in command -
CURSOR cur_cur IS
Error report -
Unknown Command


Error starting at line : 6 in command -
SELECT COUNT(*)
    FROM costumer
    WHERE customer_city=city_in
Error at Command Line : 8 Column : 25
Error report -
SQL Error: ORA-00904: "CITY_IN": undefined variable
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


Error starting at line : 9 in command -
BEGIN
  IF city_in IS NOT NULL
  THEN
    OPEN cus_cur;
    FETCH cus_cur INTO number_cus;
    CLOSE cus_cur;
  END IF;
RETURN (number_cus);
END;

Error report -
ORA-06550: row 2, column 6:
PLS-00201: 'CITY_IN' variable should been defined
ORA-06550: row 2, column 3:
PL/SQL: Statement ignored
ORA-06550: row 8, column 1:
PLS-00372: in a procedure, RETURN can not contain an expression
ORA-06550: row 8, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Where is my mistake? I can't find it, it doesn't make any sense. (I translated this warning message from my language. I hope I did it right.)

I have just tried it in Command Window and it works. Why doensn't it work in Oracle SQL Developer sql worksheet?

Upvotes: 2

Views: 13422

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

There is nothing wrong with your posted code. The issue might be with your client or the way you are compiling the code.

As you have mentioned PL/SQL Developer in the tags, it might be possible that you have some extra characters in the SQL Worksheet and you are compiling the function as a script, thus the compiler finds it erroneous.

Here is a demo in SQL*Plus, and there is no error:

SQL> CREATE OR REPLACE FUNCTION customer_city_function(i_deptno IN number)
  2  RETURN NUMBER
  3  AS
  4  number_cus NUMBER := 0;
  5  CURSOR cus_cur IS
  6  SELECT COUNT(*)
  7  FROM emp
  8  WHERE deptno=i_deptno;
  9  BEGIN
 10    IF i_deptno IS NOT NULL
 11    THEN
 12    OPEN cus_cur;
 13    FETCH cus_cur INTO number_cus;
 14    CLOSE cus_cur;
 15  END IF;
 16  RETURN number_cus;
 17  END;
 18  /

Function created.

SQL> sho err
No errors.
SQL> SELECT customer_city_function(10) FROM DUAL;

CUSTOMER_CITY_FUNCTION(10)
--------------------------
                         3

SQL>

The only difference in my code is that I have used EMP table instead of CUSTOMERS table and the input parameter is DEPTNO instead of CITY_IN. Rest everything is same and function compiles and executes without any errors.

Upvotes: 2

Related Questions