Solved

VARCHAR2 more than 32767

Posted on 2001-07-03
7
2,229 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

832 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