Imagine you've got the following situation (Oracle 9i):


Table A
-------------------
Name
Customer
Log_Date
Log_Type

(Changing the format of the table is not an option as the database is
already in production, and owned by someone else)


When the Name field is changed, you insert a new row, putting the
current date in Log_Date and you set Log_Type to 1 to indicate the name
was changed.

When the Customer field is changed, you insert a new row, putting the
current date in the Log_Date and you set Log_Type to 2 to indicate the
customer was changed.

So, table data looks like:

Name Customer Log_Date Log_Type
------------------------------------------------------
Joe Acme 1-1-2003 1
Sue Acme 1-14-2003 1
Dave Telex 1-1-2003 2
Bob Sprint 10-1-2004 2
Joe Acme 1-1-2004 1

Now, you want to produce a dataset that shows the last updated date for
both customer and name


Name Updated (LOG_DATE) Customer Updated (LOG_DATE)
---------------------------------------------------------------------
Joe 1-1-2004 Acme 1-1-2003
Bob 12-1-2003 Sprint 10-1-2004

I certainly can't see any way to do that in a single query. Not only
does the Log_Date field appear twice, but you need to find the last
date with Log_Type = 1 and then the last date with Log_Type = 2.

I think this can be done either with a stored procedure, or perhaps a
table function. This code will be called from an external program.

My questions:

1) Can someone give advice on writing such a procedure or table
function?

2) Returning values via variables is straightforward, but how does one
return a dataset to the calling external program?

3) Assume for example that a procedure has been called by Perl. Does
DBMS_OUTPUT.PUT_LINE return anything to the calling program, or is it
just for producing console output in SQL*Plus?

Please let me know if I've not been clear.

Thanks,
Wade