如何从带有constraint_name的user_constraints中选择SEARCH_CONDITION？

Question

查询

SELECT * FROM DBA_constraints WHERE constraint_type= 'C' AND search_condition = 'SEARCH_CONDITION' and table_name = 'TABLE-NAME';

错误消息

ORA-00997:非法使用长数据类型00997.00000 -“非法使用长数据类型”*原因： *行动:行错误:1栏: 62

Answer 1

不能在where子句中使用长数据类型。下面的文章有一个解决方案，它使用一个将long转换为varchar的函数。

http://blog.mclaughlinsoftware.com/2014/10/21/check-constraint-conditions/

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;
/