column with size varchar2(32767)

hi
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?

thanks
LVL 1
samir25Asked:
Who is Participating?
 
SujithConnect With a Mentor Data ArchitectCommented:
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.
0
 
konektorConnect With a Mentor Commented:
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.
0
 
konektorCommented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Geraint_M_JonesConnect With a Mentor Commented:
here is a good list of Oracle data types : http://www.ss64.com/orasyntax/datatypes.html

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.
0
 
paquicubaConnect With a Mentor Commented:
>> 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
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.