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

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;