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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
