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

Mon, 23 Jul 2001 12:39:27 -0700

You are not performing a FETCH after you open your cursor, and you are falling through your IF statements.
 
Try
 
loc_fname names.fname%type;                    <======
loc_lname names.lname%type;                    <======
BEGIN
OPEN c_namestbl;
  fetch c_namestbl into loc_lname, loc_fname;    <=======
IF c_namestbl%NOTFOUND = TRUE THEN
v_return_value := 'Not here';
ELSIF C_namestbl%FOUND = FALSE THEN
v_return_value := 'In Here'; 
ELSE                                                                      <==============
v_return_value := 'fallen down and can not get up';       <==============
END IF;

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 23, 2001 3:52 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Can't code for s.... peanuts

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

 

 

 

Reply via email to