CODEWITHSUNDEEP
pythonoracle-databaseoracle11gsqlalchemy
Jordan Jambazov
Jordan Jambazov

Reputation: 3620

SQLAlchemy type containing strings larger than 4000 on Oracle using Varchar2

I am using Oracle database and in a certain column I need to insert Strings, which in some cases are larger than 4000 symbols (Oracle 11g limits Varchar2 size to 4000). We are required to use Oracle 11g, and I know about the 12g extended mode. I would not like to use the CLOB datatype for performance considerations. The solution that I have in mind is to split the column and write a custom SQLAlchemy datatype that writes the data to the second column in case of string larger than 4000.

So, my questions are:

  1. Are we going to gain any significant performance boost from that (rather than using Clob)?
  2. How should that SQLAlchemy be implemented? Currently we are using types.TypeDecorator for custom types, but in this case we need to read/write in two fields.

Upvotes: 0

Views: 1083

Answers (1)

yǝsʞǝla
yǝsʞǝla

Reputation: 16412

CLOB or NCLOB would be the best options. Avoid splitting data into columns. What would happen when you have data larger than 2 columns - it will fail again. It also makes it maintenance nightmare. I've seen people split data into rows in some databases just because the database would not support larger character datatypes (old Sybase versions). However, if your database has a datatype built for this purpose by all means use it.

Upvotes: 1

Related Questions