Convert clob to varchar2

Question

I have an Oracle table whose column is a CLOB type. I want to read the contents of this table as text.

I tried select dbms_lob.substr( sqltext, 4000, 1 ) from test , but this one only selects the first 4000 bytes. How to read all content? The sqltext column contains more than 4000 characters. Please inform.

Answer 1

If you use a different language, such as Java, to read data, JDBC drivers provide ways to read CLob and Blob columns.

To use a direct SQL query on an SQL client to read such columns, it will not work out of the box, since clob and blob values ​​in excess of 4000 bytes must be read using a stream. For this you need to write PL / SQL. Here is one useful link you can see

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