Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Question Unanswered: Oracle Boolean data type

    Greetings,
    Since Oracle Table structures do not support a boolean data type,
    I am faced with a choice (Make it a varchar2(1) or Number(1)), with contents of
    Y,N or 1,0

    The questions I have are:

    1. From a comparison perspective, which provides better performance? Comparison of a number or string?
    2. Which solution takes up more storage? Number(1) or Varchar2(1)
    3. The results will be mapped into a J2EE data structure supporting a boolean datatype. Which method (from a performance perspective) is better. Number to boolean or DECODE(boolstring, 'Y', 1, 'N', 0)
    Many thanks in advance for your thoughts!

    Randy

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Since Oracle Table structures do not support a boolean data type
    HUH?
    http://download-west.oracle.com/docs....htm#sthref779

    BOOLEAN Datatype

    You use the BOOLEAN datatype to store the logical values TRUE, FALSE, and NULL (which stands for a missing, unknown, or inapplicable value). Only logic operations are allowed on BOOLEAN variables.

    The BOOLEAN datatype takes no parameters. Only the values TRUE, FALSE, and NULL can be assigned to a BOOLEAN variable.

    You cannot insert the values TRUE and FALSE into a database column. You cannot select or fetch column values into a BOOLEAN variable. Functions called from a SQL query cannot take any BOOLEAN parameters. Neither can built-in SQL functions such as TO_CHAR; to represent BOOLEAN values in output, you must use IF-THEN or CASE constructs to translate BOOLEAN values into some other type, such as 0 or 1, 'Y' or 'N', 'true' or 'false', and so on.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    agreed regarding pl/sql support this datatype. The questions surround the performance impact of defining the boolean type as either a number(1) or varchar2(1) in the database.

    thanks!

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Just to be clear, it is the SQL language that has no Boolean datatype, rather than just tables.

    Regarding the size of strings vs numbers:
    Code:
    SELECT DUMP(1) AS "INTEGER"
         , DUMP(CAST('1' AS VARCHAR2(1))) AS "VARCHAR2"
         , DUMP(CAST('1' AS CHAR(1))) AS "CHAR"
    FROM   dual;
    
    INTEGER              VARCHAR2             CHAR
    -------------------- -------------------- --------------------
    Typ=2 Len=2: 193,2   Typ=1 Len=1: 49      Typ=96 Len=1: 49
    I would generally go for a VARCHAR2(1) containing 'Y'/'N' or, if it made sense, 'Y'/null (i.e. only flag the rows where the indicator is 'true'). However I would need to be sure of how the column was going to be used. Also 'Y' and 'N' rather assume English.

    I don't know what will work best in Java, though.

  5. #5
    Join Date
    May 2005
    Location
    Hull
    Posts
    22
    Are you talking about 10g Express? If so then you are correct, it doesnt have a Boolean datatype but you can emulate it with char(1) or varchar2(1).
    "I hate quotes, tell me what you know"

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by DA Master
    Are you talking about 10g Express? If so then you are correct, it doesnt have a Boolean datatype but you can emulate it with char(1) or varchar2(1).
    I don't think anyone is talking about any specific Oracle version.

    Express Edition, like any other Oracle version except possibly Oracle Lite, does have a Boolean datatype, just not within the SQL language.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •