Oracle CLOB to varchar2 conversion error ORA-06502

Posted on 2011-10-11
Last Modified: 2012-05-12
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
Question by:toooki
    LVL 76

    Accepted Solution

    What is the database character set?

    If you are running a multi-byte character set you are likely using two bytes per character.

    Author Comment

    Thank you.
    I run on that database (that has myTab table):
    I get :
    NLS_RDBMS_VERSION it having varchar2(2000) as the max limit...?
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    I'm not an NLS expert.  I would look in the Globalization Docs for the limitations.

    Author Comment

    Ok, thank you.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    SQL - sum months, quarter, YTD 9 70
    oracle- 10.2.04 3 33
    sql calculate reminders 11 52
    UNIX SCP 5 28
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now