Forum Stats

  • 3,759,185 Users
  • 2,251,510 Discussions
  • 7,870,528 Comments

Discussions

Function return issue

2943907
2943907 Member Posts: 9
edited May 6, 2015 4:52AM in SQL & PL/SQL

Hello Gurus,

My requirement is to retrieve through function: if i pass string as parameter, it should return number value. I have tried below one, but it doesn't working.

CREATE OR REPLACE FUNCTION Get_Items_func(ItemName IN varchar(2000))
RETURN NUMBER IS
ItmID number;
BEGIN
SELECT ItemID into ItmID
FROM Items WHERE Item_Name = ItemName;
RETURN (ItmID);
END;

Please help me with this.

Thanks in Advance.

Tagged:
John Stegeman2943907
«1

Answers

  • chris227
    chris227 Member Posts: 3,516 Bronze Crown
    edited May 5, 2015 11:21AM

    What does not work?

  • Marwim
    Marwim Member Posts: 3,644 Gold Trophy
    edited May 5, 2015 11:25AM

    Your parametername is the same as the column name. This is bad practice.

    You have to qualify them in your select

    WHERE Items.Item_Name = Get_Items_func.ItemName;

    Regards

    Marcus

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 5, 2015 11:29AM

    When I looked in the documentation for the error code "doesn't working," I couldn't find it.

    So, if you really want help, you'll need to be much more descriptive than "it doesn't work." It would also be immensely helpful if you gave us create table scripts, some insert scripts with sample data, and demonstrated it "not working" together with the output you expect to get.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    edited May 5, 2015 12:10PM

    Hi,

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.  Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.

    In this case, it sounds like you need to post CREATE TABLE and INSERT statements for a table of sample inputs to the function, and the results of what you'd like to get from

    SELECT  itemname
    ,       get_items_func (itemname)  AS f
    FROM    sample_data;
    
    
    

    If the function depends on other tables, such as items, then you need to post CREATE TABLE and INSERT statements for those tables, too (relevant rows and columns only).

    Always say which version of Oracle you're using (for example, 11.2.0.2.0).

    See the forum FAQ:

  • 2943907
    2943907 Member Posts: 9
    edited May 5, 2015 1:01PM

    Hi,

    thanks for the reply.

    I am getting following exception when i execute the above function.

    • Error(1,62): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue. 
  • 2943907
    2943907 Member Posts: 9
    edited May 5, 2015 1:01PM

    Hi,

    thanks for the reply.

    I am getting following exception when i execute the above function.

    • Error(1,62): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

    Do i need to typecast anything.

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited May 5, 2015 1:02PM

    return is not a function, so lose the () around the value you are trying to return.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    edited May 5, 2015 1:30PM

    Hi,

    cb5ece7a-cc08-48cc-ba72-a257a47413a7 wrote:
    
    Hi,
    thanks for the reply.
    I am getting following exception when i execute the above function.
    
    
    Error(1,62): PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.
    
    Do i need to typecast anything.
    
    
    

    So, the error is on line 1, character position 62 of this function:

    CREATE OR REPLACE FUNCTION Get_Items_func(ItemName IN varchar(2000))

    RETURN NUMBER IS

    ItmID number;

    BEGIN

    SELECT ItemID into ItmID

    FROM Items WHERE Item_Name = ItemName;

    RETURN (ItmID);

    END;

    The problem is trying to give a size to the argument.  The caller will specify a size for the arguments; in the function, all you need to specify is the data type; for example:

    CREATE OR REPLACE FUNCTION Get_Items_func (ItemName   IN   varchar2) 

    Is there any reason to use VARCHAR rather than VARCHAR2?

    As John mentioned, you don't need parentheses around ItmID in the RETURN statement, but they're not doing any harm  (You would get a separate error message pointing to line 7 if that were a problem.)  In a RETURN statement like this:

    RETURN  2 * (x - y);
    
    

    you need the parentheses.  In the function you posted, you don't need them, but you can use them anyway if (unlike me) you think they're helpful.

    John Stegeman2943907
  • 2943907
    2943907 Member Posts: 9
    edited May 6, 2015 2:14AM

    Hi Frank Kulash,

                         It worked fine. Thanks for your help.

  • Ashokram
    Ashokram Member Posts: 61 Red Ribbon
    edited May 6, 2015 3:59AM

    When declaring Datatype of Parameters IN, OUT & IN OUT, the size should not be defined.

    CREATE OR REPLACE FUNCTION Get_Items_func (p_ItemName IN varchar)

    RETURN NUMBER

    IS

    .......

This discussion has been closed.