Sounds like you will need to have a custom function to manipulate the
data in any case. A few thoughts:
Until 12c, the longest Varchar2 is 4,000 (not 4K) *bytes*
(http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF50950)
If you are guaranteed to be less than 8,000 bytes, since this likely
needs to be manipulated by function, it just might be easier to code
against *two* 4,000 byte varchar2 fields rather than a CLOB. (Or 3
columns, if guaranteed to be less than 12,000 bytes. Beyond that, it
becomes a burden.)
An alternate, depending on what you need downstream, might be similar to
how Oracle handles the 'polygon' field in their standard sdo_geometry
datatype. The have an object (= column) that consists of 5 fields, the
last of which is a VARRAY that holds up to 1,000,000 (now, optionally up
to 10M) numbers of datatype NUMBER. I am suggesting you look at VARRAY
(http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#i46425)
but recommending you review the way it is used in SDO_GEOMETRY
(http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#sthref288)
to provide some potential interesting insight.
/Hans
On 11/06/2015 8:44 AM, Jose Soares Da Silva 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 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@xxxxxxxxx <mailto:martin.a.berger@xxxxxxxxx>> 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@xxxxxxxxxxxxxx <mailto:jose.soares@xxxxxxxxxxxxxx>>:
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@xxxxxxxxx <mailto:mgogala@xxxxxxxxx> 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
