Changing Databases (SQL to Oracle)

As many of you may remember, my last "fools errand" was a move from E1 back to World 8.1...this one is not quite as bad; my new company is migrating from SQL to Oracle 11g. One of the big "gotchas" we have encountered in testing is the case sensitivity (insensitivity) issue. In SQL, we had case insensitivity, which led to, um, less than sterling standards in data input (esp in the Address Book). As a result we have a mix of all caps (ROBERT) and standard (Robert) in our data entry. This was not a problem in SQL; however, if we enforce case sensitivity in Oracle, users may riot. This leaves us with 4 options:
1) User retraining...um, yeah
2) Clean up of the AB (and other places)
3) Turning off case sensitivity on the Oracle DB
4) Creating a login script that turns off case sensitivity when a user logs in to JDE.

My first question is: has anyone migrated frm SQL to Oracle? If so, how did you address this issue?
Also, did you run into any other issues in the DB conversion?

Robert,

Unfortunately, your ##'s 3 & 4 are not viable: the system will become unstable and basically unusable.

What I see most sites do is update all such tables converting all such strings to UPPERCASE for consistency. Plus, of course, the user training.

I wouldn't even start the natural "Why on earth..." thread ;-)

This might be a dumb question, but how do you end up with these kinds of dilemma's?

I'd stop searching for technical solutions if I were you and start working on communicating a more successful plan with the people who direct that these half-baked ideas come to fruition...

Malcolm

I "end up" with these because the decisions made predate me, or were made beyond my paygrade.

At this point, this is not a time for recriminations of previous regimes or bad ideas, rather a time to foment and implement USEFUL solutions. The crux of the situation is that there have been a lack of standards in creating AB records (primarily). Editorializing aside, I came here to see what the most workable option (in a bad situation) is. Thank you.

Robert

I feel your pain. We are in the same boat. XE is on case insenstive SQL. 9.0 is on case sensitive Oracle. Our first country to go live on 9.0 just sucked it up and had to learn new rules. The US and Canada who haven't upgraded yet are not happy about the pending case sensitivity. Time will tell what we end up doing about it.

We did have a conversation with Oracle about turning off case sensitivity. What that basically requires is that we create and have to maintain two sets of indexes on tables. The default index (case sensitive) and a secondary case insensitive one. Lots more for the DBAs to maintain. And risky. Not a great option.

Alex's experieince is the one that is the most stable, but it requires some politicing to get it implimented.

- Gregg

The only issues I can find on the knowledge garden about 11g and case sensitive are with case sensitive passwords. Is there anything on the knowledge garden talking about the possible issues with upgrading to 11g with JDE and coming from a case insensitive database?

Robert,

I heard your question on the EUCC call today, and it got me thinking. Would it be possible for you to set the data dictionary settings to be Uppercase for the field(s) you're concerned about? You'd want to see everywhere any data dictionary field is used (i.e. what tables it's in), and you'd probably have to run a one-time conversion for existing data. But maybe could help you out.

Yup.

It is under "Oracle_DB_Case_Insensitivity_JDEdwards_EnterpriseOne" in the Knowledge jungle. An interesting read: Oracle warns on the first page that turning off case sensitivity is not supported, then spends the next 44 pages telling you how to build the bomb.

Is probably the best option going...

[ QUOTE ]
Yup.

It is under "Oracle_DB_Case_Insensitivity_JDEdwards_EnterpriseOne" in the Knowledge jungle. An interesting read: Oracle warns on the first page that turning off case sensitivity is not supported, then spends the next 44 pages telling you how to build the bomb.

[/ QUOTE ]

I saw that document too - "we won't support you if you do this. your systems will probably become unstable. So here is how to muck up your system in such a way that we won't support it or tell you how to fix it." Yea, I'm all warm and fuzzy about that.

A better solution is this - do a conversion to select fields to make them all upper. Change the input lines on the apps to only allow upper data entry or data modification. train your users and get on with life.

by the way, in case you were curious - the reason why Oracle databases are case sensitive is that they use a binary index. When indexing a field with numeric information, they index based on the ASCII value of the letter. A lower case "a" is ASCII value 97. Upper case "A" is 65. That is why they sort differently on an Oracle databse or a Linux OS.

- Gregg

It seems that the data conversion (to all caps) is the runaway leader (fortify the walls and drag up the boiling oil to handle the dissidents!). The next question is, what is the best practice for the fields to "CAP UP". If we think about all of the QBE fields that could be subject to search, that could be quite a few... Address Book names, etc, explanations, other? Which ones did you tweak Gregg?

[ QUOTE ]
It seems that the data conversion (to all caps) is the runaway leader (fortify the walls and drag up the boiling oil to handle the dissidents!). The next question is, what is the best practice for the fields to "CAP UP". If we think about all of the QBE fields that could be subject to search, that could be quite a few... Address Book names, etc, explanations, other? Which ones did you tweak Gregg?

[/ QUOTE ]

We haven't changed anything, yet. I imagine the US and Canada will demand that change. Our Mexicans are more laid back. They complained at first, and then went on with life.

- Gregg

I could see an argument for changing all text to all caps, but I am curious if anyone has gone with anything other than a full text change.

[ QUOTE ]
I could see an argument for changing all text to all caps, but I am curious if anyone has gone with anything other than a full text change.

[/ QUOTE ]

Hold up cowboy. That's going a bit far. That means your customer names and addresses on your invoices are all caps. THEN YOU WOULD BE NONVERBALLY SHOUTING FOR YOUR MONEY. CUSTOMERS MIGHT GET ANNOYED.

How about changing the key search field to all caps, and then using a secondary set of fields to put in the address information in mixed caps? That is a kinder, gentler solution.

Exactly the solution I am angling for. That is why I am wondering if anyone has done so, so that I can target the specific fields in the tables that would need to be altered. I have an idea of the ones that I would CAP UP, but I want to make sure that I only have to do this once (don't miss any other worthy candidates)...

For our business the most critical searches are on Item Descriptions.
So we modified DSC1, DSC2, and SRTX in multiple apps to be UC only.

We didn't change address book entries for the very reasons Greg brought up. You don't want to shout on outward facing documents. Item Descriptions being all UC / Consistent can actually be desirable though.

I still don't understand why Oracle can't address this at the foundation level - turn a switch and all SQL WHERE clauses on character fields use a case conversion function.

Oh well

So I've been down this road several times before and done several migrations from DB2 UDB, DB2/400 and SQL Server to Oracle.

This topic comes up everything and after lots of pondering and wasted time 100% of people acept the fact that Oracle is different from the other databases.

I really don't suggest the case insensitivity............but after all you are the guy who went from E1 to World so "crazy" is not unfamaliar to you.

I think this will blow over and as long as you show your managers the white papepr with the "Oracle does not support or recommend this" they will make the wise choice not to go case insensitive.


Colin