Re: PLSQL Functions

Paul Noakes wrote:
>
> Help!
>
> I have an SQL query which selects one row from many table joins.
> I would now like to use this function from within my PL-SQL
> procedures. However, I cannot find a way of creating a function that
> returns several attributes (I have attempted a few record methods).
>
> Any advise?
>
> I hope to then reimplement my SQL query to utilise this new function for
> backwards compatibility.
>
> Thanks
>
> Paul

Paul:

A PL/SQL function returns exactly ONE value. There is an alternative though:

In the function , to_char() all the non-char values and concatenate || them together with any string values. RPAD the components to a particular length so that you'll know where to cut on the return side. Then return the concatenated value to your query, which uses substr() to cut it up into its proper components and uses to_<datatype> to return the proper types.

Ouch! I wouldn't reccomend trying this at home...

Maybe you should instead use dynamic SQL (DBMS_SQL package) to create a view containing the right conditions in the where clause, select from the view, and then drop the view after you're done with it. If you have a multi-user application running, you'll either have to create the view under the user's own schema, or prefix the view name with their ?name, oracle_sid, etc?

Hope this gives you some ideas...
Ken Shirey
Oracle Database Administrator
PrimeCo Personal Communications, LLP
kshirey_at_primeco.com Received on Fri Nov 15 1996 - 00:00:00 CET