DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: LONG & VARCHAR2 comparison

  1. #1
    Join Date
    Sep 2000
    Posts
    26
    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
    SELECT USER_CONSTRAINTS.CONSTRAINT_NAME,
    USER_CONSTRAINTS.TABLE_NAME, SEARCH_CONDITION,
    CONSTRAINT_TYPE, STATUS
    FROM USER_CONS_COLUMNS, USER_CONSTRAINTS
    WHERE USER_CONS_COLUMNS.CONSTRAINT_NAME =
    USER_CONSTRAINTS.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,

    Matthew Burgess

  2. #2
    Join Date
    Sep 2000
    Posts
    26
    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.

    Matt

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.

  4. #4
    Join Date
    Sep 2000
    Posts
    26
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width