0

query

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

error message

ORA-00997: illegal use of LONG datatype 00997. 00000 - "illegal use of LONG datatype" *Cause:
*Action: Error at Line: 1 Column: 62

amdixon
  • 3,814
  • 8
  • 25
  • 34
Deepesh kumar Gupta
  • 884
  • 2
  • 11
  • 29
  • `search_condition` is a long : [oracle ALL_CONSTRAINTS docs](http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022) – amdixon May 08 '15 at 11:53
  • Duplicate: http://stackoverflow.com/questions/24865539/query-to-find-constraint-by-search-condition, http://stackoverflow.com/questions/7662798/query-oracle-constrain-after-search-conditions-value, http://dba.stackexchange.com/questions/75725/inconsistent-type-after-querying-search-condition-column-in-all-constraints-tabl – Mark Leiber May 08 '15 at 12:36

1 Answers1

-1

You can't use a long datatype in the where clause. The post below has a solution using a function that converts long to 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;
/
xray
  • 1
  • 1