From oqij4ngfcrweuh001@sneakemail.com Mon, 23 Jul 2001 11:57:58 -0700 From: "" Date: Mon, 23 Jul 2001 11:57:58 -0700 Subject: Re: Can't code for s.... peanuts Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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 ISv_data VARCHAR2(20);v_return_value VARCHAR2(20);BEGINSELECT lname INTO v_data FROM namesWHERE lname = p_name_to_check;RETURN 'Found';EXCEPTIONWHEN 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 ISCURSOR c_namestbl ISSELECT lname, fnameFROM namesWHERE lname = p_name_to_check;v_return_value VARCHAR2(20);BEGINOPEN c_namestbl;IF c_namestbl%NOTFOUND = TRUE THENv_return_value := 'Not here';ELSIF C_namestbl%FOUND = FALSE THENv_return_value := 'In Here'; END IF;CLOSE c_namestbl;RETURN v_return_value;EXCEPTIONWHEN 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@sneakemail.com XXXXXXXXXXXXXXXXXXXXXXXX To: 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 ISCURSOR c_namestbl ISSELECT lnameFROM namesWHERE lname = p_name_to_check;v_return_value BOOLEAN;BEGINOPEN c_namestbl;IF c_namestbl%NOTFOUND THENv_return_value := FALSE;ELSIF C_namestbl%FOUND THENv_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                WillisSalma                HayekTom                  CruiseElle                 McPherson4 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 undefinedORA-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.