Re: Dynamic SQL to call a function with RETURN value?

On Dec 15, 6:21 am, jas..._at_aol.com wrote:
> On Oct 30, 3:11 am, mwmann <mwm..._at_gmail.com> wrote:
>
>
>
>
>
> > On Oct 28, 4:49 pm, "gym dot scuba dot kennedy at gmail"
>
> > <kenned..._at_verizon.net> wrote:
> > > "Thomas Olszewicki" <Thom..._at_cpas.com> wrote in message
>
> > >news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5_at_l62g2000hse.googlegroups.com...
> > > On Oct 27, 4:24 pm, sybra..._at_hccnet.nl wrote:
>
> > > > On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm..._at_gmail.com>
> > > > wrote:
>
> > > > >I presume that Dynamic SQL is the way to go, but I have not been able
> > > > >to get it right.
>
> > > > Dynamic SQL is the correct method to develop an application which has
> > > > 'DISASTER' inscribed all over it.
> > > > Your description is very vague, and from what I get from it you seem
> > > > to have re-invented a feature which already exists in PL/SQL, albeit
> > > > in packages, called 'Overloading'
> > > > You would need to present much more detail (as well as a database
> > > > version) to find out why you have a desire to end on the electrical
> > > > chair or to make the life of your potential customers miserable.
>
> > > > --
> > > > Sybrand Bakker
> > > > Senior Oracle DBA
> > > >>Dynamic SQL is the correct method to develop an application which has
>
> > > 'DISASTER' inscribed all over it. <<
>
> > > Could you point to Oracle documentation, where it lists Dynamic SQL
> > > limits?
> > > Thx
> > > Thomas
> > > If you read the performance guide you will see you are forcing hard parses
> > > all over the place with this approuch.  This severly limits scalability.  In
> > > addition, dynamic sql is very difficult to debug.
> > > Jim
>
> > Thanks everyone for your response. - PROBLEM SOLVED
> > I have slightly changed Thomas suggestion to suit my requirement.
>
> > This is what I have done in the event somebody comes across the same
> > requirement in the future:
> > I have used comments to best try describe what I will ultimately do,
> > but tested a sample function and all is good.
>
> > For those who have warned me against using dynamic SQL for performance
> > and scalability issues, thaks for the warning - NOTED.
> > I have spelled these out to the client as risks, and not the prefered
> > approach.
> > However, I have to work with what I have, and can not change other
> > areas of the system due to project mandate.
>
> > DECLARE
> > /*
> >  *  Return Code will be used to determine whether program completed
> > successfullly, or encountered error
> >  *  All batch functions return true or false indication function
> > status
> > */
> > nReturnCode NUMBER;
> > /*
> >  *  str_func will be set in a cursor Loop using function calls
> > obtained from a job setup table
> > */
> > str_func VARCHAR2(255) := 'test_func(-2)'; --will get this function
> > and parameters from a job_setup table using a cursor
> > cStmt VARCHAR2(600);
>
> > BEGIN
>
> >    -- LOOP THROUGH cursor (all functions)
> >       -- set str_func = get function from cursor
> >       -- execute dynamic sql function call
> >                cStmt :=          'BEGIN DECLARE bool_rtn BOOLEAN ; ';
> >                cStmt := cStmt ||      'BEGIN bool_rtn := '||
> > str_func||'; ';
> >                cStmt := cStmt ||         'IF bool_rtn THEN :o_Rtn :=
> > 0; ';
> >                cStmt := cStmt ||         'ELSE :o_Rtn := -1; END IF;
> > ';
> >                cStmt := cStmt ||      'END;';
> >                cStmt := cStmt || 'END;';
> >           EXECUTE IMMEDIATE cStmt USING OUT nReturnCode;
>
> >             -- test execution status of function
> >             IF(nReturnCode=0) THEN
> >             --do successfull completion code
> >             ELSE
> >             --do failed completion code
> >             --will exit loop and write to neccessary error tables.
> >             END IF;
> > END;- Hide quoted text -
>
> > - Show quoted text -
>
> I have developed a user defined data type called AnonymousFunction
> that might also do the trick for you.  Check it out here:
>
> http://radio.weblogs.com/0137094/2008/11/09.html

That does seem pretty cool, though the previous comments about performance and debugging certainly apply.

But the really funny thing was the ads that google deemed appropriate - for dump trucks.

jg

--
@home.com is bogus.
http://www.telegraph.co.uk/news/newstopics/howaboutthat/3743289/Zzz-mail-What-happens-when-sleepwalkers-go-online.html