-
Handling exception no_data_found
I've just started using oracle but am having trouble with syntax when handling exceptions.
I am using a function similiar to this basic one but it's not quite working saying "Function returned without a value". It should be returning values though.
I'm pretty sure it has something to do with the exception part..any ideas?
CREATE check (vstreet IN CHAR)
RETURN CHAR
IS v_test VARCHAR2(20);
BEGIN
SELECT INTO v_test
FROM house
WHERE street = vstreet;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_test:= 'notfound';
RETURN (v_test);
END;
-
Hi
CREATE check (vstreet IN CHAR)
RETURN CHAR
IS v_test VARCHAR2(20);
BEGIN
SELECT INTO v_test
FROM house
WHERE street = vstreet;
return v_test; --there should be return coded here
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_test:= 'notfound';
RETURN (v_test);
END;
ideally it should be something like this
CREATE or replace function check1(vstreet IN CHAR)
RETURN VARCHAR2 is
v_test VARCHAR2(20);
BEGIN
SELECT account_idx INTO v_test
FROM account
WHERE account_idx=100;
return v_test;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_test:= 'notfound';
RETURN (v_test);
END;
go here fro some nice tutorials
http://www.hot-oracle.com/index.html
regards
Hrishy
Last edited by hrishy; 03-18-2004 at 08:44 AM.
-
Or ...
CREATE or replace function check1(vstreet IN CHAR)
RETURN VARCHAR2 is
v_test VARCHAR2(20);
CURSOR c1 IS
SELECT account_idx
FROM account
WHERE account_idx=100;
BEGIN
OPEN c1;
FETCH c1 INTO v_test;
IF c1%FOUND THEN
RETURN v_test;
ELSE
RETURN 'Not Found';
END IF;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
RETURN 'Error in Function.';
END;
-
Originally posted by JMac
Or ...
CREATE or replace function check1(vstreet IN CHAR)
RETURN VARCHAR2 is
v_test VARCHAR2(20);
CURSOR c1 IS
SELECT account_idx
FROM account
WHERE account_idx=100;
BEGIN
OPEN c1;
FETCH c1 INTO v_test;
IF c1%FOUND THEN
RETURN v_test;
ELSE
RETURN 'Not Found';
END IF;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
RETURN 'Error in Function.';
END;
Yuk.
Except for just returning the 'Not Found' textdirectly .. I seeno reason for ...
Code:
v_test:= 'notfound';
RETURN (v_test);
... when ... would do just as well.
-
I was merely playing with the previous function and not supplying a definitive answer, O Wicked Witch of the West!
-
awesome thanks guys. Thats my first hurdle overcome
-
Originally posted by JMac
Or ...
CREATE or replace function check1(vstreet IN CHAR)
RETURN VARCHAR2 is
v_test VARCHAR2(20);
CURSOR c1 IS
SELECT account_idx
FROM account
WHERE account_idx=100;
BEGIN
OPEN c1;
FETCH c1 INTO v_test;
IF c1%FOUND THEN
RETURN v_test;
ELSE
RETURN 'Not Found';
END IF;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
RETURN 'Error in Function.';
END;
What's the point of using a cursor at all?!
-
Originally posted by pando
What's the point of using a cursor at all?!
Well, if the function is supposed to return something from the database (account_idx from table account in JMac's case), then there is no other way but to use a cursor. Now, whether you use explicit or implicit cursor, that's for another topic...
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
well what I mean is why you cannot use a simple select into, if there are more than one rows returns then just use rownum < 2
-
One more question I have.
Can a function return more than one variable?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|