Oracle CLOB to varchar2 conversion error ORA-06502

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

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
toookiAsked:
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.
0
 
toookiAuthor Commented:
Thank you.
I run on that database (that has myTab table):
SELECT * FROM NLS_DATABASE_PARAMETERS
I get :
NLS_NCHAR_CHARACTERSET      AL16UTF16
NLS_RDBMS_VERSION  10.2.0.5.0

So..is it having varchar2(2000) as the max limit...?
0
 
slightwv (䄆 Netminder) Commented:
I'm not an NLS expert.  I would look in the Globalization Docs for the limitations.
0
 
toookiAuthor Commented:
Ok, thank you.
0
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.