1 2 Previous Next 22 Replies Latest reply: Jul 8, 2009 5:45 AM by BluShadow RSS

    SQL engine cannot handle boolean return types from object_tables

    595841
      Just an FYI,

      I tried to create a pseudo "boolean" database type called "bool".
      It stores its value as an integer (0 or 1 :: TRUE or FALSE).

      I then created a second type object "employer" and then made a object table
      of that type. One of the attributes of employer was "active_status" of type
      "bool".

      I created a view on this table and in the "where" clause I used one
      of several object methods.

      I have a method named "is_true" which returnes a boolean data type.

      I tried a where clause of "where i.employee.active_status.is_true"
      and "where i.employee.active_status.is_true()"

      These did not work.

      I also tried "where i.employee.active_status.is_true() = TRUE"

      This did not work

      I have a static function in BOOL that returns a boolean value name B_TRUE.

      I tried "where i.employee.active_status.is_true() = bool.B_TRUE()"

      This compiled. but it produced the following error:

      ERROR at line 1:
      ORA-06552: PL/SQL: Statement ignored
      ORA-06553: PLS-382: expression is of wrong type

      Finally I had to revert to producing an integer type compare out of the
      object. It also compiled but it did work.

      I thought the SQL and PL/SQL engines where now the same code? Then
      why does the SQL engine not recognize a boolean return value?

      Oh well - just hope someone else will not have to spend their time
      on this matter!.
        • 1. Re: SQL engine cannot handle boolean return types from object_tables
          JustinCave
          FYI, the SQL and PL/SQL engines are very different pieces of code (they do share the same SQL parser now, however). BOOLEAN is a valid data type in PL/SQL. It is not, however, a valid data type in SQL. So you cannot use any function that returns a BOOLEAN value in a SQL statement.

          Many folks have complained that it's somewhat silly to introduce a data type in PL/SQL that the SQL engine can't recognize, but I don't see any realistic chance that it'll change in the near future.

          Justin
          • 2. Re: SQL engine cannot handle boolean return types from object_tables
            595841
            Thanks,
            Yes I have found that out.
            Just because the "parser" is the same, does not mean the
            engine is the same!

            Thanks for the clarification.
            • 3. Re: SQL engine cannot handle boolean return types from object_tables
              126955
              Personally I avoid using the boolean type in function returned type, simply for it can't be used in SQL.
              • 4. Re: SQL engine cannot handle boolean return types from object_tables
                Billy~Verreynne
                Nothing wrong with your approach until "method named "is_true" which returns a boolean data type".

                As boolean is not supported in the SQL engine, this method cannot be called by the SQL engine as it (from the SQL engine perspective) deals with an unknown type.

                You may want to consider creating a class method that can be used to compare against - using that as the TRUE and FALSE keywords in SQL.

                The biggest problem I have with SQL classes (aka object/advance data types) in Oracle, is the inability to call instance and class methods from SQL. Not sure why this does not work as PL/SQL function calls can be made, so why not function methods?

                Anyway, here is the more correct approach to implementing a boolean class.. just a pity that one has to write PL/SQL function wrappers for the class methods.

                SQL> create or replace type TBoolean as object
                2 (
                3 value number,
                4
                5 static function True return number deterministic,
                6 static function False return number deterministic
                7 );
                8 /

                Type created.

                SQL>
                SQL> create or replace type body TBoolean as
                2
                3 static function True return number is
                4 begin
                5 return(0);
                6 end;
                7
                8 static function False return number is
                9 begin
                10 return(1);
                11 end;
                12
                13 end;
                14 /

                Type body created.

                SQL>
                SQL>
                SQL> create table testtab(
                2 emp_id number,
                3 married TBoolean
                4 )
                5 /

                Table created.

                SQL>
                SQL>
                SQL>
                SQL> -- unfortunately the following does not work as SQL does not allow calling of object or class
                SQL> -- methods (which kinda sucks ito proper o-o use in SQL)
                SQL> insert into testtab values( 1, TBoolean(TBoolean.True) );
                insert into testtab values( 1, TBoolean(TBoolean.True) )
                *
                ERROR at line 1:
                ORA-00984: column not allowed here


                SQL> -- and this does not make sense either:
                SQL> insert into testtab values( 1, TBoolean(0) );

                1 row created.

                SQL> insert into testtab values( 2, TBoolean(1) );

                1 row created.

                SQL> rollback;

                Rollback complete.

                SQL>
                SQL>
                SQL> -- to workaround this issue, we create two PL/SQL function wrappers for the class
                SQL> -- functions
                SQL> create or replace function boolTrue return TBoolean is
                2 begin
                3 return(
                4 new TBoolean( TBoolean.True )
                5 );
                6 end;
                7 /

                Function created.

                SQL>
                SQL> create or replace function boolFalse return TBoolean is
                2 begin
                3 return(
                4 new TBoolean( TBoolean.False )
                5 );
                6 end;
                7 /

                Function created.

                SQL>
                SQL> insert into testtab values( 1, boolTrue );

                1 row created.

                SQL> commit;

                Commit complete.

                SQL>
                SQL> select emp_id from testtab where married = boolTrue; -- should have been TBoolean.True

                EMP_ID
                ----------
                1

                SQL>
                PS. The default constructor should be overridden to check for legal constructing values or raise an exception if not. Also a pity that the VALUE property cannot be defined as private, as this means a caller can set the property to a "non legal" value.
                • 5. Re: SQL engine cannot handle boolean return types from object_tables
                  Billy~Verreynne
                  Yeehaaa!!! I'm wrong!! :-)

                  Class methods can be called from SQL. I simply had the syntax wrong (and consulting static methods in the manual did not make the syntax clear).

                  Okay, drop the above PL/SQL wrappers. No longer needed. You can use the type as follows in SQL:

                  SQL>
                  SQL> insert into testtab values( 1, TBoolean(TBoolean.True()) );

                  1 row created.

                  SQL> commit;

                  Commit complete.

                  SQL>
                  SQL> select emp_id from testtab t where t.married.value = TBoolean.True();

                  EMP_ID
                  ----------
                  1

                  SQL>
                  • 6. Re: SQL engine cannot handle boolean return types from object_tables
                    Billy~Verreynne
                    Here is a basic implementation.

                    SQL> create or replace type TBoolean as object
                    2 (
                    3 bool smallint,
                    4
                    5 static function True return number deterministic,
                    6 static function False return number deterministic,
                    7 constructor function TBoolean( bool number ) return self as result,
                    8 member function Label return varchar2
                    9 );
                    10 /

                    Type created.

                    SQL> show errors
                    No errors.
                    SQL>
                    SQL>
                    SQL> create or replace type body TBoolean as
                    2
                    3 static function True return number is
                    4 begin
                    5 return(0);
                    6 end;
                    7
                    8 static function False return number is
                    9 begin
                    10 return(1);
                    11 end;
                    12
                    13 constructor function TBoolean( bool number ) return self as result is
                    14 begin
                    15 if bool not in (TBoolean.True(), TBoolean.False() ) then
                    16 raise_application_error( -20001, 'Invalid value for TBoolean. 0=TRUE or 1=FALSE expected.' );
                    17 end if;
                    18
                    19 self.bool := bool;
                    20
                    21 return;
                    22 end;
                    23
                    24 member function Label return varchar2 is
                    25 begin
                    26 return(
                    27 case self.bool
                    28 when TBoolean.True() then 'TRUE'
                    29 when TBoolean.False() then 'FALSE'
                    30 end
                    31 );
                    32 end;
                    33
                    34 end;
                    35 /

                    Type body created.

                    SQL> show errors
                    No errors.
                    SQL>
                    SQL>
                    SQL> create table testtab(
                    2 emp_id number,
                    3 married TBoolean,
                    4 constraint married_is_bool check( married.bool in (0,1) )
                    5 )
                    6 /

                    Table created.

                    SQL>
                    SQL>
                    SQL> insert into testtab values( 1, TBoolean(TBoolean.True()) );

                    1 row created.

                    SQL> insert into testtab values( 2, TBoolean(TBoolean.False()) );

                    1 row created.

                    SQL> commit;

                    Commit complete.

                    SQL>
                    SQL> col married format a10
                    SQL> select t.emp_id, t.married.label() as "MARRIED" from testtab t where t.married.bool = TBoolean.True();

                    EMP_ID MARRIED
                    ---------- ----------
                    1 TRUE

                    SQL>
                    SQL> update testtab t
                    2 set t.married.bool = TBoolean.False()
                    3 where t.emp_id = 1;

                    1 row updated.

                    SQL>
                    SQL> select t.emp_id, t.married.label() as "MARRIED" from testtab t;

                    EMP_ID MARRIED
                    ---------- ----------
                    1 FALSE
                    2 FALSE

                    SQL>
                    SQL>
                    SQL> insert into testtab values( 2, TBoolean(123) );
                    insert into testtab values( 2, TBoolean(123) )
                    *
                    ERROR at line 1:
                    ORA-20001: Invalid value for TBoolean. 0=TRUE or 1=FALSE expected.
                    ORA-06512: at "BILLY.TBOOLEAN", line 16


                    SQL>
                    Code was corrected (thanks to padders) by:
                    Billy Verreynne
                    • 7. Re: SQL engine cannot handle boolean return types from object_tables
                      94799
                      the override failed to work correctly
                      To override the default constructor (rather than overloading it) you need to name the parameters the same as the attributes (i.e. in your case 'bool' rather than 'b').
                      • 8. Re: SQL engine cannot handle boolean return types from object_tables
                        Billy~Verreynne
                        Duh! <sound of hand smacking forehead>

                        (thanks, have corrected the code above)
                        • 9. Re: SQL engine cannot handle boolean return types from object_tables
                          94799
                          I don't think either problem was obvious - like rather a lot of things about Oracle object types, which can unfortunately make them unnecessarily impenetrable.
                          • 10. Re: SQL engine cannot handle boolean return types from object_tables
                            Billy~Verreynne
                            Yeah, but still I did think that there must be a WHY that explains the fact there are two signatures (and I was looking at the parameter data type for some explanation).. and the answer was simply because the parameter name for each signature is different. Double duh!

                            Should have been obvious... especially as I've done this type of thing before.

                            Getting old.. or not enough caffeine.. or maybe both? <scared look>

                            ;-)
                            • 11. Re: SQL engine cannot handle boolean return types from object_tables
                              595841
                              All,

                              Thank you for your interest and suggestions. I have modified my
                              approach (some what). I now return a number to represent a
                              true and false value, as some of you have suggested.

                              I hope Oracle development considers to moving the SQL and
                              PL/SQL engines closer to compatibility with each other (e.g.
                              like the same!?) My take on it, is that the closer they come
                              the easier the developer's job will be - less exceptions between
                              the two, to have to remember and code for.

                              Thank you everyone for your help.
                              • 12. Re: SQL engine cannot handle boolean return types from object_tables
                                William Robertson
                                > I thought the SQL and PL/SQL engines were now the same code? Then why does the SQL engine not recognize a boolean return value?

                                As I understand it the "common parser" is a PL/SQL feature promising to allow the use of most SQL statements within PL/SQL code, and not the other way around. There has never been any claim to support all the features of PL/SQL in SQL, which is why you can't use %TYPE in CREATE TABLE and so on.

                                What puzzles me though is that if SQL supported the full PL/SQL feature set, surely you wouldn't need to create a "bool" object type in the first place.
                                • 13. Re: SQL engine cannot handle boolean return types from object_tables
                                  Billy~Verreynne
                                  > I hope Oracle development considers to moving the SQL and
                                  PL/SQL engines closer to compatibility with each other (e.g.
                                  like the same!?)

                                  No. Me no like. Why? Because it seems to me that there is confusion about what PL/SQL is.

                                  SQL is a Turing-incomplete language tool for addressing a specific scope of problems. Accessing and processing data in the RDBMS.

                                  PL is a formal procedural and Turing-complete language that is a tool for addressing a specific scope of problems. Procedural and processing logic.

                                  Why 2?

                                  PL does a specific job. SQL does a specific job. These jobs are not the same.

                                  Yes, you can use PL to do stuff that you can do in SQL. For example, you can code a nested loop join in PL and not use SQL for that.

                                  You can also use a Screwdriver as a Hammer.. or a Hammer as a Screwdriver.. with great difficulty.

                                  But that is just plain stupid, isn't it?

                                  PL/SQL integrates the PL and SQL languages in seamless fashion.. which is why it is so powerful and flexible when it comes to crunching Oracle data.

                                  How can they become "closer" to one another? They are already so tightly integrated many developers think that PL/SQL is a single programming language.

                                  Should SQL support PL programming structures? No. That does not make any sense.

                                  Should SQL data types be extended to support additional common relational data types like boolean? That makes sense.

                                  But arguing that PL data types (or language syntax) need to be supported in SQL, is seriously missing the point of what SQL data types are, how much superior they are than PL data types, and why it does not make sense functionally or technically to support PL types (like record structures, etc) in SQL.

                                  PL supports SQL. Not vice versa.
                                  • 14. Re: SQL engine cannot handle boolean return types from object_tables
                                    595841
                                    I agree that the SQL engine and PL/SQL engine are two different implementations to different sets of constraints. The sets of constraints have some things in common and many that are not. A major attribute of SQL is its ability to do parallel processing. PL/SQL seems to be primarily sequential like most procedural languages. Each tries to do best for what it is designed.

                                    When I write SQL I think in terms of sets and transformations. When I write PL/SQL I think in terms of processes and procedural logic.

                                    I would not like to see the constraints that make PL/SQL procedural and sequential be imposed upon SQL. I can see adding a BOOLEAN data type would enhance it.

                                    There are many areas in both domains being addressed by each implementation which allow for future enhancements. I hope Oracle will address these areas and make development easier. I am not looking for GUI front-end tools, but enhancements to the languages and what they can do. Some of these areas are lie in the ways current features are implemented. Others areas would expand on the set of problems which can be addressed.
                                    1 2 Previous Next