Re: CLOB > 4k

Your issue is it under Oracle or PostgreSQL?

If it is under Oracle, I have fixed similar issue with bind variable. Or try to create procedure with argument CLOB.

2015-06-12 6:47 GMT+01:00 Jose Soares Da Silva <jose.soares_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>:
>
>> 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
>>>>
>>>>
>>>>
>>>
>>
>>
>
>
> --
> Thanks,
> Cherif Ben Henda
>
>
>
>

-- 
Cordialement,
Cherif Ben Henda

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