BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Streamline and extend SAP BusinessObjects software 
Streamline and extend SAP BusinessObjects software (Opens a new window)  

General Notice: No events within the next 45 days.

Decimal Separator Problem (ORA-01747)


 
Search this topic... | Search DI: Designer and Job Design... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Author Message
KaeptnBlaubaer
Senior Member
Senior Member



Joined: 07 Jul 2009

Posts: 31


flag
PostPosted: Thu May 31, 2012 4:15 am 
Post subject: Decimal Separator Problem (ORA-01747)

Hello,

I hope, anyone has an idea where my config problem is. I think, I tried nearly everything without success.

Following problem: I want to load data from DB2/400 Database to an Oracle 10g (Express Edition) Database. A direct load is no problem, but with TableComparision I get the ORA-01747 error when there is an update record.

The error log shows following Update-Statement:

Code:
Update "SCHEMA"."TABLE_A" SET "ID" = 1,0000000, "NR2" = 19303,0000000, "NAME" = 'New Name'
WHERE "ID" = 1,0000000 and "NR2" = 19303,0000000 and "NAME" = 'Old Name';


The problem is the comma (,) in the numeric value. I've found no way to replace them by a point (.) as separator.

If I execute the statement on the Database as shown above, I get the same error. If I correct the decimal separator as point, the sql works perfect.

The Language in the Datastore-config is eng (Englisch), Code page = <default>, and Server code page = <default>.


Designer Version (sorry not the latest): 12.2.1.2

Thanks for tips!!!
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 3614
Location: Denver Area


flag
PostPosted: Thu May 31, 2012 6:41 pm 
Post subject: Re: Decimal Separator Problem (ORA-01747)

I think you have a mismatch of the regional settings between the client (where the job server resides) and the database server. If you are on Windows this may be found in Programs->Settings->Control Panel->Regional and Language options. I could be wrong on how to change it as I don't deal with settings other than U.S.

You may also want to check the NLS settings in the registry. For example:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1\NLS_Lang.

_________________
Jim Egan
Mantis Technology Group-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaking at Business Objects User Conference 2013
-Session 0214:My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Session 0209:Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
KaeptnBlaubaer
Senior Member
Senior Member



Joined: 07 Jul 2009

Posts: 31


flag
PostPosted: Fri Jun 01, 2012 5:42 am 
Post subject: Re: Decimal Separator Problem (ORA-01747)

Thats a good hint.


All systems are on windows and the oracle-Regkey Entry is "GERMAN_GERMANY.WE8MSWIN1252". The same entry for Target-System and Job-Server.

So I tried Datastore-config: deu (German) and also <default> without success. The error Message is still the same.


BUT: if I set the Registry entry NLS_LANG to "AMERICAN_AMERICA.WE8ISO8859P1", although all involved systems are german, it works fine icon_biggrin.gif

The Datastore language config doesn't affect anything in this context, but with an u.s. NLS_LANG the Update works.

Hope there are no side-effects to other things.


THANKS!
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 3614
Location: Denver Area


flag
PostPosted: Fri Jun 01, 2012 11:53 am 
Post subject: Re: Decimal Separator Problem (ORA-01747)

Given that changing the NLS_LANG on the client worked I would have to assume that the database server itself is set up that way also.

It is possible to change your session settings at runtime to make things match.

As I said before I don't deal with these settings much, but something sounds pretty messed up. The client and database server settings need to agree or you get some very odd results/errors.

_________________
Jim Egan
Mantis Technology Group-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaking at Business Objects User Conference 2013
-Session 0214:My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Session 0209:Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Designer and Job Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0593 seconds using 17 queries. (SQL 0.0099 Parse 0.0411 Other 0.0083)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo