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

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 Received on Mon Dec 15 2008 - 08:21:39 CST