Re: Displaying CLOB values

From: Joel R. Kallman <jkallman_at_us.oracle.com>
Date: Mon, 04 Oct 1999 17:07:32 GMT
Message-ID: <37f8de27.20632417_at_newshost.us.oracle.com>

On Mon, 04 Oct 1999 13:45:43 GMT, burkeblackman_at_my-deja.com wrote:

>Hello, I have a simple question about CLOBs. I know how to write CLOB
>data to a table, but using DBMS_OUTPUT to display the data doesn't
>work. How can I display CLOB type data to the screen?

When you are selecting a LOB column, you are actually selecting the LOB *locator* and not actually the data itself. Once you get back a LOB locator, you can use any of the DBMS_LOB procedures to get at the data itself.

As a simple example:

---

SQL> set serveroutput on size 1000000
SQL> create table foo (id number, text clob); Table created.
SQL> insert into foo values(1,'Joel was here'); 1 row created.
SQL> insert into foo values(2,'And Joel was here too!'); 1 row created.
SQL> commit;

SQL> declare
  2 l_lobloc clob;
  3 begin

  4      for c1 in (select id, text from foo) loop
  5          dbms_output.put_line( c1.id || ': ' ||
dbms_lob.substr(c1.text,4000,1) );
  6      end loop;

  7 end;
  8 /
1: Joel was here
2: And Joel was here too!

PL/SQL procedure successfully completed.

---

Hope this helps.
>
>Thanks,
>Burke
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

Thanks!

Joel

Joel R. Kallman Oracle Service Industries

Columbus, OH                             http://govt.us.oracle.com
jkallman_at_us.oracle.com                   http://www.oracle.com

---

The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. Received on Mon Oct 04 1999 - 19:07:32 CEST