Re: (no subject)

From: Andrew Babb <andrewb_at_mail.com>
Date: 1999/03/18
Message-ID: <36F05B16.239B0095_at_mail.com>#1/1


Daniel,

Presumably, you are working in Oracle7 here. The limit for a VARCHAR2 within the database is 2000 as you correctly say, but within PL/SQL and other products the limit is 32000 if I remember correctly. A string of this length can be inserted into the database, but the datatype is a LONG. This means, that under Oracle7.x you will not be able to search this column using SQL, and most of the friendly functions UPPER, SUBSTR, INSTR, NVL are lost as well. Also, you are only allowed 1 LONG column per table.

The alternative to using the LONG column is to use some form of CHILD table.

i.e.

CREATE TABLE CLIENT
( CLIENT_ID NUMBER NOT NULL PRIMARY KEY
, NAME VARCHAR2(20) NOT NULL
, .....
)

CREATE TABLE CLIENT_TEXT
( CLIENT_ID NUMBER NOT NULL

, TEXT_PART NUMBER NOT NULL
, TEXT VARCHAR2(2000) NOT NULL
, PRIMARY KEY ( CLIENT_ID , TEXT_PART)
, FOREIGN KEY CLIENT_ID references CLIENT ( CLIENT_ID )
)

Now when you want to insert the long text field, the application cuts the screen field up into multiple parts and then inserts them into the CLIENT_TEXT field.

Also, if you know you only have a VARCHAR2(4000), then you can simply have two fields added to the CLIENT table as follows;

CREATE TABLE CLIENT
( CLIENT_ID NUMBER NOT NULL

, .....
, TEXT_P1 VARCHAR2(2000)
, TEXT_P2 VARCHAR2(2000)

)

Hope these options help

Andrew

Daniel Kim wrote:

> I'm trying to insert a varchar2 of the length 4000
> now oracle complains saying that the maximum length of a literal can
> only be 2000
> how would I insert something longer?
> Thanks in advance
> Daniel Kim
Received on Thu Mar 18 1999 - 00:00:00 CET

Original text of this message