Sorry one more question... I just found an example where the TABLE was declared off of the cursor which is pretty cool. If I am writing this in a function how do I return the table. Is this any different if I code it directly into a stored procedure - for example how do I return the table type via a output refcursor to middle layer code?
Thanks. On Apr 26, 9:18 am, Jer <[email protected]> wrote: > Thanks Daniel, > Follow up question... > > BULK COLLECT looks interesting however all examples I found online > just talk about selecting from one table into a TABLE type which is > declared from a physical table (TABLEA%ROWTYPE). In my case the type > I have declared is a custom type made up of data from multiple > different tables, can bulk collect also be used here? In my first > test of this I got an ORA Error PLS-00386: type mismatch found at > 'OUT_TBL' between FETCH cursor and INTO variables > > Also the advice online seems to say that bulk collect should only be > used with a limit on the number of rows, this does not work for me > unfortunately as I need all of the rows. Are there ways around this? > > Thanks > > Jeremy. > > > > > > > You can investigate using BULK COLLECT and FORALL; using bulk collect > > into a collection allows you to load all of the data at once (one > > join, one pass through the tables) and the FORALL 'loop' processes the > > collection by iterating through the collection 'index'. Yes, it's > > still a 'row by row' approach but using a collection rather than > > multiple fetches can improve processing time considerably. > > > David Fitzjarrell > > > -- > > You received this message because you are subscribed to the Google > > Groups "Oracle PL/SQL" group. > > To post to this group, send email to [email protected] > > To unsubscribe from this group, send email to > > [email protected] > > For more options, visit this group > > athttp://groups.google.com/group/Oracle-PLSQL?hl=en-Hide quoted text - > > > - Show quoted text - > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group > athttp://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text - > > - Show quoted text - -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
