For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
Why the table creation tool does not include boolean and integer data types?
Should I use a binary format (like binary_float) instead of boolean?
"boolean" is not a valid data type for the Oracle database.
That is why it is not an option.
It is usually simulated with:
My_Bool_column VARCHAR2(3) REFERENCES My_Bool_Options ( Bool_Value )
or
My_Bool_column NUMBER(1) REFERENCES My_Bool_Options ( Bool_Value )
(those are Database CREATE TABLE column definition lines.)
Your LoV will be a Dynamic LoV based on a SELECT statement against the table My_Bool_Options.
This way you can dynamically change Yes/No to Oui/Non or Ja/Nein or ...
MK
Ricardo Lavezzi wrote:Why the table creation tool does not include boolean and integer data types?
Ricardo Lavezzi wrote:
Oracle SQL does not have a Boolean data type (although it is suggested that it should).
Use a NUMBER with a scale of 0 for integers.
It depends on what you're going to be using it for. NUMBER or VARCHAR2 representations are the customary implementations.
My personal preference is a linguistically neutral NUMBER(1,0) with a check constraint restricting possible values to 0 or 1.
When you manually create a table (eg SQL Workshop - SQL Commands), you can use the keywords INT and INTEGER.
CREATE TABLE T ( My_Integer INT)
CREATE TABLE T (
My_Integer INT
)
When you look at the table's DESCription, you will see that INT/INTEGER are actually implemented as a NUMBER with a precision of zero(0)
My_Integer NUMBER(38)
Oh .. don't use any of the BINARY_xxxx data types unless you can prove (via benchmarks) why it is better than a NUMBER.
By default, NUMBERs have better precision than BINARY_DOUBLEs.
To help somebody reading this thread in the future, this is how I implemented the boolean table
CREATE table "BOOLEAN_TYPE" ( "TRUE" NUMBER(1,0) default 1, "FALSE" NUMBER(1,0) default 0, "BNULL" NUMBER(1,0))/alter table "BOOLEAN_TYPE" add constraint "BOOLEAN_TYPE_CK1" check (true =1 AND false=0 AND bnull is null)/
CREATE table "BOOLEAN_TYPE" (
"TRUE" NUMBER(1,0) default 1,
"FALSE" NUMBER(1,0) default 0,
"BNULL" NUMBER(1,0)
/
alter table "BOOLEAN_TYPE" add
constraint "BOOLEAN_TYPE_CK1"
check (true =1 AND
false=0 AND
bnull is null)
FWIW in the absence of a builtin boolean type these are my preferred methods:
Method #1:
ENABLED_YN VARCHAR2(1) NOT NULL
CONSTRAINT ENABLED_YN_CK CHECK (ENABLED_YN IN ('Y','N'))
Method #2:
ENABLED_IND VARCHAR2(1)
CONSTRAINT ENABLED_IND_CK CHECK (ENABLED_IND = 'Y')
The advantages of Method #2 are that an index on the column can make finding the "enabled" rows very fast, and it maps cleanly to Apex simple checkboxes. It does mean, however, that you have to use "ENABLED_IND IS NOT NULL" to filter on "false", in addition to the normal downsides to nullable columns in general.
The main principle, of course is that whatever method you use, be consistent.
'Y' stands for "Yuck! "
'N' stands for "Nice "
Correct?
"Y" stands for "Yox" (Azerbaijani for "No")
"N" stands for "Ndiyo" (Swahili for "Yes")
