[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1365
  • Last Modified:

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
0
toooki
Asked:
toooki
  • 2
  • 2
1 Solution
 
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now