4 Replies Latest reply: Jul 21, 2010 3:42 AM by 565226 RSS

    How to view clob data using sql

    565226
      Hi,

      In our database, we have one table that is having one column of CLOB datatype but now i want to view the data using sql select query but it throws error: "Datatype not supported".

      Could any one please let me know how to view the clob data using select query.

      Oracle DB version : 10.2.0.3

      Thanks
        • 1. Re: How to view clob data using sql
          Saubhik
          Where are you querying your data ? In SQL*PLUS ?. Check your client version.
          • 2. Re: How to view clob data using sql
            elegantbuddy
            h5.
            use read procedure
            PROCEDURE READ (
            lobsrc IN BFILE|BLOB|CLOB ,
            amount IN OUT BINARY_INTEGER,
            offset IN INTEGER,
            buffer OUT RAW|VARCHAR2 );
             example  Updating LOB by Using DBMS_LOB in PL/SQL
            DECLARE
            lobloc CLOB; -- serves as the LOB locator
            text VARCHAR2(32767):='Resigned: 5 August 2000';
            amount NUMBER ; -- amount to be written
            offset INTEGER; -- where to start writing
            BEGIN
            SELECT resume INTO lobloc
            FROM employees
            WHERE employee_id = 405 FOR UPDATE;
            offset := DBMS_LOB.GETLENGTH(lobloc) + 2;
            amount := length(text);
            DBMS_LOB.WRITE (lobloc, amount, offset, text );
            text := ' Resigned: 30 September 2000';
            SELECT resume INTO lobloc
            FROM employees
            WHERE employee_id = 170 FOR UPDATE;
            amount := length(text);
            DBMS_LOB.WRITEAPPEND(lobloc, amount, text);
            COMMIT;
            END;
            • 3. Re: How to view clob data using sql
              fjfranken
              user562223 wrote:
              Hi,

              In our database, we have one table that is having one column of CLOB datatype but now i want to view the data using sql select query but it throws error: "Datatype not supported".

              Could any one please let me know how to view the clob data using select query.

              Oracle DB version : 10.2.0.3

              Thanks
              Usually a to_char(CLOBDFIELD) does the trick

              HTH,

              FJFranken
              • 4. Re: How to view clob data using sql
                565226
                Hi FJFranken,

                The trick to_char(clobdbfield) works and i am able to view the data now.
                Thanks for the answer.

                Thanks