Re: CLOB > 4k

From: MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
Date: Thu, 11 Jun 2015 11:11:27 -0400
Message-ID: <CAAaXtLCMQFrYab_3DapiVtC0kyGv6yLKM1HcUkYKJeOnnoCUOQ_at_mail.gmail.com>



That sounds like something you can safely store as text. Of course, if it is always numbers, then maybe you should store them as numbers.

On Thu, Jun 11, 2015 at 10:44 AM, Jose Soares Da Silva < jose.soares_at_sferacarta.com> wrote:

> Hi Mark,
>
> What I'm trying to do is the following:
> I have a long string often longer than 4k
> that string is a list of numbers like:
> 3122,433434,1212,2,1212,12123,456,67445,122349,7875,87,34,765,....
> I was thinking to manage it using split(',') and ','.join()
> thinking better I can insert single values separated on more columns as
> in:
>
> 3122
> 433434
> 1212
> 2
> 1212
> 12123
> 456
> 67445
> 122349
> 7875
> 87
> 34
> 765
> ....
>
>
>
>
>
>
> On 11/06/2015 15:34, MARK BRINSMEAD wrote:
>
> 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 - 17:11:27 CEST

Original text of this message