Function to get first record from query and return single value


/ Published in: PL/SQL
Save to your folder(s)

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

Be sure to set your ORDER BY clause properly.


Copy this code and paste it in your HTML
  1. -- Person may have multiple addresses. Gets FIRST record only
  2. CREATE OR REPLACE FUNCTION get_first_address(person_id_in IN VARCHAR2) RETURN VARCHAR2 IS
  3. ret_value VARCHAR2(1024) := ' ';
  4.  
  5. CURSOR the_cur(l_person VARCHAR2) IS
  6. SELECT street1 || ', ' || street2
  7. FROM address
  8. WHERE person_id = l_person
  9. ORDER BY date_added;
  10. OPEN the_cur(l_person);
  11.  
  12. FETCH the_cur
  13. INTO ret_value;
  14.  
  15. CLOSE the_cur;
  16.  
  17. RETURN ret_value;
  18. RETURN ret_value;
  19. DBMS_OUTPUT.PUT_LINE('EXCEPTION IN get_first_address - ' || SQLCODE || ': ' || SQLERRM);
  20. RETURN ret_value;
  21. END get_first_address;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.