Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Posts
    3

    Question Unanswered: UNION for Columns of CLOB data type

    Hi,
    I have two tables Encounter & Encounter_History. They have same columns. One column is of type CLOB. My requirement is to retrieve all the distinct records from both the tables with order by a date column. But problem is, UNION does not work in case of CLOB data type.

    I know it will work if I use UNION ALL, but it returns duplicate records.

    Please give me suggestion, how to solve this problem.

    For example: The following query does not work since column1 is a CLOB data type

    select column1 from table1
    union
    select column1 from table2

    Thanks
    Last edited by monoranjang; 05-08-07 at 03:35.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    did you try

    select distinct column1
    from
    (select column1 from table1
    union all
    select column1 from table2);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2007
    Posts
    3

    UNION for Columns of CLOB data type

    In that case also it does not work. I get the same error, as I was getting while using UNION only.

    Error report:
    SQL Error: ORA-00932: inconsistent datatypes: expected - got CLOB

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You can use a CAST AS VARCHAR2(4000) but it won't work if some of the CLOB values are longer than 4000 bytes :
    Code:
    rbaraer@Ora10g> CREATE TABLE TestVarchar2(
        IdTest VARCHAR2(4000));  2
    
    Table created.
    
    rbaraer@Ora10g> CREATE TABLE TestClob(
        IdTest CLOB);  2
    
    Table created.
    
    rbaraer@Ora10g> INSERT INTO TestVarchar2(IdTest) VALUES ('test data 1');
    
    1 row created.
    
    rbaraer@Ora10g> INSERT INTO TestVarchar2(IdTest) VALUES ('test data 2');
    
    1 row created.
    
    rbaraer@Ora10g> INSERT INTO TestClob(IdTest) VALUES ('test data 1');
    
    1 row created.
    
    rbaraer@Ora10g> COMMIT;
    
    Commit complete.
    
    rbaraer@Ora10g> SELECT IdTest
    FROM
    (
        SELECT IdTest
        FROM TestVarchar2
        UNION
        SELECT CAST(IdTest AS VARCHAR2(4000))
        FROM TestClob
    );  2    3    4    5    6    7    8    9
    
    IDTEST
    -----------------------------------------------------------------------------------------------------------------------------------
    test data 1
    test data 2
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    May 2007
    Posts
    3
    My requirement is such that column1 in both the tables is CLOB in the following query:

    select column1 from table1
    union
    select column1 from table2

    Please help .. its is very urgent.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by monoranjang
    My requirement is such that column1 in both the tables is CLOB in the following query:

    select column1 from table1
    union
    select column1 from table2

    Please help .. its is very urgent.
    The SQL Reference states that UNION is NOT supported on CLOB columns :
    Restrictions on the Set Operators

    * The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.
    * The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.
    * If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause.
    * You cannot also specify the for_update_clause with the set operators.
    * You cannot specify the order_by_clause in the subquery of these operators.
    * You cannot use these operators in SELECT statements containing TABLE collection expressions.
    So there is no way unless you cast the columns as VARCHAR2 as I shown you (you can do this on all tables instead of only one), but you would be limited to 4000 bytes long columns (which in that case should have been VARCHAR2(4000), not CLOBs ). Sorry but CLOB (and more generally LOB) columns have their limitations . You can maybe use SUBSTR(strColumn, 1, 4000) in combination with the CAST but even if it works, you would only get the first 4000 characters of each row...

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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