?
Solved

VARCHAR2 more than 32767

Posted on 2001-07-03
7
Medium Priority
?
2,304 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
Industry Leaders: 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!

 
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

Technology Partners: 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 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.
Suggested Courses

752 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