Hello,
I recently purchased the DEWizard to pipe data from sqlserver to Oracle. Although the application suits my needs, I have run into 2 issues which would both be fixed with the same alteration.
My destination is in oracle version 9.2.06
**************************************************************************************Issue 1 *********************************************
One of my destination columns is called GROUP. Unfortuately, it results in an ora-01747 error.
The fix is to wrap the column name with “double quotes”.
I was able to manually change the confiq file and run the translation via command line with success but when I use the interface, the configuration reverts back to an unmapped column.
Here is an example of the change:
Field="GROUP"
Mapping=Mapped To
MappedTo=[GROUP]
Calculated=
Format=
Default=
IfNull=Ignore
IfError=Set To Null
Key=False
The following is the rational for the change:
Error:
ORA-01747: invalid user.table.column, table.column, or columns specification
Cause:
You tried to reference a column name, but the column name used is a reserved word in Oracle.
Action:
The options to resolve this Oracle error are:
1. Try redefining your table so that none of your column names are reserved words.
2. Try enclosing the reserved word in double quotes.
For example, if you had a supplier table with a column named number, and you tried to update this field as follows:
UPDATE suppliers
SET number = 10000;
You would receive the following error message:
www.techonthenet.com/oracle/errors/images/ora01747_001.png
You could correct this error by enclosing the column name in double quotes as follows:
UPDATE suppliers
SET "number" = 10000;
-***************************************************************Issue 2************************************************
The second issue is with columns that have a space within the column name
Oracle will accept a column named : "EFFECTIVE DATE"
Again I was able to manually alter the configuration file and run via command file, but when using the interface the configuration reverts back to an unmapped column.
Here is an example of the manually altered config file:
Field="EFFECTIVE DATE"
Mapping=Mapped To
MappedTo=[EFFECTIVE DATE]
Calculated=TO_DATE(TO_CHAR(EffectiveDate,'YYYY/MM/DD'))
Format=DDMMYY
Default=
IfNull=Ignore
IfError=Set To Null
Key=False
M. Ross
