Returning more than one value from PL/SQL procedure

Is it possible to return more than one value from a PL/SQL procedure or function? If so, how would I go about it?

    Requires Free Membership to View

    Login

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:

  1. Return a composite object
  2. Use OUT or IN OUT arguments
  3. Don't return it: store it
Each of the three approaches is useful in different circumstances. You might want to use all three approaches at the same time.

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  BOOLEAN
The 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

  • Ordinary tables You can return hundreds of values at once.
  • Temporary tables Each session can have its own copy of the table
  • Package variables Like the variable unclaimed_text in my last question
  • SYS_CONTEXT variables For example, here's one way to store a value: 
    •     ...
    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

    Join the conversationComment

    Share
    Comments

      Results

      Contribute to the conversation

      All fields are required. Comments will appear at the bottom of the article.
      Back to top