Re: Proc or function to return a table

On 1 Sep 2006 01:56:57 -0700, devjnr_at_gmail.com wrote:

>I'm quite a newbie in PL/SQL and I would get best way to start,
>possibly to avoid writing non efficient code and using all
>functionality and performance of PL/SQL.

first of all: I'm not sure why you are not using the object features of Oracle. You can represent your table as a nested table or Varray. You would need to create an object type to do that. That said, you probably don't need your function anymore. This is relevant, as your solution for sure is very inefficient (as your need dynamic sql you force Oracle to parse every statement, and parsing is a serialization device by design: as part of the shared pool needs to be locked during parsing, other sessions can't access that part of the shared pool).
That all said, the best way to start would probably be to reconsider, and read up on object types, and see what can be done at http://asktom.oracle.com using object types. Right now you need to convert your procedure to a *pipelined* function, but be warned: your code is not going to scale at all.

--
Sybrand Bakker, Senior Oracle DBA