OTN Appreciation Day : Functions returning record structures

OTN Appreciation Day : Functions returning record structures
Oct 08, 2016 by Gert Poel


Tim Hall, the man behind Oracle-base.com invited all Oracle bloggers to write a post about their favorite Oracle feature and this as a kind of appreciation of the Oracle Technolgy Network.

Oracle Technology Network(OTN) is a great source for information, downloads, events, ... for me,  just like Time explains in his OTN appreciation day blog post.

So here is my feature I like in Oracle: Functions returning record structures.

It's not the biggest cool thing, but something I use a lot.

A PL/SQL function has only one RETURN parameter, but what when you have to return multiple values?
I've seen people using OUT parameters in a function.  But when I see that, I start shivering.
To me, there's only one way out for values in function... the RETURN clause(and one of Steven Feuerstein's best practices).
When you need to return multiple values, use a record structure!

And a cool feature in Oracle functions is, that you can access fields directly from the record in your function call.

E.g.:

FUNCTION get_full_name(p_employee_id IN hr.employees.employee_id%TYPE)
RETURN employees%ROWTYPE

You can retrieve the last name like this:

get_employee(100).last_name

Pretty cool, isn't it?

If you want more information, I wrote an article on All Things Oracle about this.
And a blog post with the scripts I've put on Oracle LIVE SQL.