Functions (in any language, not just PL/SQL) return one value. That may be restrictive, it's one more than procedures return. So what if you want to return a date and a numeric code that tells how accurate the date is? What if you want to look up an address, including city, state and ZIP code? Solutions fall into three broad categories:
- Return a composite object
- Use OUT or IN OUT arguments
- Don't return it: store it
1. Return a composite object We're used to seeing functions that return simple, built-in types, like DATE. But DATEs aren't really simple, are they? An Oracle DATE contains a date (in the non-Oracle sense) and a time, and these, in turn, are compounds of year, month, minutes, etc. So the first, obvious trick you can use is to return a single large object (like a long VARCHAR2) that you can parse after you have it. (Don't laugh: a comma-separated list of values might be just the thing you need.) A function can also return an XMLTYPE, a user-defined type, a PL/SQL table, a cursor, or any kind of data structure you can have in PL/SQL.
2. Use OUT or IN OUT arguments
FUNCTION emp_addr ( out_street OUT VARCHAR2, out_city OUT VARCHAR2, out_state OUT VARCHAR2, out_zip_cd OUT VARCHAR2, emp_id IN NUMBER, max_len IN NUMBER DEFAULT 100 ) RETURN BOOLEANThe example above "returns" a single true-false value, but it sets four VARCHAR2 variables along the way. You can (in fact, must) assign values to these variables in the body of the function. You can not read them. (Declare the arguments as IN OUT if you want to do both.) When you call the function, you must supply variables (not literals) for these arguments, and the variables have to be able to contain whatever the function tries to put in them. That's the purpose of the max_len argument in the example: I can call the function with tiny little strings, like this:
DECLARE street_addr VARCHAR2 (20); city_name VARCHAR2 (20); ... BEGIN ... IF emp_addr (street_addr, city_name, state_abbr, zip_cd, current_id, 20) THEN ...without risking a run-time error (only loss of data) as long as my function does something like:
out_street := SUBSTR (emp_addr, 1, max_len);Remember to think objectively! The OUT (or IN OUT) arguments can be XMLTYPEs, user-defined types, etc.
3. Don't return it: store it Store values in
... dbms_session.set_context ('EMP', error_text, NULL); RETURN TRUE; EXCEPTION WHEN OTHERS THEN dbms_session.set_context ('EMP', error_text, SQLERRM || ' = error in emp_addr'); RETURN FALSE; END emp_addr;Here's how you retrieve it:
SELECT SYS_CONTEXT ('EMP', error_text) INTO status_text FROM dual;
This was first published in July 2003