I was looking around about cursor performance and noticed this link: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/technique.htm
What it describes (generating dynamic SQL) is exactly what I'm doing because there are so many different user options. The user can control if certain columns are returned and what range of values within the columns are returned, etc. Is the documentation actually talking about a different situation where bind variables could be used but they aren't? So in that case it's a mistake. In my case, it's just something I have to live with? 3.1.3 Top Ten Mistakes Found in Oracle Systems... *2. Bad Use of Cursors and the Shared Pool* Not using cursors results in repeated parses. If bind variables are not used, then there is hard parsing of all SQL statements. This has an order of magnitude impact in performance, and it is totally unscalable. Use cursors with bind variables that open the cursor and execute it many times. Be suspicious of applications generating dynamic SQL. .... On Thu, Apr 9, 2009 at 10:02 AM, Joel <johow...@gmail.com> wrote: > The TABLE functions seem very handy and I'm sure I'll find a use for it in > other projects. For now I'm going to go with David's suggestion since it > provides the most flexibility. Depending on what parameters are being sent > in, the results could have from 5 to 25 columns returned and I'd rather not > deal with the null columns if I don't have to. > > Thanks for all of the suggestions! > > > > > On Wed, Apr 8, 2009 at 1:01 PM, Michael Moore <michaeljmo...@gmail.com>wrote: > >> I also prefer TABLE functions to ref-cursors whenever possible. The >> end-user can treat them just like any other SELECT statement including >> adding their own WHERE, ORDER BY, GROUP BY, and JOIN logic. The only thing >> to be aware of is if your procedure does any updates, they you must make it >> AUTONOMOUS. >> >> >> On Wed, Apr 8, 2009 at 9:01 AM, Rob Wolfe <rob.wo...@oraclegeeks.com>wrote: >> >>> >>> On Wed, April 8, 2009 10:59, Michael Moore wrote: >>> > You could also use a TABLE function. There are plenty of great examples >>> > online of how to do it. You would always return the entire super set of >>> > columns even though some of them would have NULL depending on your >>> > parameter. This may or may not be acceptable depending on how many >>> column. >>> > Mike >>> > >>> > On Wed, Apr 8, 2009 at 4:09 AM, Joel <johow...@gmail.com> wrote: >>> > >>> >> >>> >> Rather than building a select statement in application code then >>> >> sending it to the database for execution, I'd like to send some >>> >> parameter values into a stored procedure and then let it conditionally >>> >> build the select statement for me, execute it, and then return the >>> >> results to the application. I've gotten to the point where I can send >>> >> in parameters and my select statement is built, but how do I go about >>> >> executing my select statement string from within the stored >>> >> procedure? How do I send back the results? Is this possible if I >>> >> don't know what columns will be included in the results until the >>> >> parameters are sent in (e.g. true/false parameters such as >>> >> IncludeColumnA, IncludeColumnB, etc)? >>> >> > >>> >> >>> > >>> > > >>> > >>> Just to chime in my $0.02 worth, I use the table function approach all >>> the >>> time and to make it nice and friendly to end users I usually wrap it in a >>> view so that they don't see the parameter passing. This allows you to >>> include or exclude columns as you see fit. >>> >>> For instance, I have a package that gets a large quantity of order entry >>> information directly from Oracle Apps, massages it, stores it and then >>> presents it in a few different ways in Excel Workbooks (yeah, I know, >>> Excel == icky, we are implementing OBIEE+ real soon now) and to make >>> maintenance easier on myself I created several views wrapping a single >>> table function and had a query in the workbooks basically do a "select *" >>> from one of the views and my clients can then just hit "refresh" and they >>> get shiny new data even if I have modified the underlying function or >>> made >>> the view pass different parms to the table function. We currently use >>> this >>> technique to get daily, weekly, and historical snapshots of data and the >>> only difference between the views is a parameter passed to the table >>> function. >>> >>> Just a thought on the maintenance side if you go that route... >>> >>> Rob >>> >>> >>> >>> >> >> >> >> > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
