inconsistent type after querying search_condition column in all_constraints table Oracle

Question

I'm trying to get the not null constraints in my tables, so I'm using the following sql request :

    select * from all_constraints
    where owner='userx'
    and constraint_type='C'
    and search_condition='%IS NOT NULL';  

The problem is that I'm getting the ORA-00997: illegal use of LONG datatype exception
When looking on the Oracle 9i documentation I realize that search_condition column is of LONG datatype.

How to make use of this column ?

Answer 1

if you have xmldb installed (not sure if this will work for 9i) you can use something like this,

SELECT table_name, constraint_name, sc FROM   (SELECT  constraint_name, table_name, search_condition ,to_char(extractvalue(dbms_xmlgen.getxmltype ('select search_condition from user_constraints x' ||' where x.constraint_name  = ''||t.constraint_name||''''),'//text()')) sc FROM   user_constraints t) where sc like '%IS NOT NULL'

Answer 2

you need a package to read a long column. Sadly Oracle continues to use this datatype but does not recommend anyone else use it.

CREATE OR REPLACE PACKAGE LONG_HELP
   /******************************************************************************
      NAME:       LONG_HELP
      PURPOSE:    Read fields of type long.  (commonly found in data dictionary)

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        10/27/2011             1. Created this package. based on Tom Kyte's column here
      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:839298816582
      note  that it only retrieves the first 4000 characters of any LONG column
      USAGE in a WHERE
      INSTR(
      LONG_HELP.SUBSTR_OF('SELECT text from all_views where view_name =:o ',
      1,4000,'o',m2.obj_name),m1.FK_ID) > 0

   ******************************************************************************/


AS
   FUNCTION substr_of (p_query   IN VARCHAR2,
                       p_from    IN NUMBER,
                       p_for     IN NUMBER,
                       p_name1   IN VARCHAR2 DEFAULT NULL ,
                       p_bind1   IN VARCHAR2 DEFAULT NULL ,
                       p_name2   IN VARCHAR2 DEFAULT NULL ,
                       p_bind2   IN VARCHAR2 DEFAULT NULL ,
                       p_name3   IN VARCHAR2 DEFAULT NULL ,
                       p_bind3   IN VARCHAR2 DEFAULT NULL ,
                       p_name4   IN VARCHAR2 DEFAULT NULL ,
                       p_bind4   IN VARCHAR2 DEFAULT NULL )
      RETURN VARCHAR2;
END LONG_HELP;

--and the body

CREATE OR REPLACE PACKAGE BODY LONG_HELP
AS
   g_cursor   NUMBER := DBMS_SQL.open_cursor;
   g_query    VARCHAR2 (32765);


   PROCEDURE bind_variable (p_name IN VARCHAR2, p_value IN VARCHAR2)
   IS
   BEGIN
      IF (p_name IS NOT NULL)
      THEN
         DBMS_SQL.bind_variable (g_cursor, p_name, p_value);
      END IF;
   END BIND_VARIABLE;



   FUNCTION substr_of (p_query   IN VARCHAR2,
                       p_from    IN NUMBER,
                       p_for     IN NUMBER,
                       p_name1   IN VARCHAR2 DEFAULT NULL ,
                       p_bind1   IN VARCHAR2 DEFAULT NULL ,
                       p_name2   IN VARCHAR2 DEFAULT NULL ,
                       p_bind2   IN VARCHAR2 DEFAULT NULL ,
                       p_name3   IN VARCHAR2 DEFAULT NULL ,
                       p_bind3   IN VARCHAR2 DEFAULT NULL ,
                       p_name4   IN VARCHAR2 DEFAULT NULL ,
                       p_bind4   IN VARCHAR2 DEFAULT NULL )
      RETURN VARCHAR2
   AS
   /******************************************************************************
   NAME:       LONG_HELP.SUBSTR_OF
   PURPOSE:    CONVERT long data fields into VARCHAR2
   WHOSE DATA IS CHANGED: none
   WHAT USES THIS:
   WHERE ARE THE RESOURCES NEEDED:

******************************************************************************/
      l_buffer       VARCHAR2 (4000);
      l_buffer_len   NUMBER;
   BEGIN
      IF (NVL (p_from, 0) <= 0)
      THEN
         raise_application_error (-20002,
                                  'From must be >= 1 (positive numbers)');
      END IF;

      IF (NVL (p_for, 0) NOT BETWEEN 1 AND 4000)
      THEN
         raise_application_error (-20003, 'For must be between 1 and 4000');
      END IF;


      IF (p_query <> g_query OR g_query IS NULL)
      THEN
         IF (UPPER (TRIM (NVL (p_query, 'x'))) NOT LIKE 'SELECT%')
         THEN
            raise_application_error (-20001, 'This must be a select only');
         END IF;

         DBMS_SQL.parse (g_cursor, p_query, DBMS_SQL.native);
         g_query := p_query;
      END IF;

      bind_variable (p_name1, p_bind1);
      bind_variable (p_name2, p_bind2);
      bind_variable (p_name3, p_bind3);
      bind_variable (p_name4, p_bind4);

      DBMS_SQL.define_column_long (g_cursor, 1);

      IF (DBMS_SQL.execute_and_fetch (g_cursor) > 0)
      THEN
         DBMS_SQL.column_value_long (g_cursor,
                                     1,
                                     p_for,
                                     p_from - 1,
                                     l_buffer,
                                     l_buffer_len);
      END IF;

      RETURN l_buffer;
   END substr_of;
END LONG_HELP;
/