Advanced Search

Return to Snippet

Revision: 8384
at September 17, 2008 17:44 by stews
Initial Code 
-- Person may have multiple addresses.  Gets FIRST record only
CREATE OR REPLACE FUNCTION get_first_address(person_id_in IN VARCHAR2) RETURN VARCHAR2 IS
    ret_value VARCHAR2(1024) := ' ';

    CURSOR the_cur(l_person VARCHAR2) IS
        SELECT street1 || ', ' || street2 
          FROM address 
         WHERE person_id = l_person
        ORDER BY date_added;
BEGIN
    OPEN the_cur(l_person);

    FETCH the_cur
        INTO ret_value;

    CLOSE the_cur;

    RETURN ret_value;
EXCEPTION
    WHEN no_data_found THEN
        RETURN ret_value;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('EXCEPTION IN get_first_address - ' || SQLCODE || ': ' || SQLERRM);
        RETURN ret_value;
END get_first_address;
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
Nothing amazing here, just an efficient way to get the first row.

Be sure to set your ORDER BY clause properly.

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Function to get first record from query and return single value

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
query

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
PL/SQL