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

From: mwmann <>
Date: Thu, 30 Oct 2008 01:11:16 -0700 (PDT)
Message-ID: <>

On Oct 28, 4:49 pm, "gym dot scuba dot kennedy at gmail" <> wrote:
> "Thomas Olszewicki" <> wrote in message
> On Oct 27, 4:24 pm, wrote:
> > On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <>
> > 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.


  • 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);


  • 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;
Received on Thu Oct 30 2008 - 03:11:16 CDT

Original text of this message