?
Solved

column with size varchar2(32767)

Posted on 2007-10-12
6
Medium Priority
?
2,748 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:samir25
6 Comments
 
LVL 9

Assisted Solution

by:konektor
konektor earned 400 total points
ID: 20063597
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
 
LVL 9

Expert Comment

by:konektor
ID: 20063683
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
 
LVL 1

Assisted Solution

by:Geraint_M_Jones
Geraint_M_Jones earned 400 total points
ID: 20063823
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 27

Accepted Solution

by:
sujith80 earned 1000 total points
ID: 20064335
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
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 100 total points
ID: 20065078
>> 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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 100 total points
ID: 20067533
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

864 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