How to store more than 4000 char and show in oracle?

Starting with 12c you can store 32K:
https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321

I would use a CLOB.


With .Net
If you are using ODP.Net, I loved the new Managed Client, CLOBs are pretty simple to work with.

In SQL, you could could get multiple 4000 character pieces by using DBMS_LOB.SUBBSTR(field,4000,4001), DBMS_LOB.SUBBSTR(field,4000,8001), etc. You can even simulate looping and make the incrementing of the start point dynamic using connect by level or some other recursion method,

In PL/SQL, that looping is much easier and I believe you may be able to concatenate the string to get the LOB in a single column depending on your Oracle release.

DECLARE 
yourtext VARCHAR2(32000); 
BEGIN 
SELECT SUBSTR(yourstring,1,32000) INTO yourtext FROM DUAL 
-- do some work 
END; 

Select allOpen in new window

@Dhanasekaran,

The question was not just to "show" but to "store".

As far as looping to show a CLOB 4000 characters at a time likely isn't feasible in a production app.  Just about anything today will display a CLOB natively.