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;
On Oct 28, 6:56 pm, Michael Moore <[EMAIL PROTECTED]> wrote:
> Same principle as my solution but Thomas'es is better because it does
> not concatenate the parameter into the dynamic sql string. Instead it uses
> only EXECUTE IMMEDIATE parameters (USING nIn) which is a better way to do
> it. [?]
>
> On Tue, Oct 28, 2008 at 5:54 AM, Thomas Olszewicki <[EMAIL PROTECTED]> wrote:
>
> > On Oct 27, 9:17 am, mwmann <[EMAIL PROTECTED]> wrote:
> > > Thanks Rob, I have done something similar to what you have suggested
> > > as a work around.
> > > I suppose you have just confirmed what I feared. Will have to go
> > > change the RETURN types and statements in all the functions which are
> > > already BOOLEAN.
> > > Was also trying to think of somehow wrapping the call, but thats wont
> > > work as ultimately I have to always dynamically make the call the the
> > > Boolean function.
>
> > > Thanks again
>
> > > > It just will not work. You can IIRC use it in a where or having clause
> > > > (as part of a condition) but since it is not legal to have a column
> > that
> > > > is type boolean it is not legal to select it. Basically that is a good
> > > > rule of thumb to go by. If you have a type that you cannot use as the
> > > > type of a column then you cannot select it.
>
> > > > Not the answer that you wanted to hear I am sure but ...
>
> > > > What you will often see is a function that returns 0,1,null or -1, 1,
> > > > null or 'T', 'F', null in lieu of being able to use the actual type
> > > > boolean.
>
> > > > Hopefully helpful
>
> > > > mwmann wrote:
> > > > > 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;- Hide quoted text -
>
> > > - Show quoted text -
>
> > You may create little more elaborate dynamic sql to trap boolean
> > values
> > inside Begin end; block.
> > Look at this example:
>
> > create or replace function TESTDynFunc(nIn NUMBER) return number is
> > nResult NUMBER(1);
> > cStmt VARCHAR2(600);
> > begin
>
> > cStmt := 'BEGIN DECLARE lRes boolean
> > ; ';
> > cStmt := cStmt || 'BEGIN lRes := test_positive ( :nNum ); ';
> > cStmt := cStmt || 'IF lRes THEN :nRtn := 1; ';
> > cStmt := cStmt || 'ELSE :nRtn := 0; END IF; ';
> > cStmt := cStmt || 'END;';
> > cStmt := cStmt || 'END;';
> > EXECUTE IMMEDIATE cStmt USING nIn , OUT nResult;
>
> > return nResult;
> > end TESTDynFunc;
>
> > As you can see this returns numeric and therefore can be call from
> > select statement.
> > HTH
> > Thomas
>
> 332.gif
> < 1KViewDownload
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
