column with size varchar2(32767)

Posted on 2007-10-12
Last Modified: 2013-12-19
i want to have a column in the table with field as varchar2(32767)...which i know is not allowed.
so the only option is clob? or anythign else is there in oracle 10g?

Question by:samir25
    LVL 9

    Assisted Solution

    such huge varchar can be defined only in PL/SQL block. column of table can have length max 4000. if you need longer data, the clob is the best way. oracle stores clobs directly with other table data if value of string is not longer than 4000 bytes, if longer then is stored in separated blocks.
    LVL 9

    Expert Comment

    one more thing ... you can define column as varchar2(10 byte) or varchar2(10 char). while you using database which is set to use UTF characterset - it's different. UTF character (like chinese,russian,latin charasters with acutes,...) is stored in 3 bytes, so defining varchar2(10 bytes) or varchar2(10) can store maximaly 3 UTF characters. defining varchar2(10 char) can store 10 UTF characters.
    LVL 1

    Assisted Solution

    here is a good list of Oracle data types :

    it doesnt mention 10g explicitly but you are safe to assume anything thats true for 9i is true for 10g

    also why would you want a varchar2(32767) ? you could have 9 varchar2(4000)'s OR a sperate table it a primary key joined to your table you need the varchar2(32767) in and store the char's in rows there, and rebuild at runtime.
    LVL 27

    Accepted Solution

    Only and best option is CLOB. It supports all the string functions and much flexible to operate on data.
    If you are sure of the pattern of data, you may consider to have multiple columns of varchar2(4000).

    Never decide to use LONG.
    LVL 23

    Assisted Solution

    >> so the only option is clob? or anythign else is there in oracle 10g?
    Yes, that's your only option, nothing else. My only advice: Study the limitations of a CLOBs vs. a VARCHAR
    LVL 34

    Assisted Solution

    The only other option is to break up your field into multiple pieces.  A table like this:

    create table text_piece
    ( id  number,
      piece number,
      text varhcar2(4000)

    This will allow you to store more than 4000 bytes without using a CLOB.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    758 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

    10 Experts available now in Live!

    Get 1:1 Help Now