need help with returning value of pl/sql Function

[bobby, morkos]

I have this and it compiles, however it does not return anything. Could anyone tell me why?

CREATE OR REPLACE Function SHOW_PEGASUS_id(v_owner_id IN varchar, v_property_code IN varchar, v_chain_code in varchar)
RETURN NUMBER is

cursor c1 IS SELECT pegasus_id
     FROM pegasus
     WHERE owner_id = v_owner_id
     AND property_code = v_property_code
     AND chain_code = v_chain_code;

rec C1%ROWTYPE;
v_pegasus_id number;
BEGIN

open C1;

fetch C1 into rec;
IF C1%NOTFOUND THEN
select pegasus_id.CURRval into v_pegasus_id from dual; 
insert into pegasus(pegasus_id, owner_id, property_code, chain_code) 
values (v_pegasus_id, v_owner_id, v_property_code, v_chain_code);
RETURN v_pegasus_id;
else
RETURN rec.pegasus_id;
end if;


commit;
close C1;

END;
/

[Jennifer Bhamoo]

If I remember correctly, when you don't assign a variable a default value, that variable is initialized to null.
I don't see where you ever assigned v_pegasus_id anything, so if C1%NOTFOUND is true, then it is just returning null, and if C1%NOTFOUND is indeed true, then in your else statement, I don't see how rec.pegasus_id could have return anything since a record wasn't returned.
I hope this helps.
-Jennifer

[Mark Spritzler]

Or, I also don't see any exception handling. Maybe something is wrong in the query, and it throws and exception, which isn't handled, and therefore no return statement is ever executed.
Actually, I take that back. However it will be somthing just like that.
In your fetch it finds no records, therefore it throws the NOTFOUND exception, and trys to go to the exception handling that is not there. It won't run the code after the fetch.
So try this

 EXCEPTION
    WHEN NOTFOUND THEN
select pegasus_id.CURRval into v_pegasus_id from dual; 
insert into pegasus(pegasus_id, owner_id, 
property_code, chain_code) 
values (v_pegasus_id, v_owner_id, v_property_code, v_chain_code);RETURN v_pegasus_id;
;
END ; 

Then remove the IF statement in the Body of the function.
I wish I could show this better than I am right now, but basically you have it good. It's just that you need to move the code for NOTFOUND into the EXCEPTION area, so that it will return a value

Just my guess.
Mark
[ April 30, 2002: Message edited by: Mark Spritzler ]

[Mark Spritzler]

OK try this

 
CREATE OR REPLACE Function SHOW_PEGASUS_id(
    v_owner_id IN varchar, 
    v_property_code IN varchar, 
    v_chain_code in varchar)
RETURN NUMBER is
    cursor c1 IS SELECT pegasus_id         
                    FROM pegasus        
                    WHERE owner_id = v_owner_id         
                    AND property_code = v_property_code         
                    AND chain_code = v_chain_code;
    rec C1%ROWTYPE;
    v_pegasus_id number;
BEGIN
    open C1;         
    fetch C1 into rec;      
    RETURN rec.pegasus_id;      
    close C1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN 
        select pegasus_id.CURRval into v_pegasus_id from dual;          
                insert into pegasus(pegasus_id, owner_id, property_code, chain_code)            
                    values (v_pegasus_id, v_owner_id, v_property_code, v_chain_code);           
        commit;
        RETURN v_pegasus_id;
    WHEN OTHERS THEN return -1;
END;
 

[Mark Spritzler]

OK, for some reason I feel I am still off here.
It looks like the procedure is trying to first see if the record already exists. If it does it returns the ID, if not it inserts and returns a new ID.
FOr new ID, I would use the nextVal rather than currval.
Second, I don't think you need a Cursor for this type of function. And also that way my solution of the Exception handling would work, and also give less code.
Now try this

  
CREATE OR REPLACE Function SHOW_PEGASUS_id(    
    v_owner_id IN varchar,     
    v_property_code IN varchar,     
    v_chain_code in varchar)
RETURN NUMBER is    
v_pegasus_id number;
BEGIN    
    SELECT pegasus_id into v_pegasus_id                             
    FROM pegasus                            
    WHERE owner_id = v_owner_id                             
        AND property_code = v_property_code                             
        AND chain_code = v_chain_code; 
    RETURN v_pegasus_id;          
EXCEPTION    
    WHEN NO_DATA_FOUND THEN        
        select pegasus_id.Nextval into v_pegasus_id from dual;                          
            insert into pegasus(pegasus_id, owner_id, property_code, chain_code)  
            values (v_pegasus_id, v_owner_id, v_property_code, v_chain_code);                   
            commit;        
        RETURN v_pegasus_id;    
    WHEN OTHERS THEN return -1;
    
END;

I feel much better about that one.
Here are a couple of sites I'd suggest you add to your favorites
a great link is Ask Tom.
Ask Tom is a guy at Oracle that knows everything. You ask him a question and he'll respond,
usually within the day. I have never had him fail me yet.
For those that like a good Oracle FAQ There is no better than
Ari Kaplan's Oracle Tips.

Hope that helps
Mark

[bobby, morkos]

Thanks for your help and links. You been really helpful.

Originally posted by Mark Spritzler:
OK, for some reason I feel I am still off here.
It looks like the procedure is trying to first see if the record already exists. If it does it returns the ID, if not it inserts and returns a new ID.
FOr new ID, I would use the nextVal rather than currval.
Second, I don't think you need a Cursor for this type of function. And also that way my solution of the Exception handling would work, and also give less code.
Now try this

  
CREATE OR REPLACE Function SHOW_PEGASUS_id(    
    v_owner_id IN varchar,     
    v_property_code IN varchar,     
    v_chain_code in varchar)
RETURN NUMBER is    
v_pegasus_id number;
BEGIN    
    SELECT pegasus_id into v_pegasus_id                             
    FROM pegasus                            
    WHERE owner_id = v_owner_id                             
        AND property_code = v_property_code                             
        AND chain_code = v_chain_code; 
    RETURN v_pegasus_id;          
EXCEPTION    
    WHEN NO_DATA_FOUND THEN        
        select pegasus_id.Nextval into v_pegasus_id from dual;                          
            insert into pegasus(pegasus_id, owner_id, property_code, chain_code)  
            values (v_pegasus_id, v_owner_id, v_property_code, v_chain_code);                   
            commit;        
        RETURN v_pegasus_id;    
    WHEN OTHERS THEN return -1;
    
END;

I feel much better about that one.
Here are a couple of sites I'd suggest you add to your favorites
a great link is Ask Tom.
Ask Tom is a guy at Oracle that knows everything. You ask him a question and he'll respond,
usually within the day. I have never had him fail me yet.
For those that like a good Oracle FAQ There is no better than
Ari Kaplan's Oracle Tips.

Hope that helps
Mark