CODEWITHSUNDEEP
sqloracle-databaseboolean
FtDRbwLXw6
FtDRbwLXw6

Reputation: 28899

Oracle: How to determine if a column is a "boolean"?

I create quasi-boolean columns like this:

CREATE TABLE foo
    bar NUMBER(1) DEFAULT 0 NOT NULL CHECK (hide IN (0, 1))

I'm currently scraping user_tab_columns and would like to be able to determine whether a given column is a boolean or not. So far, I've got this:

SELECT column_name,
       (SELECT COUNT(*)
        FROM   all_constraints
        WHERE  table_name = table_name
        AND    constraint_type = 'C'
        AND    REGEXP_LIKE(search_condition, '^ *' || column_name || ' +IN *\( *0, *1 *\) *$', 'i')) is_boolean
FROM   user_tab_columns;

But I'm getting the following error:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 6 Column: 31

I've learned that this is because the search_condition data type is LONG, and REGEXP_LIKE() expects a character type, but I'm not sure how to resolve this issue.

Am I going about this the right way? If so, how do I fix the error I'm getting? If not, what is a better way of doing this?

Upvotes: 0

Views: 482

Answers (3)

Allan
Allan

Reputation: 17429

It depends on you application, but it may be sufficient to look at the column type, scale, and precision. I don't think I've ever seen a number(1) used for anything but a Boolean in an Oracle database.

Upvotes: 1

schglurps
schglurps

Reputation: 1387

You can use Oracle comments :

COMMENT ON COLUMN foo.bar is 'Boolean';

Upvotes: 3

Tony Andrews
Tony Andrews

Reputation: 132590

Yes, the LONG datatype is very difficult to work with. One way round this is to create a function to return the search condition of a constraint as a varchar2:

create or replace
function search_condition
( p_owner varchar2
, p_constraint_name varchar2
) return varchar2
is
  l_text long;
begin
  select search_condition into l_text from all_constraints
  where owner = p_owner
  and constraint_name = p_constraint_name;
  return l_text;
end;

Now you can use this in your query:

select constraint_name, search_condition from
(
select constraint_name, search_condition (owner, constraint_name) search_condition
from all_constraints
where constraint_type = 'C'
and owner = 'TONYEOR'
)
where  ...

Upvotes: 2

Related Questions