Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Unanswered: Converting CLOB to Varchar2(4000)

    I have a table TEST_TABLE with following structure-

    Column1 - ID (int)
    Column2 - Text (clob)


    Inserted the value having 4000 characters in text column as follows-

    INSERT INTO TEST_TABLE VALUES (1, rpad('x',4000,'x'));
    Updated the text column to increase the character value to 8000

    UPDATE TEST_TABLE set TEXT=TEXT||TEXT
    So the following query will return '8000'

    SELECT length(TEXT) FROM TEST_TABLE; -- Returns 8000 as expected
    As we cannot order by 'CLOB' column, I am casting the column to varchr before using order by as follows

    SELECT ID FROM TEST_TABLE
    ORDER BY cast(TEXT AS varchar2(100))
    it is giving error

    Code:
    ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: string, maximum: string)
    Can anybody suggest a way to achieve the order by

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try this:
    Code:
    SELECT ID FROM TEST_TABLE
     ORDER BY DBMS_LOB.SUBSTR(TEXT,1,100)
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2012
    Posts
    15
    Great !!! it worked

Tags for this Thread

Posting Permissions

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