I'm trying to query the SYS.ALL_CONSTRAINTS view to see which check constraints exist, but exclude those that contain the text "IS NOT NULL" in the search_condition field.
I have the following code so far:
CURSOR c_check_constraints (tableName VARCHAR2) IS
FROM USER_CONS_COLUMNS, USER_CONSTRAINTS
WHERE USER_CONS_COLUMNS.CONSTRAINT_NAME =
AND USER_CONSTRAINTS.TABLE_NAME = tableName
AND USER_CONSTRAINTS.CONSTRAINT_TYPE = 'C';
AND USER_CONSTRAINTS.SEARCH_CONDITION NOT
LIKE '%IS NOT NULL'
I get an error message about inconsistent datatypes - i.e. I can't do a comparison of a string on a LONG column (search_condition). We're on Oracle 7.3.4 and don't want to have to install any further products (apparently ConText and Intermedia will do the conversion) if at all possible. Is it possible to change this last criteria of the where clause in order for it to bring back only those constraints that don't include "IS NOT NULL"?
Thanks for any help,
OK, worked it out. Rather than limiting the results returned by the SELECT statement, I wait until I process the cursor. During this loop I put the value of the LONG column into a varchar2 variable, at which point Oracle does an implicit conversion anyway! I can then do a comparison and execute statements dependent on the outcome.
Here are some of the limitations on longs:
*pl/sql --> 32Kb for size of long
*sql command --> 64Kb
*sqlloader ---> 64Kb
other Limitations on longs listed in the Application Developers Guide:
*Only one LONG column allowed per table
*LONG column cannot be indexed
*LONG columns cannot appear in integrity constraints
*LONG columns cannot be used in WHERE,GROUP BY,
ORDER BY, or CONNECT BY clauses, or with the DISTINCT
operator in SELECT statements.
*LONG columns cannot be used referenced by SQL functions
(such as SUBSTR or INSTR).
*LONG columns cannot be used in the SELECT list of a
subquery or queries combined by set operators (UNION,
UNION ALL, INTERSECT, or MINUS).
*LONG columns cannot be used in SQL expressions.
*LONG columns cannot be referenced when creating a table
with query (CREATE TABLE...AS SELECT...) or when inserting
into a table(or view) with a query (INSERT INTO ... SELECT...).
*LONG columns cannot be referenced in a sub-query
A variable or argument of a PL/SQL program unit cannot be
declared using the LONG datatype.
With all those limitations, why was it used by Oracle when implementing these system tables (e.g. sys.cdef$.condition) then? Surely another more suitable type exists?
Click Here to Expand Forum to Full Width