| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can't code for s.... peanuts
Michael,
The one with cursor is not working beacuse you have not fetched the cursor and are trying to evaluate whether it was found or not. Try this
create oR REPLACE FUNCTION check_for_name ( p_name_to_check VARCHAR)
RETURN VARCHAR2 IS
CURSOR c_namestbl IS
SELECT lname, fname
FROM names
WHERE lname = p_name_to_check;
v_return_value VARCHAR2(20);
vLName VARCHAR2(30);
vFName VARCHAR2(30);
BEGIN
OPEN c_namestbl;
FETCH c_namestbl INTO vLName, vFName;
IF c_namestbl%NOTFOUND THEN
v_return_value := 'Not here';
ELSIF C_namestbl%FOUND THEN
v_return_value := 'In Here';
END IF;
CLOSE c_namestbl;
RETURN v_return_value;
EXCEPTION
WHEN no_data_found THEN RETURN 'No Matches';
END check_for_name;
Shailesh
To all who offered there help :
A big thanks, I've got this about 70% licked now :)
The winning combination was :
CREATE OR REPLACE FUNCTION check_for_name2 (
p_name_to_check VARCHAR)
RETURN VARCHAR2 IS
v_data VARCHAR2(20);
v_return_value VARCHAR2(20);
BEGIN
SELECT lname
INTO v_data
FROM names
WHERE lname = p_name_to_check;
RETURN 'Found';
EXCEPTION
WHEN no_data_found THEN RETURN 'No Matches';
END check_for_name2;
Thanks to Lisa for the help there. However, the attempts to do the same with
the cursor continue to fail:
CREATE OR REPLACE FUNCTION check_for_name (
p_name_to_check VARCHAR)
RETURN VARCHAR2 IS
CURSOR c_namestbl IS
SELECT lname, fname
FROM names
WHERE lname = p_name_to_check;
v_return_value VARCHAR2(20);
BEGIN
OPEN c_namestbl;
IF c_namestbl%NOTFOUND = TRUE THEN
v_return_value := 'Not here';
ELSIF C_namestbl%FOUND = FALSE THEN
v_return_value := 'In Here';
END IF;
CLOSE c_namestbl;
RETURN v_return_value;
EXCEPTION
WHEN no_data_found THEN RETURN 'No Matches';
END check_for_name;
While I can get it to execute with "Select check_for_name('Cruise') FROM
dual;" it returns no data, but looks like it wants to:
SQL> SELECT check_for_name('Cruise') FROM dual;
CHECK_FOR_NAME('CRUISE')
SQL>
If anybody has any ideas on that one, thankyou. But try to structure your
answer in the form of 'hints' that way I get to do something instead of have
type what I'm told... can't learn that way.
Thanks again to all for your responses.
RETURN BOOLEAN IS
CURSOR c_namestbl IS
SELECT lname
FROM names
WHERE lname = p_name_to_check;
v_return_value BOOLEAN;
BEGIN
OPEN c_namestbl;
IF c_namestbl%NOTFOUND THEN
v_return_value := FALSE;
ELSIF C_namestbl%FOUND THEN
v_return_value := TRUE;
END IF;
RETURN v_return_value;
CLOSE c_namestbl;
END check_for_name;
It compiles without errors (now).
The table 'names' is built like this :
SVRMGR> DESCRIBE names;
Column Name Null? Type
------------------------------ -------- ----
FNAME VARCHAR2(20)
LNAME VARCHAR2(20)
SVRMGR>
With data like this :
SVRMGR> SELECT * FROM names;
FNAME LNAME
-------------------- --------------------
Bruce Willis
Salma Hayek
Tom Cruise
Elle McPherson
4 rows selected.
But, whenever I try to do this :
EXECUTE check_for_name('Cruise');
so I can run the function from PL/SQL Developer I get the following
error :
"ORA 0900 - Invalid SQL Statment"
However, If I execute the same statement from SQL*Plus or svrmgrl I
get a differant error :
SVRMGR> EXECUTE check_for_name('Cruise');
check_for_name('Cruise');
*
ORA-06550: line 2, column 2:
PLS-00221: 'CHECK_FOR_NAME' is not a procedure or is undefined
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
I'm running Oracle Enterprise Edition 8.1.5.0.0 on Windows 2000.
If that's not enough info, please let me know and I will supply
whatever I can.
Thanks for reading this far :)
Michael.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Yadav, Shailesh
INET: NDASY3_at_labor.state.ny.us
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Jul 23 2001 - 15:01:39 CDT
 |
 |