Re: Can a stored procedure return an array of values?

Antonio Pascual wrote:
>
> Hi all.
> Can a stored procedure return an array of values?
> Not a result set. A need to process the data.
>
> regards.
>
> Antonio.

Not exactly, but it can return (in the form of an OUT or IN OUT parameter) a PL/SQL table, which is sort of like an array in other languages.

I suspect (though I haven't tried it) that a function could return a PL/SQL table as its return value.

For large PL/SQL tables this technique may incur a lot of overhead, especially if you pass the table up and down through several levels of procedure calls. Since PL/SQL passes parameters by value, each pass of a PL/SQL table parameter requires that the entire table be copied.

One workaround is to declare a single global PL/SQL table within the specification of a package. Then each level of procedure can access the global table directly, without having to make a copy of it. This trick may or may not work for you, depending on the circumstances. Also global variables always weaken modularity, but the efficiency tradeoff may make them worthwhile.

Scott McKellar mck9_at_swbell.net
Free PL/SQL utilities at: http://home.swbell.net/mck9/pls/ Just out: new release of plsb, a beautifier for PL/SQL Received on Mon Aug 23 1999 - 19:59:18 CDT