 | |
Dynamic SQL to call a function with RETURN value?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Dynamic SQL to call a function with RETURN value? -
10-30-2008
, 02:11 AM
|
"Thomas Olszewicki" <Thom... (AT) cpas (DOT) com> wrote in message news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5 (AT) l62g2000hse (DOT) googlegroups.com... On Oct 27, 4:24 pm, sybra... (AT) hccnet (DOT) nl wrote: On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm... (AT) gmail (DOT) 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 |
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
_Rtn :=0; ';
cStmt := cStmt || 'ELSE
_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;
#22
| |||
| |||
|
|
On Oct 28, 4:49*pm, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Thomas Olszewicki" <Thom... (AT) cpas (DOT) com> wrote in message news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5 (AT) l62g2000hse (DOT) googlegroups.com.... On Oct 27, 4:24 pm, sybra... (AT) hccnet (DOT) nl wrote: On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm... (AT) gmail (DOT) 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 _Rtn :=0; '; * * * * * * * *cStmt := cStmt || * * * * 'ELSE _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 - |
that might also do the trick for you. Check it out here:
http://radio.weblogs.com/0137094/2008/11/09.html
#23
| |||
| |||
|
|
On Oct 28, 4:49*pm, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Thomas Olszewicki" <Thom... (AT) cpas (DOT) com> wrote in message news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5 (AT) l62g2000hse (DOT) googlegroups.com.... On Oct 27, 4:24 pm, sybra... (AT) hccnet (DOT) nl wrote: On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm... (AT) gmail (DOT) 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 _Rtn :=0; '; * * * * * * * *cStmt := cStmt || * * * * 'ELSE _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 - |
that might also do the trick for you. Check it out here:
http://radio.weblogs.com/0137094/2008/11/09.html
#24
| |||
| |||
|
|
On Oct 28, 4:49*pm, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Thomas Olszewicki" <Thom... (AT) cpas (DOT) com> wrote in message news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5 (AT) l62g2000hse (DOT) googlegroups.com.... On Oct 27, 4:24 pm, sybra... (AT) hccnet (DOT) nl wrote: On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm... (AT) gmail (DOT) 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 _Rtn :=0; '; * * * * * * * *cStmt := cStmt || * * * * 'ELSE _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 - |
that might also do the trick for you. Check it out here:
http://radio.weblogs.com/0137094/2008/11/09.html
#25
| |||
| |||
|
|
On Oct 28, 4:49*pm, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Thomas Olszewicki" <Thom... (AT) cpas (DOT) com> wrote in message news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5 (AT) l62g2000hse (DOT) googlegroups.com.... On Oct 27, 4:24 pm, sybra... (AT) hccnet (DOT) nl wrote: On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm... (AT) gmail (DOT) 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 _Rtn :=0; '; * * * * * * * *cStmt := cStmt || * * * * 'ELSE _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 - |
that might also do the trick for you. Check it out here:
http://radio.weblogs.com/0137094/2008/11/09.html
#26
| |||
| |||
|
|
On Oct 30, 3:11*am, mwmann <mwm... (AT) gmail (DOT) com> wrote: On Oct 28, 4:49*pm, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Thomas Olszewicki" <Thom... (AT) cpas (DOT) com> wrote in message news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5 (AT) l62g2000hse (DOT) googlegroups.com.... On Oct 27, 4:24 pm, sybra... (AT) hccnet (DOT) nl wrote: On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm... (AT) gmail (DOT) 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 _Rtn :=0; '; * * * * * * * *cStmt := cStmt || * * * * 'ELSE _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 |
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/news...go-online.html
#27
| |||
| |||
|
|
On Oct 30, 3:11*am, mwmann <mwm... (AT) gmail (DOT) com> wrote: On Oct 28, 4:49*pm, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Thomas Olszewicki" <Thom... (AT) cpas (DOT) com> wrote in message news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5 (AT) l62g2000hse (DOT) googlegroups.com.... On Oct 27, 4:24 pm, sybra... (AT) hccnet (DOT) nl wrote: On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm... (AT) gmail (DOT) 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 _Rtn :=0; '; * * * * * * * *cStmt := cStmt || * * * * 'ELSE _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 |
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/news...go-online.html
#28
| |||
| |||
|
|
On Oct 30, 3:11*am, mwmann <mwm... (AT) gmail (DOT) com> wrote: On Oct 28, 4:49*pm, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Thomas Olszewicki" <Thom... (AT) cpas (DOT) com> wrote in message news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5 (AT) l62g2000hse (DOT) googlegroups.com.... On Oct 27, 4:24 pm, sybra... (AT) hccnet (DOT) nl wrote: On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm... (AT) gmail (DOT) 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 _Rtn :=0; '; * * * * * * * *cStmt := cStmt || * * * * 'ELSE _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 |
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/news...go-online.html
#29
| |||
| |||
|
|
On Oct 30, 3:11*am, mwmann <mwm... (AT) gmail (DOT) com> wrote: On Oct 28, 4:49*pm, "gym dot scuba dot kennedy at gmail" kenned... (AT) verizon (DOT) net> wrote: "Thomas Olszewicki" <Thom... (AT) cpas (DOT) com> wrote in message news:a83a2f4f-00c2-4665-90c5-1fbacc3594b5 (AT) l62g2000hse (DOT) googlegroups.com.... On Oct 27, 4:24 pm, sybra... (AT) hccnet (DOT) nl wrote: On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwm... (AT) gmail (DOT) 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 _Rtn :=0; '; * * * * * * * *cStmt := cStmt || * * * * 'ELSE _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 |
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/news...go-online.html
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.