?
Solved

VARCHAR2 more than 32767

Posted on 2001-07-03
7
Medium Priority
?
2,330 Views
Last Modified: 2006-11-17
I need to reset the size of VARCHAR2 of column fields to 64000. But I found that VARCHAR2 can hold only 32000 odd characters. I am using Oracle 8i (8.1.5). How shall I use VARCHAR2 here. Note I do not want BFILE.

Currently my table VARCHAR2 is of 2000 size (Don't ask why I need to jump to 64000). Please also put your comments about that if storing such a large size data directly in the record is used in normal practice. What ARE the correct ways of doing such thing. Thanks/
0
Comment
Question by:javaq092999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 2

Accepted Solution

by:
renuraj earned 200 total points
ID: 6247788
CLOB datatype can be used, which holds the upto 2GB long.

Data can be manipulated into CLOB type as same as VARCHAR2 data type.

Ex:

create table test(n number,t clob)

insert into test VALUES(10,'osaeotjjjsjdpjfj')

update test set t = 'asdkfkldklkfl'
where n = 10

delete test where n = 10

All the data manipulations to CLOB type can be done in very usual manner.

Regards,
0
 

Author Comment

by:javaq092999
ID: 6247839
Right. CLOB can be used here. Can I change the data type of my field from VARCHAR2 to CLOB without loosing the data.

Can I limit the CLOB size to just 64000 ie do not allow more than 64K. The reason is that my Java Application uses String to store the data of a Oracle VARCHAR2 and String can store upto 64K chars only (I need to reconfirm myself on it). If I can do both of the above possibly CLOB is what I can use. Thanks/
0
 
LVL 2

Expert Comment

by:renuraj
ID: 6247905
A VARCHAR2 column cannot be changed to CLOB type.  But there is a work around for this.
Test - initial table with VARCHAR2
test2 - table with CLOB type

create table test(n number, t varchar2(100))

insert into test values(10,'sjfdkkasjdkfj')

create table test2(n number, t clob)

insert into test2 select * from test

rename test to test1

rename test2 to test

Now the table with TEST includes the t column as CLOB.

By default the CLOB type can store upto 2GB.  but the same can be limited by the means of inputting method like if u are using the frontend for inserting, updating then the textitem representing the CLOB column is limited to 64k, then it should serve your purpose.

One more method is by implementing before insert and before update triggers restricting the length of the CLOB  column to 64k.

Regards,
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 2

Expert Comment

by:vanmeerendonk
ID: 6247911
CLOB = 4 Gig
For what i know you can't alter a varchar2 column to a CLOB; You have to recreate the table.
0
 
LVL 2

Expert Comment

by:renuraj
ID: 6248009
Hi javaq,

Here is the trigger for restricting the data length to 64K and if it is more trigger raises the exception.

create or replace trigger rstr_man
after insert or update on test
referencing OLD as old NEW as new
for each row
declare
  c CLOB;
begin
  c := :new.t;
  if dbms_lob.getlength(c) >65500 then
     raise_application_error(-20010,'Data length is more than 64K');
  end if;
end;

64k = 65536, if required you can replace the value 65500 with 65536.

Regards,
0
 

Expert Comment

by:ainoan
ID: 6248353
The max. length of a varchar2 can only be 4000 (previous to Oracle 8.x it was only 2000). As a (PL/SQL) variable 32k is allowed.  If longer is required use a clob.
0
 

Author Comment

by:javaq092999
ID: 6259330
thanks renuraj!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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‚Ķ
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

649 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