Solved

VARCHAR2 more than 32767

Posted on 2001-07-03
7
2,277 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 50 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

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!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

688 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