Re: Dynamic SQL to call a function with RETURN value?
Date: Mon, 15 Dec 2008 14:32:34 -0800 (PST)
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:
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.
-- @home.com is bogus. http://www.telegraph.co.uk/news/newstopics/howaboutthat/3743289/Zzz-mail-What-happens-when-sleepwalkers-go-online.htmlReceived on Mon Dec 15 2008 - 16:32:34 CST