Re: CLOB > 4k
Date: Fri, 12 Jun 2015 07:47:21 +0200
Message-ID: <557A7269.6050805_at_sferacarta.com>
Here my simple pseudo code:
create table pool(
owner_id integer primary key,
longstring text -- this data type is a variable unlimited length
(PostgreSQL)
)
datalist=[1302,3122,433434,1212,2,1212,12123,456,67445,122349,7875,87,34,765,....]
mystring=','.join(datalist)
insert into pool (owner_id, longstring) values (1302, mystring);
select value from pool into data;
datalist = data.split(',')
print datalist
[1302,3122,433434,1212,2,1212,12123,456,67445,122349,7875,87,34,765,....]
j
On 11/06/2015 17:02, Cherif Ben Henda wrote:
> Hi Jose ,
>
> Could you please send us an example of your issue ?
>
>
>
> 2015-06-11 15:44 GMT+01:00 Jose Soares Da Silva
> <jose.soares_at_sferacarta.com <mailto:jose.soares_at_sferacarta.com>>:
>
> 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 insertsingle 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 <mailto: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 <mailto: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 <mailto: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 DBA
>>> http://mgogala.freehostia.com
>>
>>
>>
>
>
>
>
> --
> Thanks,
> Cherif Ben Henda
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 12 2015 - 07:47:21 CEST