Boolean Field in Oracle

by

I found this link useful.

Here is the paragraph highlighting some of the pros/cons of each approach.

The most commonly seen design is to imitate the many Boolean-like
flags that Oracle’s data dictionary views use, selecting ‘Y’ for true
and ‘N’ for false. However, to interact correctly with host
environments, such as JDBC, OCCI, and other programming environments,
it’s better to select 0 for false and 1 for true so it can work
correctly with the getBoolean and setBoolean functions.

Basically they advocate method number 2, for efficiency’s sake, using

  • values of 0/1 (because of interoperability with JDBC’s getBoolean() etc.) with a check constraint
  • a type of CHAR (because it uses less space than NUMBER).

Their example:

create table tbool (bool char check (bool in (0,1));
insert into tbool values(0);
insert into tbool values(1);`

More Related Contents:

  1. What is the difference between varchar and varchar2 in Oracle?
  2. Is there any boolean type in Oracle databases?
  3. Printing the value of a variable in SQL Developer
  4. Oracle Equivalent to MySQL INSERT IGNORE?
  5. null vs empty string in Oracle [duplicate]
  6. Convert comma separated string to array in PL/SQL
  7. UTL_FILE.FOPEN() procedure not accepting path for directory?
  8. Oracle stored procedure with parameters for IN clause
  9. ways to avoid global temp tables in oracle
  10. Split function in oracle to comma separated values with automatic sequence
  11. ORA-06508: PL/SQL: could not find program unit being called
  12. How to handle to_date exceptions in a SELECT statment to ignore those rows?
  13. How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?
  14. Difference between VARCHAR2(10 CHAR) and NVARCHAR2(10)
  15. HIbernate issue with Oracle Trigger for generating id from a sequence
  16. Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?
  17. error: ORA-65096: invalid common user or role name in Oracle database
  18. Using Alias in query resulting in “command not properly ended”
  19. Spring boot Hibernate error “Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set” when working with multiple data sources
  20. How to insert a column in a specific position in oracle without dropping and recreating the table?
  21. Is it possible to kill a single query in oracle without killing the session?
  22. oracle “table or view does not exist” from inside stored procedure
  23. Get the LENGTH of a LONG RAW
  24. MySQL equivalent of ORACLES rank()
  25. Reading clob line by line with pl\sql
  26. Oracle: Days between two date and Exclude weekdays how to handle negative numbers
  27. How to convert “1985-02-07T00:00:00.000Z” (ISO8601) to a date value in Oracle?
  28. How to generate a version 4 (random) UUID on Oracle?
  29. Oracle SQL Developer: Show REFCURSOR Results in Grid?
  30. Find out the history of SQL queries

Leave a Comment