Re: CLOB > 4k

From: MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
Date: Thu, 11 Jun 2015 09:34:29 -0400
Message-ID: <CAAaXtLBn91m3gubsAYXC2e8CNfRLvfWD4R=k11jVsg_Zjz9ZKw_at_mail.gmail.com>

You may want to consider -- carefully -- whether you want VARCHAR or RAW (CLOB or BLOB) for this purpose, too.

If the data you are storing is a large piece of text, then varchar/CLOB is probably correct. But if the data you are storing is a file or a document (e.g., a PDF or microsoft word document), it would probably be more appropriate to use RAW/BLOB.

Specifically, if you are storing files/documents, you *probably* do not *ever* want the database (or TNS) to perform characterset translation. Also, the data itself might well contain binary data, as well as text. In order to remain useful, you might* always* want to retrieve the data from the database byte-for-byte (bit-for-bit) identical to the way it was entered. For that, you want a binary representation, not a textual one.

Hopefully, all of your candidate database platforms will support "RAW".

On Thu, Jun 11, 2015 at 5:44 AM, Martin Berger <martin.a.berger_at_gmail.com> wrote:

> In 11.2 you can not extend VARCHAR2 beyond 4000 byte.
> If you want to be DB independent in your ORM, you limit yourself to use
> only the features available on all DBs, but obey any limitation on any DB.
> But that's another discussion ....
>
> For your specific case you need to find out what's the smallest VARCHAR2
> size in all the to_be_supported DBs is. Then create a dedicated table to
> store these (with a FK on your original table) and a method in sqlalchemy
> to split up any textfile larger into pieces with references (FK) and
> orders.
> For retrieving the data, concatenate it in another Method.
> If you need to do any string manipulation, the savings for using an ORM
> will vanish soon.
>
> sorry for the not-so-simple answer,
> Martin
>
> 2015-06-11 11:35 GMT+02:00 Jose Soares Da Silva <
> jose.soares_at_sferacarta.com>:
>
>> here is my version:
>>
>> Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
>>
>> j
>>
>>
>> On 10/06/2015 16:18, Mladen Gogala (Redacted sender mgogala_at_yahoo.com
>> for DMARC) wrote:
>>
>> Depending on your database version, you maybe able to insert 32K VARCHAR2
>> data into your database.
>>
>> On 06/10/2015 05:12 AM, Jose Soares wrote:
>>
>> Hi all,
>>
>> I need to store a lot of data into a single column (more than 4k).
>> I tried using CLOB because documentation says :
>>
>> *CLOB data type*
>>
>> *A CLOB (character large object) value can be up to 2,147,483,647
>> characters long. A CLOB is used to store unicode character-based data, such
>> as large documents in any character set.*
>>
>> *The length is given in number characters for both CLOB, unless one of
>> the suffixes K, M, or G is given, relating to the multiples of 1024,
>> 1024*1024, 1024*1024*1024 respectively. *
>> ------------------------------------
>>
>> When I tried to insert into it more than 4 k a got this error:
>>
>> (DatabaseError) ORA-24373: invalid length specified for statement
>>
>>
>> j
>>
>>
>>
>> --
>> Mladen Gogala
>> Oracle DBAhttp://mgogala.freehostia.com
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 11 2015 - 15:34:29 CEST