When ever you use a select into statement and when no data is found it
raises an exception NO_DATA_FOUND and jumps out of the block,here after
retrn statement.

You should add

EXCEPTION when no_data_found then
return 0;


after return 2


You may also wan tto handle TOO_MANY_ROWS exception.

Sandeep



-----Original Message-----
From: Kelly Grigg [mailto:[Email Address Removed] Thursday, March 28, 2002 3:02 PM
To: LazyDBA.com Discussion
Subject: Re: Why doesn't function return a value here?


Here is what I get when I run this...(I just rename the colume to 'joe' to
make it shorter named)

With a value that does put a value into the v_meeting_id variable:

SQL> select generic_structure.check_date(to_date('MAR 27, 2002 05:30
AM','MON DD, YYYY HH:MI AM'),200) joe from dual;

JOE
----------
2

Now, if I run the same thing with a room number change to one that does not
exist in the table being tested..and therefore puts a null into the
variable,
the hardcoded return value of 2 is NOT returned here:

SQL> select generic_structure.check_date(to_date('MAR 27, 2002 05:30
AM','MON DD, YYYY HH:MI AM'),199) joe from dual;

JOE
----------


I don't understand this...

One more thing...I have done the function with the NVL and it does the same
thing. Here it is with the NVL in it:

FUNCTION check_date (v_date IN DATE, v_room_id IN NUMBER) RETURN NUMBER IS

v_meeting_id NUMBER;

BEGIN

SELECT NVL(a.meeting_id,0) INTO v_meeting_id from
booked_meeting_room a, meeting b
WHERE v_date between b.begin_date and b.end_date
AND v_room_id = a.room_id
AND a.meeting_id = b.meeting_id;


RETURN 2;

END check_date;

I thought this would clear it up, but, it hasn't. I still don't understand,
if the parameters are fed correctly...this function SHOULD return 2
regardless
of what the select into puts into the variable...???

Thanks..please keep the ideas coming...

Kelly


On Thu, Mar 28, 2002 at 02:50:10PM -0500, after pounding the keys randomly,
Alok.[Email Address Removed] came up with....
> How are you using this function, try calling like this:
> sql>select check_date (v_date IN DATE, v_room_id IN NUMBER) from dual;
>
> Regards,
> Alok Kumar
>

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

A computer lets you make more mistakes faster than any other invention,
With the possible exceptions of handguns and tequila.

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

--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page