[Deploying Sakai] Lessons on Oracle possible issue

[Steve Swinsburg]

Ah yeah I've been doing PL/SQL for too long where it is 32767.

So either use VARCHAR2 for relatively small amounts of data, or CLOB for
large.
Don't use LONG for anything. it is restrictive and Oracle have been
advising against it since 8i (10 years ago).

The next query I have is in regards to storing the data as JSON. Whilst
that might be reasonable if its data that is always retrieved in full, then
deserialised and examined, XML may be better choice as you can use the
native database XML functions on both Oracle and MySQL for searching an
extracting without returning the whole chunk of data, which would make it
more future proof.

JSON is more of a data representation format than a format suitable for
storage in a relational database, unless the database has native support
for handling it. JSON handling in Oracle may be a future part of 12c. MySQL
seem to be adding support as well but I wouldn't be relying on it. The XML
functions are solid.

cheers,
Steve




On Fri, May 2, 2014 at 7:32 AM, Hedrick Charles <hedrick at rutgers.edu> wrote:

> clob is a result of the Hibernate change in 2.9. Things declared text were
> changed to materialized_clob.
>
> Oracle claims the maximum for varchar2 is 4000.
> http://docs.oracle.com/cd/B28359_01/server.111/b28320/limits001.htm#i287903.
> This changes to 32K in Oracle 12c. I don’t think we want to limit Lessons
> to requiring 12c.
>
> On May 1, 2014, at 4:47:44 PM, Steve Swinsburg <steve.swinsburg at gmail.com>
> wrote:
>
> If you are going to use a LONG datatype, you might as well just declare it
> as VARCHAR2(32767) as that is what you are limited to. If you are storing
> it as JSON and are unsure of the data length, I’d be using a CLOB.
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20140502/d7d37f78/attachment.html