I'm having a problem inserting into a Oracle 9.2 database from DataStage where the NLS_CHARSET of the database server is AL32UTF8. See below for the error message:
JCTest1..Transformer_1.DSLink4: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO SIEBEL.JC_TEST1(FIELD1, FIELD2, FIELD3) VALUES (?,?,?)
SQLSTATE=HY000, DBMS.CODE=1461
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle driver][Oracle]ORA-01461: can bind a LONG value only for insert into a LONG column
FIELD1 = "XYZ"
FIELD2 = "This is another test"
FIELD3 = "789"
Note that there are no LONG columns in the table. The same job works fine when inserting into an ORacle 9.2 database with NLS_CHARSET = UTF8.
Has anyone else experienced this problem? I'm assuming at the moment that it is an Oracle problem rather than DataStage. Probably the Oracle client on the DataStage server which is version 8.1.7, but might also be the NLS_CHARSET setting of the client.
Regards,
Jeremy.
Problems inserting into an Oracle 9.2 database
Moderators: chulett, rschirm, roy
Re: Problems inserting into an Oracle 9.2 database
I had this once before but at that time the error was caused by triggers which were activated and trying to insert a long wrongly in another table, so although I also did not bind longs... I did get the same error.
Did you run your successful test on exactly the same database with exactly the same values.
Ogmios
P.S. I don't think you can use 2 Oracle clients at the same time in DataStage, if you would be thinking of installing a 9.2 client besides the 8.1.7 client.
Did you run your successful test on exactly the same database with exactly the same values.
Ogmios
P.S. I don't think you can use 2 Oracle clients at the same time in DataStage, if you would be thinking of installing a 9.2 client besides the 8.1.7 client.
We've managed to fix this problem by switching DataStage to use an Oracle 9i client (actuallly 9.0.1). I would suggest the problem was to do with the DS ODBC driver interaction with the 8 client.
(This is on a dev box. Remains to be seen whether it is backwards compatible for all projects using the OCI8 plugin in prod!)
The problem I now have is that the metadata for all CHAR and VARCHAR2 fields is out of sync. Was previously 3x length in UTF8 charset, now expects 4x length in AL32UTF8.
(This is on a dev box. Remains to be seen whether it is backwards compatible for all projects using the OCI8 plugin in prod!)
The problem I now have is that the metadata for all CHAR and VARCHAR2 fields is out of sync. Was previously 3x length in UTF8 charset, now expects 4x length in AL32UTF8.
Instead of using varchar(n) in your tables. use varchar(n char). The default for Oracle is to count in bytes, if you put "char" behind the number Oracle will count in unicode characters. So varchar( 8 ) becomes varchar(8 char)
You can find more information in the Oracle globalization guide.
Ogmios
P.S. Didn't you write you were part of an Oracle solution center somewhere
You can find more information in the Oracle globalization guide.
Ogmios
P.S. Didn't you write you were part of an Oracle solution center somewhere
In theory there's no difference between theory and practice. In practice there is.
I don't think so... DataStage should see the varchar(100 char) as 100 long, if the worst case you could still manually change it to 100 manually after importing the table definition. It does work because I have several of these kind of jobs.jcoley wrote:Unfortunately the target system is a Siebel application, so I have no say as to the definition of the tables.
Wouldn't the same problem still exist, though? A column defined as Varchar(100 char) on a UTF8 database would import into DS as Varchar(300) and on a AL32UTF8 database as Varchar(400)?
In theory there's no difference between theory and practice. In practice there is.