Solved

VARCHAR2 more than 32767

Posted on 2001-07-03
7
2,260 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

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ā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

733 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