You Asked
Hi Tom,
When i tried to access serially reusable package from pipelined table function it has thrown error message.I have below queries on this ..
1.What is the workaround for this i.e if at all i want to access the serially reusable package from the table function,is there any way i could surpass the error message,if yes How?If no what alternative do you suggest?
2.Is there any specific reason why oracle does not support this??
Thanks
and Tom said...
well, think about this for a minute.
A client fetches from a cursor - they fetch say 100 rows at a time. Suppose further that your table function returns 200 rows.
Now, the client requests the first 100 rows - we run your code - you return the first 100 and when you call pipe row() for the 100th time - we SUSPEND your code and sent the 100 records back to the client.
Now, the call is over - the database call (fetch 100 records) is done. Pragma serially reusable says "when the call is complete, deinstantiate the code - reset it back to square one, reset all variables, get all its memory bits out of the PGA - make it go away"
But - you have 100 more records to return - you will have "forgotten" that if we did that.
It doesn't even begin to make sense to use a serially reusable, get me out of memory after each call, bit of code with a table function.
The reason it is not supported is - it cannot make sense to do it. You *need* to maintain a state between calls to the database for a table function - since the plsql routine does NOT finish in general in a single call - it takes repeated fetches (repeated calls) to get this table function "exhausted"
My alternative would be to not use a serially reusable package in that context - since they do not, cannot, work correctly.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment