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 > > > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
<<inline: 332.gif>>
