Function return issue

2943907

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.

chris227

What does not work?

Marwim

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

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

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

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

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

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

Frank Kulash

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.

2943907

Hi Frank Kulash,

                     It worked fine. Thanks for your help.

Ashokram

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

.......

Billy Verreynne

John Stegeman wrote:

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

I always use brackets for the argument for a return statement. In Ada it is optional - but in other languages (like Pascal) it is not.

It also reads easier in my view with brackets - as it encloses the contents that need to be returned. But then Pascal is also mostly the language I judged others by. And the language other languages (like Ada) have their roots in.