Re: Dynamic SQL
Date: Fri, 22 Oct 2010 09:03:52 -0700 (PDT)
Message-ID: <b2f96c97-8f66-4ee4-82c1-ea52bfabec98_at_u24g2000pru.googlegroups.com>
On Oct 22, 7:25 am, The Magnet <a..._at_unsu.com> wrote:
> Hi, I'm looking for an answer but also thought I can take a shortcut
> here.
>
> We have a query with a ton of calculations using DECODE and NULLIF and
> more and more. So, we created a function in which we will pass a pair
> of values in a string (number,'operator'):
>
> CALC_FORMULA(x,'-',y,'+',z,'*')
>
> My question is, with all that dynamic stuff, can I perform the
> calculation? I mean, the operators are variables. So, is it as
> simple as constructing a string and using like EXECUTE IMMEDIATE?
Gerard certainly has a point with the performance implications of switching back and forth between PL and SQL, on some systems that does become significant. On the other hand, with the modern versions way of handling child cursors, mutexes and such, it may be either better or worse, so a demonstration both ways (function pounded by many multiple sessions, and equivalent inlines) might be informative to everybody. Also try native compilation. Sometimes it is even worth it to write heavy calcs in another language, pipeline to a dedicated session or other strange things.
That's the thing about generalized functions; depending on exactly what you are doing under load, they may help, hurt, or only make coding management difference. Scalability may be a big difference, as might the spread of different ways apps could use such a function. There could even be platform differences specific to how calculations are handled.
jg
-- _at_home.com is bogus. "Workers of America, to the ramparts! We are on the march for — what? Retirement at 68? So here’s to the French. Those croissant-munching, Champagne-swilling, Galois-puffing, pension oui-nies." - Peter RoweReceived on Fri Oct 22 2010 - 11:03:52 CDT