Check Constraints
Oracle Database 12c introduces a SEARCH_CONDITION_VC
column to the CDB_
, DBA_
, ALL_
, and USER_CONSTRAINTS
views. The SEARCH_CONDITION_VC
column is a VARCHAR2
data type equivalent to the search condition in the LONG
data type SEARCH_CONDITION
column. Unfortunately, Oracle Database 11g and earlier versions requires you to convert the LONG
data type to a VARCHAR2
for the equivalent behavior. This post provides you with a function to help you do that in Oracle Database 11g.
While Oracle Database 12c let’s you check the search condition of a CHECK
constraint, with this query:
SELECT uc.constraint_name AS constraint_name , uc.search_condition_vc AS search_condition FROM user_constraints uc WHERE uc.table_name = UPPER('table_name') AND REGEXP_LIKE(uc.search_condition_vc,'search_key','i'); |
You need the following GET_SEARCH_CONDITION
function to convert the SEARCH_CONDITION
column from a LONG
data type to a VARCHAR2
data type. It uses the DBMS_SQL
package to convert the LONG
data type.
CREATE OR REPLACE FUNCTION get_search_condition ( pv_table_name VARCHAR2 , pv_column_name VARCHAR2 ) RETURN VARCHAR2 AS /* Declare local variables. */ lv_cursor INTEGER := DBMS_SQL.open_cursor; lv_feedback INTEGER; -- Acknowledgement of dynamic execution lv_length INTEGER; -- Length of the input string lv_value_length INTEGER; -- Length of the output string lv_constraint_name VARCHAR2(30); -- Constraint name lv_return VARCHAR2(32767); -- Function output lv_stmt VARCHAR2(2000); -- Dynamic SQL statement lv_long LONG; -- Dynamic LONG data type. lv_string VARCHAR2(32760); -- Maximum length of LONG data type FUNCTION return_length ( pv_table_name VARCHAR2 , pv_column_name VARCHAR2 ) RETURN VARCHAR2 IS /* Declare a target variable, because of the limit of SELECT-INTO. */ lv_long_view LONG; /* Declare a dynamic cursor. */ CURSOR c ( cv_table_name VARCHAR2 , cv_column_name VARCHAR2 ) IS SELECT uc.search_condition FROM user_constraints uc INNER JOIN user_cons_columns ucc ON uc.table_name = ucc.table_name AND uc.constraint_name = ucc.constraint_name WHERE uc.table_name = UPPER(cv_table_name) AND ucc.column_name = UPPER(cv_column_name) AND uc.constraint_type = 'C'; BEGIN /* Open, fetch, and close cursor to capture view text. */ OPEN c (pv_table_name, pv_column_name); FETCH c INTO lv_long_view; CLOSE c; /* Return the output CLOB length value. */ RETURN LENGTH(lv_long_view); END return_length; BEGIN /* Get the length of the CLOB column value. */ lv_length := return_length(pv_table_name, pv_column_name); /* Create dynamic statement. */ lv_stmt := 'SELECT uc.search_condition'||CHR(10) || 'FROM user_constraints uc INNER JOIN user_cons_columns ucc'||CHR(10) || 'ON uc.table_name = ucc.table_name'||CHR(10) || 'AND uc.constraint_name = ucc.constraint_name'||CHR(10) || 'WHERE uc.table_name = UPPER('''||pv_table_name||''')'||CHR(10) || 'AND ucc.column_name = UPPER('''||pv_column_name||''')'||CHR(10) || 'AND uc.constraint_type = ''C'''; /* Parse and define VARCHAR2 and LONG columns. */ DBMS_SQL.parse(lv_cursor, lv_stmt, DBMS_SQL.native); DBMS_SQL.define_column_long(lv_cursor,1); /* Only attempt to process the return value when fetched. */ IF DBMS_SQL.execute_and_fetch(lv_cursor) = 1 THEN DBMS_SQL.column_value_long( lv_cursor , 1 , lv_length , 0 , lv_string , lv_value_length); END IF; /* Check for an open cursor. */ IF DBMS_SQL.is_open(lv_cursor) THEN DBMS_SQL.close_cursor(lv_cursor); END IF; /* Convert the long length string to a maximum size length. */ lv_return := lv_string; RETURN lv_return; END get_search_condition; / |
Then, you can use the following query to view the full search criteria of a CHECK
constraint that matches part of a search string:
COLUMN constraint_name FORMAT A16 COLUMN search_condition FORMAT A30 SELECT uc.constraint_name AS constraint_name , get_search_condition('table_name','column_name') AS search_condition FROM user_constraints uc WHERE REGEXP_LIKE(get_search_condition('table_name','column_name'),'check_constraint_search_string','i') AND uc.constraint_type = 'C'; |
Hope this helps those looking at discovering the full search criteria of a CHECK
constraint.