The tables ttadv304, ttadv335, ttadv364 have a field " layo" which store binary data (Baan IV c4).

How these fields got corrupted:
Stupid me dumped out company 000 from the Oracle database having NLS_LANG=american_america.we8dec
Oracle 9.2.5 was installed and then I created the 000 company at Oracle 9.2.5 having this
NLS_LANG=american_america.we8iso8859p1

Everything worked fine for 6 months, then some user wanted me to change some reports. This is not possible in our system because the layo field is corrupted.

Before I noticed this problem I have done a lot of Patching so I'm afraid to restore the tables, because I see they have been influenced by the patches.

Regarding Oracle support it is a design mistake to store Binary data in a varchar / char field.

Regarding SSA this is a known issue, so it is only me that is stupid. :eek:

So is this a known issue ? that you cannot migrate from one
NLS_LANG to another the way I did ?

And,,does anybody have any advice how to solve it?

Yes, it's a widly known issue (i think the Baan solution that talks about it dates back to 1998 or something...)

There are different levels of this problem. It will present the same if there is a mismatch somewhere (for example NLS_LANG in your App server's Windows registry says US7ASCII where your DB server is we8iso8859p1.
That's an easy fix, just make them match. But if everything's matching the only way to fix it is to correct binary values by hand... so basically, there is no fix.

However, you might get lucky if you just set your NLS_LANG to your old character set and try to access the data. you might be able to get at your old stuff that way.. (don't just pre and post the table at this point though, because new records are in the new NLS format!)

Dave

Right, widly known issue. I'm not that wide :)
One relevant hit when searching for layo in SSA knowledge base:
83 Error "Index n out of dims[n] n " on form, report, menu layout tables on ORACLE
1955 ttB40b advformedit 11 Mar 1998


I think I have to correct it by hand when I need to customize the report. The only problem is that I don't know what to type in. :confused:

To be honest, I think you are fu**ed. Sorry to say. Correcting by hand sounds to me pretty impossible.

Your only real chance involves a lot of work and some spare hardware:

Restore an old backup on a test system
Apply the patches again
Make sure SW level on test system and life system is the same
Dump company 000 as big dump in Baan without separators
Read this dump into your production company

A lot of work, but without this you will have endless trouble until you switch off Baan and migrate to another system. Not only report layouts, but also menus and forms are affected, so basically everything, which involves customizations.


The only other "theoretical" way I see is the following: Baan is obviously running fine in your installation. Even though the layout tables are corrupt. The reason for this is, that the layouts used are actually in the runtime files on disk. So if somebody reverse engineers these files and writes a small program to write them back into the tables, than you should be fine. This might actually be worth the effort, compared to the first option I described.
But unfortunately it might also not be possible, because the runtime files should contain translated labels, while the layout will only contain label placeholders. So this might be a one way mapping only.

Regards

Markus

Very strange. :confused:

The 000 dump was made in Baan IV by using session "Create Sequential Dump of Table". The environment was using NLS_LANG=american_america.WE8DEC.

Then I imported the dump file to the new environment by using the script file below:

#!/bin/ksh
cd /u04/app/bse
export BSE=/u04/app/bse
export TARGET_COMPANY=000
export SOURCE_COMPANY=000
export PCOMB=B40D_dv1
export SOURCEDIR=/u04/bdump
export LOGFILE=${SOURCEDIR}/imp_${TARGET_COMPANY}.log
export ORACLE_BASE=/u04/app/oracle
export ORACLE_HOME=/u04/app/oracle/product/9.2.0
export ORACLE_SID=B4
export PATH=$BSE/bin:/usr/bin:/u04/app/oracle/product/9.2.0/bin

#------ DO NOT EDIT BELOW THIS LINE -----------------------
export BSE_TMP=${BSE}/tmp
export ORA_INIT=0111000
export ORA_MAX_ARRAY_INSERT=1000
export ORA_MAX_ARRAY_FETCH=1000
export NLS_LANG=american_america.WE8ISO8859P1

clear
echo BSE=$BSE
echo TARGET_COMPANY=$TARGET_COMPANY
echo SOURCE_COMPANY=$SOURCE_COMPANY
echo PCOMB=$PCOMB
echo SOURCEDIR=$SOURCEDIR
echo LOGFILE=$LOGFILE
echo ORACLE_SID=$ORACLE_SID
echo ORACLE_HOME=$ORACLE_HOME
echo NLS_LANG=$NLS_LANG
echo ""

case $1 in
DOIT) echo "Importing tables from company ${SOURCE_COMPANY} to company ${TARGE
T_COMPANY} while deleting existing tables..."
echo ""
date +"%d.%m.%y %H:%M:%S Import tables to company ${TARGET_
COMPANY} and delete existing tables..." > ${LOGFILE}
echo "" >> ${LOGFILE}
nohup /u04/app/bse/bin/bdbpost6.1 -doracle8 -f -k -c${TARGET_COMPANY} -e
${SOURCEDIR}/err_000.log -m -n -p${PCOMB} < ${SOURCEDIR}/dump.${SOURCE_COMPANY}
>> ${LOGFILE}
date +"%d.%m.%y %H:%M:%S COMPANY 000 IMPORTED" >> ${LOGFIL
E}
echo "-----------------------------------------------------" >> ${LOGFIL
E}
echo "IMPORT COMPLETED!";;
*) clear
echo "You didn't specify the mandatory parameter to run this script"
echo ""
echo "This script will OVERWRITE all tables in Company ${TARGET_COMPANY}
!"
echo "If you are sure this is what you want, run this script with the pa
rameter "DOIT", like this:"
echo ""
echo "imp000.sh DOIT"
echo "This will execute this script with the following parameters:"
echo ""
echo BSE=$BSE
echo SOURCE_COMPANY=$SOURCE_COMPANY
echo TARGET_COMPANY=$TARGET_COMPANY
echo PCOMB=$PCOMB
echo SOURCEDIR=$SOURCEDIR
echo LOGFILE=$LOGFILE
echo ORACLE_SID=$ORACLE_SID
echo ORACLE_HOME=$ORACLE_HOME
echo NLS_LANG=$NLS_LANG
echo "";;
esac
#

