Oracle CLOB to varchar2 conversion error ORA-06502

I am running this sql query on a database (Oracle version

sql>create or replace view myView as select
DBMS_LOB.SUBSTR(F1, 4000, 1) F1 from myTab;
--F1 in myTab is LOB field.

view gets created.
sql>select * from myView;
After running for a while I get the ORA-06502 error.

Same error I get when I do:
sql>insert into myTab2(F1)  select F1 from myView;
-- F1 is Tab2 is varchar2(4000) field

However, everything works (select from view and insert into myTab2) fine when I create the original view as:
sql>create or replace view myView as select
DBMS_LOB.SUBSTR(F1, 2000, 1) F1 from myTab;

Could you suggest why I get error with varchar2(4000) , also I get error with varchar2(3000)
(in create view). screen
Who is Participating?
slightwv (䄆 Netminder) Commented:
What is the database character set?

If you are running a multi-byte character set you are likely using two bytes per character.
toookiAuthor Commented:
Thank you.
I run on that database (that has myTab table):
I get :
NLS_RDBMS_VERSION it having varchar2(2000) as the max limit...?
slightwv (䄆 Netminder) Commented:
I'm not an NLS expert.  I would look in the Globalization Docs for the limitations.
toookiAuthor Commented:
Ok, thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.