From vgorbounov@simplyengineering.com Mon, 23 Jul 2001 10:57:29 -0700 From: Vadim Gorbounov Date: Mon, 23 Jul 2001 10:57:29 -0700 Subject: RE: Can't code for s.... peanuts Message-ID: MIME-Version: 1.0 Content-Type: text/plain Hi, Michael,   This happens because you dont specify this EXECUTE as function call, try this: declare   b boolean; begin   b := check_for_name('Cruise'); end; /   HTH Vadim Gorbounov Brainbench master of Oracle DBA www.brainench.com     -----Original Message-----From: oqij4ngfcrweuh001@sneakemail.com [mailto:oqij4ngfcrweuh001@sneakemail.com]Sent: Monday, July 23, 2001 2:27 PMTo: Multiple recipients of list ORACLE-LSubject: 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.