Forum Stats

  • 3,783,343 Users
  • 2,254,760 Discussions
  • 7,880,369 Comments

Discussions

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

umitkilic
umitkilic Member Posts: 3
edited May 6, 2015 7:02PM in SQL & PL/SQL

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 İS 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 MISTAKE? I COULDNT FIND.

(i tried to translate all of warnings and errors from my language.I hope I did it right. )

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,645 Red Diamond
    edited May 6, 2015 10:07AM

    How are you creating this function?  It works ok when I try it, direct copy of your code...

    SQL> create table customer as select dummy customer_city from dual;
    
    Table created.
    
    SQL> ed
    Wrote file afiedt.buf   1  CREATE OR REPLACE FUNCTION customer_city_function(city_in IN VARCHAR2)
      2  RETURN NUMBER
      3  AS
      4    number_cus NUMBER := 0;
      5    CURSOR cus_cur IS
      6      SELECT COUNT(*)
      7      FROM customer
      8      WHERE customer_city=city_in;
      9  BEGIN
    10    IF city_in 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;
    SQL> / Function created. SQL> select customer_city_function('X') from dual; CUSTOMER_CITY_FUNCTION('X')
    ---------------------------
                              1 1 row selected.

    What are you doing differently?  Are you creating it in SQL*Plus? or another tool?  And what oracle database version are you using?

  • Bhavesh Tailor
    Bhavesh Tailor Member Posts: 52 Red Ribbon
    edited May 6, 2015 10:11AM

    It worked for me too.

    SQL> create table customer (customer_city varchar2(20));

    Table created.

    SQL> CREATE OR REPLACE FUNCTION customer_city_function(city_in IN VARCHAR2)

      2  RETURN NUMBER

      3  AS

      4    number_cus NUMBER := 0;

      5

      6    CURSOR cus_cur IS

      7      SELECT COUNT(*)

      8      FROM customer

      9      WHERE customer_city=city_in;

    10

    11  BEGIN

    12

    13    IF city_in IS NOT NULL

    14    THEN

    15

    16      OPEN cus_cur;

    17

    18      FETCH cus_cur INTO number_cus;

    19

    20      CLOSE cus_cur;

    21

    22    END IF;

    23

    24  RETURN number_cus;

    25

    26  END;

    27  /

    Function created.

    SQL> select customer_city_function('charlotte') from dual;

    CUSTOMER_CITY_FUNCTION('CHARLOTTE')

    -----------------------------------

                                      0

    1 row selected.

  • kendenny
    kendenny Member Posts: 1,269
    edited May 6, 2015 10:15AM

    Works for me

    SQL> CREATE TABLE customer

      2    (cust_id   INTEGER

      3    ,cust_name VARCHAR2(50)

      4    ,cust_city VARCHAR2(50)

      5    );

    Table created

    SQL> CREATE OR REPLACE FUNCTION customer_city_function(city_in IN VARCHAR2)

      2  RETURN NUMBER

      3  AS

      4    number_cus NUMBER := 0;

      5    CURSOR cus_cur IS

      6      SELECT COUNT(*)

      7      FROM customer

      8      WHERE cust_city=city_in;

      9  BEGIN

    10    IF city_in 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> select customer_city_function('New York') from dual;

    CUSTOMER_CITY_FUNCTION('NEWYOR

    ------------------------------

                                 0

  • umitkilic
    umitkilic Member Posts: 3
    edited May 6, 2015 5:21PM

    @BluShadow @kendenny @Bhavesh Tailor I'm using ORACLE SQL DEVELOPER for writing and compiling codes. Maybe there is a problem about settings of compiler. But i still have the same problem. 

    here is how to appear the code => http://imgur.com/GBk9heK

    and here is how to calling it => http://imgur.com/SfAyBR4


    i am using two different document for writing code and calling the function. For example : first document is "function.sql" and my code is in it, another one is "calling_function.sql" and my calling code is in it.

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited May 6, 2015 5:57PM

    Not very sure what you want, but remove the last "/" , Press Ctrl+A and then press F5 to compile first and the same steps to run the code.

  • umitkilic
    umitkilic Member Posts: 3
    edited May 6, 2015 6:00PM

    i am using ORACLE sqldeveloper and Version is 4.0.3.16

  • Unknown
    edited May 6, 2015 7:02PM
    umitkilic wrote:
    
    i am using ORACLE sqldeveloper and Version is 4.0.3.16
    

    This forum is ONLY for SQL and PL/SQL questions.

    Questions on how to use Sql dev should be posted in the Sql Developer forum.

This discussion has been closed.