One of the easiest ways to return PL/SQL table types to the middle code layer is using the standard SQL statement: "SELECT * FROM my.pkg.my_tab_func(arg1,arg2)"; on some Oracle versions you will need to use the TABLE function like the following "SELECT * FROM TABLE(my.pkg.my_tab_func(arg1,arg2))".
On Apr 26, 9:25 am, Jer <[email protected]> wrote: > 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-Hidequoted 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 > athttp://groups.google.com/group/Oracle-PLSQL?hl=en -- 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
