function

[goutham_konduru]

Can function return more values if yes how can it possible

[susarlasireesha]

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