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