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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not an NLS expert. I would look in the Globalization Docs for the limitations.
ASKER
Ok, thank you.
ASKER
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...?