This content has been marked as final. Show 4 replies
You have a table called "table"?!?
Why don't you give us the exact copy/paste?
Did you try to select less than 4000 bytes? (SQL*Plus may have a restriction)
SELECT DBMS_LOB.SUBSTR(col1,3000,1) FROM table WHERE NUM=100 /
Yes i tried with 4000 IT is working fine.
What you are trying to tell is Clob to varchar2 conversion happening here. it wont exceed 4000. this is ok.
Why you think of varchar2? does dbms_lob.substr can convert clob to long?
Yes and No.
In this example, you are using dbms_lob.substr in a plain SQL statement. In SQL, a varchar2 has a max limit of 4000 characters. In PL/SQL, a varchar2 can hold up to 32K characters. The limitation to 4000 chars is of the SQL.
By its definition, the function dbms_lob.substr is returning a varchar2 (or raw) though no, you can not convert it into a long.