Link to home
Start Free TrialLog in
Avatar of toooki
toooki

asked on

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). User generated image
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of toooki
toooki

ASKER

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...?
I'm not an NLS expert.  I would look in the Globalization Docs for the limitations.
Avatar of toooki

ASKER

Ok, thank you.