It is strange that it does not work to create a dump by using NLS_LANG=american_america.WE8DEC
and then import it to a new environment with another NLS_LANG=american_america.WE8ISO8859P1

I have changed $BSE/lib/tabledef6.1
to contain:

*:*:oracle8(NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1,ORACLE_SID=B4):N

and I have restored the tables ttadv304, ttadv335 and ttadv364 by using session "Create Table from Sequential Dump" and used the Dump file that was from the old Environment with WE8DEC. But the problem with layo out of dims is still current.
Logical because it was the same import that was done concerning NLS_LANG in the first place, using the script above with WE8ISO8859P1.

So how can I fix the problem ?
Switch the language for Oracle and in tabledef6.1 back to WE8DEC and live with the fact that with Baan on Oracle. it is not possible to change Character set.

First off - you're sure that your NLS_LANG was set to american_america.WE8DEC in the old database? I've never seen that particular characterset before - it seems strange. Also - by default if you didn't specify it in your tabledef or db_resource it would have defaulted to US7ASCII. (no matter what your DB was set to).

Second, are you moving from 1 box to the next? if so, try gzipping the tables and transfering them in binary.

Just some thoughts.

Dave

I am sorry I did not log in for a while. I guess you have already solved the problem. Besides the problem with NLS_LANG, NLS_SORT and other oracle stuff, are you sure that the problem is not in BAAN?

Usually this problem comes up when you upgrade to a porting set that takes by default "ora_column_format=8" instead of 7. I think it is the same problem for you. I guess you also changed the porting set for the upgrade. So all you need to do is to add:

ora_column_format:7 to the file db_resource, for example.

In this way in oracle you will have two columns for the same baan column, due to the "255 max length" limitation for a column in Oracle7.
Since ttadv335.layo has 260 characters, it will be split in t$layo$1 and t$layo$2.


I hope this info may help someone, someday, altthough it may not be the case for your exact problem, I know....


P.S. I forgot to say... In your particular case, you should take a look in Oracle to see if you have two columns or if you have one column with bigger length.
2 columns means you should set (in Baan) ora_column_format=7
1 larger column means you should set ora_column_format=8

The setting can be either as a resource variable or as an evironment variable, as you wish. I recommend db_resource file.