Hello,

When I create a table with a not null column, Oracle implicitly
creates a check constraint "<field> is not null":

> create table AAA (B number(10) not null);
> select * from user_constraints where table_name = 'AAA';
constraint_name: SYS_C00114059
constraint_type: 'C'
search_condition: '"B" IS NOT NULL'

Since the information is already stored in user_tab_columns.nullable,
I want to omit these rows from user_constraints.
The problem is I cannot issue such query:

> select * from user_constraints where constraint_type = 'C'
and search_condition not like '% IS NOT NULL';

....because search_condition is of type LONG:

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

I've tried to cast search_condition to string using TO_CHAR() and
CAST(... AS VARCHAR2(2000)), but I was still getting the same error.

How to solve it? I'd prefer not to create any PL/SQL functions/procedures,
since I've got read-only access to the database...

Thank you!

PS. I use Oracle9i 9.2.0.1.0.

http://www.dbasupport.com/forums/sho...threadid=39607

from http://asktom.oracle.com :

GOTO a page to Bookmark Review | Bottom | Top
How to deal with long for this query June 24, 2003
Reviewer: Yong from CA, USA

Tom,

Search_condition is long type in Oracle data dictionary. How can I
construct a
query to get the following results without set a condition on a long
type

SQL> select constraint_name from user_constraints
2 where search_condition not like '%NOT NULL';
where search_condition not like '%NOT NULL'
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG




Followup:

ops$tkyte@ORA920LAP> create or replace function get_search_condition(
p_cons_name in varchar2 ) return varchar2
2 authid current_user
3 is
4 l_search_condition user_constraints.search_condition%type;
5 begin
6 select search_condition into l_search_condition
7 from user_constraints
8 where constraint_name = p_cons_name;
9
10 return l_search_condition;
11 end;
12 /

Function created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> select constraint_name
2 from user_constraints
3 where get_search_condition(constraint_name) like '%NOT NULL%';

nomorekiss@yahoo.com wrote:
> ops$tkyte@ORA920LAP> create or replace function get_search_condition(
> p_cons_name in varchar2 ) return varchar2
> 2 authid current_user
> 3 is
> 4 l_search_condition user_constraints.search_condition%type;
> 5 begin
> 6 select search_condition into l_search_condition
> 7 from user_constraints
> 8 where constraint_name = p_cons_name;
> 9
> 10 return l_search_condition;
> 11 end;
> 12 /

Thanks, but:

Piotr B. wrote:
> I'd prefer not to create any PL/SQL functions/procedures,
> since I've got read-only access to the database...

This is not a direct answer to the posted question which has been
answered but it may also be of interest that all_, dba_, and
user_tab_columns contain a column named NULLABLE that can be used to
determine the NULL allowed status of a column.

HTH -- Mark D Powell --

Mark D Powell wrote:
> it may also be of interest that all_, dba_, and user_tab_columns
> contain a column named NULLABLE that can be used to
> determine the NULL allowed status of a column.

Yes, I mentioned this fact in my first post:
> the information is already stored in user_tab_columns.nullable

Unfortunately, my problem remains unsolved. I want to write some
application to export database schema from Oracle, and I don't
see an easy way to distinguish "real", user-added check constraints
(like "GENDER IN ('M', 'F')") from automatically generated and
redundant "column IS NOT NULL" constraints for every non-null column.

Creating PL/SQL procedures/function to cast
user_constraints.search_condition to varchar2 in order to apply
"NOT LIKE '% IS NOT NULL'" is not possible when you have read-only
access to the database...

On Fri, 28 Jan 2005 18:33:52 +0100, "Piotr B."
<piotrb@nie.spamuj.mnie> wrote:

>Unfortunately, my problem remains unsolved. I want to write some
>application to export database schema from Oracle, and I don't
>see an easy way to distinguish "real", user-added check constraints
>(like "GENDER IN ('M', 'F')") from automatically generated and
>redundant "column IS NOT NULL" constraints for every non-null column.

The constraints are not redundant.
Also the constraint name will always start with SYS_C
You also shouldn't 'write some application' as Oracle comes with
a export in any version
b dbms_metadata in 9i and higher

Looks like you would better concentrate on getting familiar with
Oracle, than reinventing the wheel.


--
Sybrand Bakker, Senior Oracle DBA

Piotr B. wrote:

> Mark D Powell wrote:
>
>> it may also be of interest that all_, dba_, and user_tab_columns
>
> > contain a column named NULLABLE that can be used to
>
>> determine the NULL allowed status of a column.
>
>
> Yes, I mentioned this fact in my first post:
> > the information is already stored in user_tab_columns.nullable
>
> Unfortunately, my problem remains unsolved. I want to write some
> application to export database schema from Oracle, and I don't
> see an easy way to distinguish "real", user-added check constraints
> (like "GENDER IN ('M', 'F')") from automatically generated and
> redundant "column IS NOT NULL" constraints for every non-null column.
>
> Creating PL/SQL procedures/function to cast
> user_constraints.search_condition to varchar2 in order to apply
> "NOT LIKE '% IS NOT NULL'" is not possible when you have read-only
> access to the database...

What is the point of this exercise? There is not a bit of difference
between a NOT NULL and a NOT NULL.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

As Sybrand points out dbms_metadata can be used to generate the souce
as can a full export with no rows plus some edit scripts to extract or
reformat the code.

As Daniel points our a "NOT NULL" is a not null so why not just use the
"nullable" column to always generate a "not null" entry in the create
table? Then you could ignore all type "C" not null constraints.
HTH -- Mark D Powell --