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

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 23 Jul 2001 12:39:27 -0700
Message-ID: <F001.003527D3.20010723125605@fatcity.com>

You
are not performing a FETCH after you open your cursor, and you are falling through your IF statements.
<SPAN

class=189292319-23072001> 
<SPAN

class=189292319-23072001>Try
<SPAN

class=189292319-23072001> 
<SPAN

class=189292319-23072001>loc_fname

names.fname%type;                    

<======
<SPAN

class=189292319-23072001>loc_lname

names.lname%type;                    

<======

BEGINOPEN c_namestbl;<SPAN
class=189292319-23072001>  fetch c_namestbl into loc_lname, loc_fname;    <=======
IF
c_namestbl%NOTFOUND = TRUE THENv_return_value := 'Not here'<FONT
size=2>;ELSIF C_namestbl%FOUND = FALSE
THENv_return_value := <FONT color=#0000ff face=Arial size=2>'In Here'<FONT
face=Arial>; <SPAN

class=189292319-23072001>ELSE                                                                      

<==============
<SPAN

class=189292319-23072001>v_return_value := 'fallen down and can not get up';      
<==============

END
IF;
Tom Mercadante <FONT face=Arial
size=2>Oracle Certified Professional

  <FONT face=Tahoma
  size=2>-----Original Message-----From:   oqij4ngfcrweuh001_at_sneakemail.com
  [mailto:oqij4ngfcrweuh001_at_sneakemail.com]Sent: Monday, July 23,   2001 3:52 PMTo: Multiple recipients of list   ORACLE-LSubject: 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   ISv_data VARCHAR2(<FONT color=#0000f0   size=1>20);v_return_value
  VARCHAR2(20<FONT color=#000000
  size=1>);BEGINSELECT lname INTO v_data   FROM namesWHERE lname =
  p_name_to_check;RETURN <FONT color=#0000f0   size=1>'Found'<FONT color=#000000
  size=1>;EXCEPTIONWHEN no_data_found THEN   RETURN 'No Matches'<FONT
  color=#000000 size=1>;END check_for_name2;   Thanks to Lisa for the help there. However, the   attempts to do the same with the cursor continue to fail:<FONT   color=#000000 size=1>
  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(<FONT color=#0000f0   size=1>20<FONT color=#000000
  size=1>);BEGINOPEN c_namestbl;IF
  c_namestbl%NOTFOUND = TRUE THENv_return_value :=   'Not here'<FONT color=#000000
  size=1>;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'<FONT
  color=#000000 size=1>;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;
  <FONT face=Arial

  size=2>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.
  <BLOCKQUOTE
  style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: <A href="mailto:oqij4ngfcrweuh001_at_sneakemail.com [iceic/oracle @ fatcity]" title=XXXXXXXXXXXXXXXXXXXXXXXXX>oqij4ngfcrweuh001_at_sneakemail.com XXXXXXXXXXXXXXXXXXXXXXXX To: <A href="mailto:XXXXXXXXXXXXXXXXX" title=XXXXXXXXXXXXXXXXX>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 :<FONT     face=Arial size=2>
    EXECUTE check_for_name(<FONT color=#0000f0     size=1>'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.

Received on Mon Jul 23 2001 - 14:39:27 CDT