convert clob to varchar2

Question

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.

Answer 1

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