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

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Mon, 23 Jul 2001 11:09:36 -0700
Message-ID: <F001.003523B2.20010723111139@fatcity.com>

My that's one heck of an email address.

Basic answer here.  A function must return a value.  Put it in an anonymous block, return it's value to a variable and output it.  Then you'll see what you expect.

The other question is why are you using a cursor?  That's not necessary here. 

I'd do something like

SELECT lname FROM names INTO var_name WHERE laname = &var and rownum < 2;

RETURN true;

EXCEPTION
WHEN no_data_found RETURN FALSE;

END;
/

HTH
Lisa Koivu
Clumsy Stumbling Gimp, ex-db developer (and DBA) Ft. Lauderdale, FL, USA

-----Original Message-----

From:   oqij4ngfcrweuh001_at_sneakemail.com [SMTP:oqij4ngfcrweuh001_at_sneakemail.com]
Sent:   Monday, July 23, 2001 2:27 PM
To:     Multiple recipients of list ORACLE-L

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.

 

 

 
Received on Mon Jul 23 2001 - 13:09:36 CDT