DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 172
Location: Paris
Points: 1320

Post Posted: Thu Jun 21, 2018 11:44 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Server
OS: Windows
Hi All
We are migrating from the 8.7 to the 11.5 version.
We have the Error ORA-01461 in a very simple job who read a file to upsert a table.
The key of the table is a varchar(9) but it contains only numerical characters (ex 007660008).
I presume that the connector see the value like a long even so it's define in varchar 9.
How can i handle this ?
Thank you.

_________________
Hope This Helps
Regards
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42621
Location: Denver, CO
Points: 219439

Post Posted: Thu Jun 21, 2018 12:14 pm Reply with quote    Back to top    

ORA-01461: can bind a LONG value only for insert into a LONG column

It's typically about a CLOB field unless your Oracle is ancient and still actually has LONG as a datatype. Can't have anything to do with your VC2(9) field so seems to me we'd need to see the metadata for all of the columns to offer any further help.

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1245

Points: 8171

Post Posted: Thu Jun 21, 2018 3:17 pm Reply with quote    Back to top    

I've got a few migrations under my belt now. The 1# issue encountered is Metadata related. Bad columns in the old job that were "warnings" are now aborts.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42621
Location: Denver, CO
Points: 219439

Post Posted: Thu Jun 21, 2018 3:30 pm Reply with quote    Back to top    

I've got a few vicarious migrations under my belt from hanging out here. Wink

And yes, you should not be surprised to find several things that used to "work fine" but were actually only working because of loopholes or bugs in the old version you were taking advantage of (so to speak), issues that have since been plugged or tightened up as Paul mentioned. Especially with such a long upward leap as you are taking.

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54254
Location: Sydney, Australia
Points: 294257

Post Posted: Thu Jun 21, 2018 6:23 pm Reply with quote    Back to top    

Sometime in my near future I'm doing an upgrade from 8.0.1 on AIX to 11.7.0.1 on (RHEL on) cloud. Not really looking forward to that one. And they want Stewardship Center and Enterprise Search.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 172
Location: Paris
Points: 1320

Post Posted: Fri Jun 22, 2018 1:47 am Reply with quote    Back to top    

Hi
Here is the metaData
File
C1 Varchar(255)
C2 Varchar(255)
Connector
C1 Varchar(9)
V2 Varchar(32)
The error is on the column C1 who contains only digits.

Ps : we all agree that a migration is painfull !

Thank you

_________________
Hope This Helps
Regards
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42621
Location: Denver, CO
Points: 219439

Post Posted: Fri Jun 22, 2018 6:39 am Reply with quote    Back to top    

Well, then that means it's time to involve support unless you are short on fixpacks, in which case I'd suggest you get current for your release. There's absolutely no reason that putting 'only digits' into a string would case any kind of problem.

Only other thing I'd throw out there: have you tried to explicitly substring that problem field down to size before sending it to the connector? Really shouldn't be necessary but IMHO would be a good test before you report the issue.

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16311
Location: Germany
Points: 92496

Post Posted: Fri Jun 22, 2018 8:33 am Reply with quote    Back to top    

We are doing a 9.1.2 to 11.5 Migration on AIX to LINUX and there are a number of "gotchas", but not this ORA-error is not among them.

_________________

Image
Rate this response:  
Not yet rated
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 172
Location: Paris
Points: 1320

Post Posted: Fri Jun 22, 2018 8:39 am Reply with quote    Back to top    

Hi
The problem is solve.
The problem was the French accent is the C2 Column.
We change the NLS from NONE to MS1252-CS and everything is okay now.
Thank you

_________________
Hope This Helps
Regards
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42621
Location: Denver, CO
Points: 219439

Post Posted: Fri Jun 22, 2018 9:00 am Reply with quote    Back to top    

Not any kind of an expected error from that situation but glad you worked it out.

_________________
-craig

And I'm hovering like a fly, waiting for the windshield on the freeway...
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours