Dynamic SQL to call a function with RETURN value?

On Oct 27, 4:24 pm, sybra... DeleteThis @hccnet.nl 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
 >> Stay informed about: Dynamic SQL to call a function with RETURN value? 

On Oct 27, 4:24 pm, sybra....DeleteThis@hccnet.nl 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

I also recommend avoiding use of dynamic SQL unless you cannot solve
your business problem any other way.

If you really need dynamic SQL then look in the PL/SQL manual at the
execute immediate statement and for more complex SQL look in the PL?
SQL Packages and Type Reference manual at the entry for package
dbms_sql.

Packages and overloading procedure/function calls as Sybrand mentioned
is a potential alternate approach. You might also be able to make use
of weakly typed reference cursors.

HTH -- Mark D Powell --
 >> Stay informed about: Dynamic SQL to call a function with RETURN value? 

"Thomas Olszewicki" wrote in message

On Oct 27, 4:24 pm, sybra....DeleteThis@hccnet.nl 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
 >> Stay informed about: Dynamic SQL to call a function with RETURN value? 

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, sybra... DeleteThis @hccnet.nl 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.

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;
 >> Stay informed about: Dynamic SQL to call a function with RETURN value? 

On Oct 30, 3:11 am, mwmann wrote:
> 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, sybra....DeleteThis@hccnet.nl 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.
>
> 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
 >> Stay informed about: Dynamic SQL to call a function with RETURN value? 

On Dec 15, 6:21 am, jas... RemoveThis @aol.com wrote:
> On Oct 30, 3:11 am, mwmann wrote:
>
>
>
>
>
> > 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, sybra... RemoveThis @hccnet.nl 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.
>
> > 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-ma...What-ha
 >> Stay informed about: Dynamic SQL to call a function with RETURN value?