[Building Sakai] truncating to fit field

Fri Feb 28 07:24:52 PST 2014

>From what I've read and been told, setting NLS_LENGTH_SEMATICS globally
would affect other schemas, rather than just being specific to the Sakai
schema. I could be wrong about that.

You can also set it to session rather than global, but I think that would
have to be done with Hibernate somehow, or as part of the JDBC connection.
I'm not aware of how to do this, or if it's even possible. 

Brian Jones
Programmer/Analyst
Information Technology Services
Support Services Building, Room 4326
Western University
(519) 661-2111 x86969
bjones86 at uwo.ca

-----Original Message-----
From: Charles Hedrick [mailto:hedrick at rutgers.edu] 
Sent: Friday, February 28, 2014 10:17 AM
To: Brian Jones
Cc: Sam Ottenhoff; sakai-dev
Subject: Re: [Building Sakai] truncating to fit field

Actually, I'd like to track down why it's base to set NLS_LENGTH_SEMANITICS.
My concern with changing all the declarations is that the next time someone
creates a new table they may forget, and we'll end up with some columns on
way and some another. That's why the Oracle document I quoted recommended
using NLS_LENGth_SEMANTICS. It may well be that different Oracle documents
make contradictory recommendations here. I've also seen a document that
recommended not setting it globally, but setting it for specific sessions.
That seems weird since you different sessions accessing the same data with
different length semantics would seem to be a recipe for disaster.

On Feb 28, 2014, at 10:14 AM, Charles Hedrick <hedrick at rutgers.edu> wrote:

> It seems to me that the best approach would be to find all the .sql files
and add "char" to all the definitions. That sounds like something to do for
Sakai 11.
> 
> On Feb 28, 2014, at 10:10 AM, Brian Jones <bjones86 at uwo.ca> wrote:
> 
>> We run with Oracle DB's at Western and we've had problems with this 
>> in the past. Many, many columns are created as VARCHAR2 (XXX), where 
>> XXX is a number of BYTEs rather than CHARs.
>> 
>> From the documentation we've read, and according to our DBA, it is 
>> not recommended to set NLS_LENGTH_SEMANTICS=char.
>> 
>> I think it would be better if the auto.ddl scripts created the 
>> columns explicitly as VARCHAR2 (XXX CHAR).
>> 
>> Brian Jones
>> Programmer/Analyst
>> Information Technology Services
>> Support Services Building, Room 4326
>> Western University
>> (519) 661-2111 x86969
>> bjones86 at uwo.ca
>> 
>> From: sakai-dev-bounces at collab.sakaiproject.org
>> [mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Sam 
>> Ottenhoff
>> Sent: Friday, February 28, 2014 9:43 AM
>> To: Charles Hedrick
>> Cc: sakai-dev
>> Subject: Re: [Building Sakai] truncating to fit field
>> 
>> We already have code in Sakai to check whether the database 
>> connection can read/write proper UTF-8.  I would be fine with adding 
>> another check for Oracle semantics, but it doesn't make much sense to 
>> me to rewrite all insert string truncation code to deal with an 
>> Oracle db that isn't multi-byte preferred.
>> 
>> On Fri, Feb 28, 2014 at 8:21 AM, Charles Hedrick 
>> <hedrick at rutgers.edu>
>> wrote:
>>> 
>>> The Oracle web page recommends not using "char" in the declaration, 
>>> but
>> instead setting NLS_LENGTH_SEMANTICS to char. However unless I have 
>> some assurance that sakai sitss actually set NLS_LENGTH_SEMANTICS, I 
>> need to assume the worst.
>>> 
>>> On Feb 27, 2014, at 5:12 PM, Sam Ottenhoff <ottenhoff at longsight.com>
>> wrote:
>>> 
>>>> Adding code to work around this seems wrong.  Isn't the issue that 
>>>> your
>> database was setup with a non-multibyte character set which means 
>> that the length calculations Oracle is making are based on one byte = one
character?
>> Our confluence page
>> (https://confluence.sakaiproject.org/display/I18N/Common+UTF-8+Proble
>> ms)
>> recommends "AL32UTF8".
>>>> 
>>>> Can any of our Oracle-using institutions chime in here?  If the 
>>>> database
>> is setup initially with a multi-byte character set, are the tables 
>> created correctly where the length of SAKAI_SITE_GROUP/TITLE is 99 
>> multi-byte characters and not 99 bytes?
>>>> 
>>>> --Sam
>>>> 
>>>> 
>>>> 
>>>> On Thu, Feb 27, 2014 at 4:49 PM, Charles Hedrick 
>>>> <hedrick at rutgers.edu>
>> wrote:
>>>> my new code uses this: https://gist.github.com/lpar/1031951, except 
>>>> for
>> mysql, where a substring will do (because it interprets limits as 
>> characters, not bytes).
>>>> 
>>>> Yuck.
>>>> 
>>>> This is LSNBLDR-352.
>>>> 
>>>> On Feb 27, 2014, at 3:54 PM, Steve Swinsburg 
>>>> <steve.swinsburg at gmail.com>
>> wrote:
>>>> 
>>>>> The field is declared wrong, if it's going to store utf8 but use
>> substrb or substrc to trim the string in bytes or utf8 chars.
>>>>> 
>>>>> Cheers
>>>>> Steve
>>>>> 
>>>>> sent from my mobile device
>>>>> 
>>>>> On 28/02/2014 3:29 AM, "Charles Hedrick" <hedrick at rutgers.edu> wrote:
>>>>> I am generating a group name. The maximum length is 99 characters. 
>>>>> In
>> mysql it appears that truncating to 99 characters is fine, because 
>> mysql interprets field lengths in characters.
>>>>> 
>>>>> However in Oracle, unless the declaration uses "char", it is 
>>>>> understood
>> to be bytes. That means that UTF-8 characters can take more than one
space.
>> Is there some standard method in Sakai to truncate a string to a 
>> length that will fit into a given database field?
>>>>> 
>>>>> Unfortunately, the SAKAI_SITE_GROUP is defined in oracle using 
>>>>> field
>> names without "char" after them.
>>>>> 
>>>>> 
>>>>> _______________________________________________
>>>>> sakai-dev mailing list
>>>>> sakai-dev at collab.sakaiproject.org
>>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>>> 
>>>>> TO UNSUBSCRIBE: send email to
>> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of 
>> "unsubscribe"
>>>> 
>>>> 
>>>> _______________________________________________
>>>> sakai-dev mailing list
>>>> sakai-dev at collab.sakaiproject.org
>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>> 
>>>> TO UNSUBSCRIBE: send email to
>> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of 
>> "unsubscribe"
>>>> 
>>> 
>> 
> 