Hi

Please can someone help me out. I have done quite a bit of searching
and 'trial and error' without much success.
I have simplified the problem for readability, but a solution to this
will allow me to solve my problem.

PROBLEM:
I have a table BATCH_FUNCTION which will contain records with various
existing DB functions, with parameters- (Return Type BOOLEAN).

In an Anonymous Block (lets say for example), if I had a list of the
functions to call in a cursor (select function_name from
batch_function), how would I call these functions with parameters, as
well as be able to test the Return value in my Anonymous block?

I presume that Dynamic SQL is the way to go, but I have not been able
to get it right.


-----------------------------------------------------------------------------------------------------------
EXAMPLE TABLE: BATCH_FUNCTION
-----------------------------------------------------------------------------------------------------------
func_id function_name
----------- ---------------------
111 test_positive(1)



-----------------------------------------------------------------------------------------------------------
EXAMPLE FUNCTION
-----------------------------------------------------------------------------------------------------------
FUNCTION test_positive(i_number IN NUMBER) RETURN BOOLEAN AS
BEGIN
if(i_number >0) THEN
RETURN true;
else
RETURN false;
end if;
END test_positive;

On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwmann (AT) gmail (DOT) com>
wrote:

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

On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwmann (AT) gmail (DOT) com>
wrote:

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

On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwmann (AT) gmail (DOT) com>
wrote:

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

On Mon, 27 Oct 2008 03:41:00 -0700 (PDT), mwmann <mwmann (AT) gmail (DOT) com>
wrote:

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

On Oct 27, 4:24*pm, sybra... (AT) hccnet (DOT) nl wrote:

Could you point to Oracle documentation, where it lists Dynamic SQL
limits?
Thx
Thomas

On Oct 27, 4:24*pm, sybra... (AT) hccnet (DOT) nl wrote:

Could you point to Oracle documentation, where it lists Dynamic SQL
limits?
Thx
Thomas

On Oct 27, 4:24*pm, sybra... (AT) hccnet (DOT) nl wrote:

Could you point to Oracle documentation, where it lists Dynamic SQL
limits?
Thx
Thomas

On Oct 27, 4:24*pm, sybra... (AT) hccnet (DOT) nl wrote:

Could you point to Oracle documentation, where it lists Dynamic SQL
limits?
Thx
Thomas

On Oct 27, 4:24*pm, sybra... (AT) hccnet (DOT) nl wrote:
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 --