convert clob to varchar2

I have a Oracle table whose column is a CLOB datatype. I want to read the content of this table as a text.

I tried select dbms_lob.substr( sqltext, 4000, 1 ) from test but this one开发者_开发问答 selects only the first 4000 bytes. How to read the entire content ? there are more than 4000 characters in the sqltext column. Please advise.

If you are using another language like Java to read the data, JDBC drivers provide ways to read CLob and Blob columns.

For using a direct SQL query on a SQL client to read such columns, it wont work out of the box as clob and blob values greater than 4000 bytes have to be read using a stream. You need to write PL/SQL to do this. Here is one useful link you can look at

http://www.dba-oracle.com/t_read_blob.htm