Hi everyone, It seems my life is complicated this week. Again I need to draw on your experiences and I'm looking for your opinions and cautionary statements.
Background: We use an outsourcer company to do the bulk of our database work. When we built new servers last year and they build the new oracle 10g databases we didn't notice that they were not Unicode. Apparently the previous ones were, but in actuality we didn't really have a need for it. About 5 years ago we opened a China office requiring all staff to be English proficient so everything was still ok. But now we're finding that customer information which arrives in simplified Chinese, which they translate into English, which then needs to be re-translated back to Chinese to meet government requirements for billing is not as straight forward as it may sound. So they were trying to type more Chinese in and of course since the new server databases were not Unicode it didn't work like it did before. Then to add to the pain, no one said anything. So here we are a year later and now it's a big deal. So, we are endeavoring to change to Unicode. Our approach is to create a new server space, create a new Unicode database, and get the data in there converted, and then install the application again. No upgrades of db or application involved, only the Unicode aspect of it. Well, that sounds so much easier than it's turning out to be. Our dba has run CSSCAN various times and finally came up with a list of forms/fields that have potential problems with the conversion. The current field length is being totally used so in the conversion to Unicode it needs more field length. The dba is suggesting that he just expand the database field lengths with no need to touch the form objects. This sets off all sort of red flags for me. I never let anyone go directly to the database for data updates. Other applications we use can read data but if they need to write there they use the Remedy API. There can be no manipulation of the tables in the database, it must all be done via developer tool. What else is troubling is that in the list of tables/fields that have a problem some tables/fields that should be listed because that's where the data originated and was pushed down from are not listed. Many of the records in question are older and if some data gets 'cut off' or what I used to think truncated meant we can live with that. We're talking several thousand records in 15-25 tables. The dba is proposing the following: I will export the table metadata and data of the tables, listed in the spreadsheet with NLS_LANG set to AMERICAN_AMERICA.WE8ISO8895P1. I will then truncate the tables and drop the tables. The CSALTER program will then be ran against the database. I will then import the table metadata and data, using NLS_LANG set to AMERICAN_AMERICAN.AL32UTF8 This will cause the internal expansion of the column from bytes to chars and some of the tables will fail to import because some of the characters may take up to 3 bytes instead of 1. You said there would be a problem with the forms and I know what you are saying. But if just the lengths of some of the columns change, will that invalidate the forms? So if a column is defined VARCHAR2(50) in the database and the form and that column is enlarged to VARCHAR2(100) just in the database, will that really affect the form? The data entered will be 50 Bytes long because of the form but that data will be stored in a 100 byte column in the database. Is that a problem? So my questions are: Do you have any experience with this type of situation and how did you deal with it? What really were the pitfalls? I'm not even sure what other questions to ask. I only hope the above gives enough of the story without leaving out important details. Thanks for your help, Susan Susan Palmer ShopperTrak 200 W Monroe 11th Floor Chicago, IL 60606 312-529-5325 [email protected] ARS v7.5 Oracle 10g Sun Solaris _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
