5 Replies Latest reply: Aug 2, 2013 12:22 AM by 842086 RSS

    Boolean value in function

    842086

      Dear All,

      I have the function like as follows

      Function Test_Bool (id number,name varchar2 ) return boolean as

      begin

      return true;

      end;

       

      for the above function i need to use as follows

       

      select 'X' from dual where Test_Boo(1,'dddd') = 1 ;

       

       

      but its show the error like invalid identifier

      How to do this? just i want to check in where condition like as true

       

       

      Thanks ...

        • 1. Re: Boolean value in function
          Pablolee

          SQL does not recognise the boolean datatype. You'll have to have the function return a different datatype

          • 2. Re: Boolean value in function
            Frank Kulash

            Hi,

             

             

            842086 wrote:

             

            Dear All,

            I have the function like as follows

            Function Test_Bool (id number,name varchar2 ) return boolean as

            begin

            return true;

            end;

             

            for the above function i need to use as follows

             

            select 'X' from dual where Test_Boo(1,'dddd') = 1 ;

             

             

            but its show the error like invalid identifier

            How to do this? just i want to check in where condition like as true

             

             

            Thanks ...

            If you want to say:

             

            where Test_Boo(1,'dddd') = 1

             

            then you want Test_Boo to return a NUMBER, not a BOOLEAN.

            If you can't change Test_Boo so that it returns a NUMBER, you can write a wrapper function that does:

             

            CREATE OR REPLACE FUNCTION  test_boo_num
            (   in_id    IN  NUMBER
            ,   in_name  IN  VARCHAR2
            )
            RETURN NUMBER
            IS
                return_val  NUMBER;
                test_boo_true BOOLEAN  := test_boo (in_id, in_name);
            BEGIN
                IF  test_boo_true
                THEN
                    return_val := 1;
                ELSIF NOT test_boo_true
                THEN
                    return_val := 0;
                END IF;

                RETURN  return_val;
            END  test_boo_num;
            /
            SHOW ERRORS

             

            You can call the wrapper function from SQL statements like this:

             

            where   Test_Boo_num (1, 'dddd')  = 1

             

            I assume test_boo is actually more complicated than what you posted.  If test_boo never returns UNKNOWN, then the wrapper function can be simplified a little.

            • 3. Re: Boolean value in function
              myOra_help

              Do like this,

               

              create Function Test_Bool (id number,name varchar2 ) return number as
              begin

              --if something then
              return 1;

              --else

              --return something;

              ---end if;

              end;

               

              select 'X' from dual where Test_Bool(1,'dddd') = 1 ;

               

              Output

              ------

              X

              • 4. Re: Boolean value in function
                Peter

                If the functionality within the function test_bool results in a boolean, recreate the function returning a number and use this as the return clause

                if it does not matter what datatype is used within the function, just use a number.

                 

                create or replace function test_bool return number

                as

                  t_result boolean;

                begin

                  -------------------

                  -- Do a lot of stuff which gives a boolean value to t_result

                  -------------------

                  return case when t_result then 1 else 0 end;

                end;

                • 5. Re: Boolean value in function
                  842086

                  Thanks To All ... Its Really Helpful ...