Re: Can't code for s.... peanuts

That's done the trick, thanks Shailesh... I can't believe I missed that :)

Cheers,
Michael.

• Original Message ----- <XXXXXXXXXXXXXXXXXXXXXXXXX> To: <XXXXXXXXXXXXXXXXX> Sent: Tuesday, July 24, 2001 6:56 AM

> 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.
> ----- Original Message ----- From: oqij4ngfcrweuh001_at_sneakemail.com
> XXXXXXXXXXXXXXXXXXXXXXXX <mailto:oqij4ngfcrweuh001_at_sneakemail.com
> XXXXXXXXXXXXXXXXXXXXXXXX> To: XXXXXXXXXXXXXXXXX <mailto:XXXXXXXXXXXXXXXXX>
> Sent: Tuesday, July 24, 2001 4:27 AM Subject: Can't code for s.... peanuts
> Hi again everybody,
> I'll try to be as descriptive as possible, sorry if I leave anything
> important out.
> I have the following function which I'm compiling using PL/SQL
> Developer (tnx Djordje).
> CREATE OR REPLACE FUNCTION check_for_name (
> p_name_to_check VARCHAR)
>
> 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).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: oqij4ngfcrweuh001_at_sneakemail.com

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).