Home » RDBMS Server » Networking and Gateways » Data problems using ODBC Gateway (Database: Oracle 11gR2 (11.2.0.4) , OS:Oracle Linux 6.6 x86_64 )
| Data problems using ODBC Gateway [message #651899] |
Fri, 27 May 2016 02:26  |
 |
emkk
Messages: 9
Registered: May 2016
Location: Hungary
|
Junior Member |
|
|
Our ORACLE 11R2 database server connects to a MS-SQL database server via ODBC Gateway 11.2.0.4.
We use Microsoft ODBC Driver ver11.0.2270.0.
We've got two problems using this connection, as follows:
1. If the MS-SQL table's field is defined as NUMBER(6,1), and we read this data in an ORACLE SQL environment
via SELECT statement, the returned value is truncated to integer, that means, number 14.7 returns as 14.
2. If the MS-SQL table's field is defined as CHAR(6), and we read this data in an ORACLE SQL environment
via SELECT statement, the returned value is padded with 6 blanks, that means, "ABCDEF" returns as "ABCDEF<6 space>".
Please help us, what to do for solving these problems.
|
|
|
|
|
|
| Re: Data problems using ODBC Gateway [message #651901 is a reply to message #651900] |
Fri, 27 May 2016 02:57  |
|
emkk
Messages: 9
Registered: May 2016
Location: Hungary
|
Junior Member |
|
|
SQL*Plus: Release 8.0.6.0.0 - Production on P. Mßj 27 09:51:43 2016
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Adja meg a felhasznßlˇ nevet: jarmu/jarmu@volan
Kapcsolˇdßsi cÚl:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc "dbo"."MenMl16"@ohvcont2012;
NÚv Null? TÝpus
------------------------------- -------- -----
ML_ID NOT NULL NUMBER(10)
VALL NOT NULL NUMBER(3)
EV NOT NULL NUMBER(3)
HO NOT NULL NUMBER(3)
TSZAM NOT NULL CHAR(12)
NAP NOT NULL NUMBER(3)
TTI NOT NULL CHAR(2)
OKMANY NOT NULL NUMBER(10)
OKMANY1 NOT NULL NUMBER(10)
OKM NOT NULL CHAR(6)
OKM1 NOT NULL CHAR(6)
EGYSEG NOT NULL CHAR(8)
EGYS_BER NOT NULL CHAR(8)
EGYS_UZ NOT NULL CHAR(8)
DECKOD NUMBER(3)
DEC_UZ NUMBER(3)
VEZ NUMBER(3)
VEZ_UZ NUMBER(3)
ACS NUMBER(3)
MKK NUMBER(3)
PROF NUMBER(3)
PROF_UZ NUMBER(3)
FRSZ NOT NULL CHAR(12)
FRSZ_UJ NOT NULL CHAR(12)
TIPUS NOT NULL CHAR(8)
JELL NUMBER(3)
FORDA NOT NULL CHAR(12)
FORDA1 NOT NULL CHAR(12)
VERZ NOT NULL NUMBER(3)
NAPT NOT NULL NUMBER(3)
SZJEL NOT NULL NUMBER(5)
SZTAV NOT NULL NUMBER(5)
KALAUZN NOT NULL NUMBER(3)
KIFELEZ NOT NULL NUMBER(3)
UZA_KELL NOT NULL NUMBER(3)
BER_KELL NOT NULL NUMBER(3)
TOM_KELL NOT NULL NUMBER(3)
TOM1_KELL NOT NULL NUMBER(3)
FIZUN_JAR NOT NULL NUMBER(3)
MEG NUMBER(3)
FUV NUMBER(3)
KJTIP NOT NULL CHAR(2)
KJNAP NOT NULL NUMBER(3)
ATF_K NOT NULL NUMBER(7,1)
ATF_V NOT NULL NUMBER(7,1)
FUT_K NOT NULL NUMBER(7,1)
FUT_V NOT NULL NUMBER(7,1)
HOM NUMBER(5)
KIFUT NOT NULL NUMBER(5)
MELEG NOT NULL NUMBER(5)
LEGKOND NOT NULL NUMBER(5)
KM_KEZD NOT NULL NUMBER(7)
KM_VEG NOT NULL NUMBER(7)
OPJEL NOT NULL NUMBER(3)
JELTA NOT NULL NUMBER(5)
VEZET NOT NULL NUMBER(5)
KIALL NOT NULL NUMBER(5)
KIALLF NOT NULL NUMBER(5)
KIALL_ NOT NULL NUMBER(5)
EGYEB NOT NULL NUMBER(5)
EGYEBF NOT NULL NUMBER(5)
EGYEB_ NOT NULL NUMBER(5)
FELLEP NOT NULL NUMBER(5)
LELEP NOT NULL NUMBER(5)
FELLEAT NOT NULL NUMBER(5)
KARB NOT NULL NUMBER(5)
LEDOLG NOT NULL NUMBER(5)
FLNEL NOT NULL NUMBER(5)
SZOLG NOT NULL NUMBER(5)
OSZT NOT NULL NUMBER(5)
FIZTAV NOT NULL NUMBER(5)
FIZUN NOT NULL NUMBER(5)
BINEM NOT NULL NUMBER(5)
SZABAD NOT NULL NUMBER(5)
BETEG NOT NULL NUMBER(5)
TAVOL NOT NULL NUMBER(5)
[b]KM NOT NULL NUMBER(6,1)[/b]
KKM NOT NULL NUMBER(6,1)
SZKM NOT NULL NUMBER(6,1)
SULYAL NOT NULL NUMBER(6,1)
SULYNO NOT NULL NUMBER(18)
SULYKM NOT NULL NUMBER(6,1)
SULYHA NOT NULL NUMBER(6,1)
NUZAGT NOT NULL NUMBER(6,2)
NUZAGM NOT NULL NUMBER(6,2)
NUZAGH NOT NULL NUMBER(6,2)
TUZAG NOT NULL NUMBER(6,2)
TKENAG NOT NULL NUMBER(6,2)
TOCSE NOT NULL NUMBER(6,2)
TADALAG NOT NULL NUMBER(6,2)
TFUTOAG NOT NULL NUMBER(6,2)
POTLEK NOT NULL NUMBER(8,2)
JSZ NOT NULL NUMBER(3)
DUHI NOT NULL NUMBER(5)
DUHK NOT NULL NUMBER(5)
EJHI NOT NULL NUMBER(5)
EJHK NOT NULL NUMBER(5)
OKTAT NOT NULL NUMBER(3)
BERFEL NOT NULL NUMBER(3)
JAF NUMBER(3)
MSZJB NOT NULL NUMBER(5)
MSZJK NOT NULL NUMBER(5)
MSZJK_ NOT NULL NUMBER(5)
KJORA NOT NULL NUMBER(5)
VEZCS NOT NULL NUMBER(5)
UTASKI NOT NULL NUMBER(5)
ELSZAM NOT NULL NUMBER(5)
TANKOL NOT NULL NUMBER(5)
MSZUNF NOT NULL NUMBER(5)
OSZTPI NOT NULL NUMBER(5)
MSZUN NOT NULL NUMBER(5)
SZAB NOT NULL NUMBER(3)
BET NOT NULL NUMBER(3)
BSZ NOT NULL NUMBER(3)
FCS NOT NULL NUMBER(3)
TSZAM2 NOT NULL CHAR(12)
PARTNER NOT NULL CHAR(24)
SAPPM NOT NULL CHAR(4)
TOMB NOT NULL NUMBER(3)
ORAKI_JAR NOT NULL NUMBER(3)
VALTO_FORD NOT NULL NUMBER(3)
ELLEN NOT NULL NUMBER(3)
AFK_1 NOT NULL NUMBER(5)
AFV_1 NOT NULL NUMBER(5)
AFK_2 NOT NULL NUMBER(5)
AFV_2 NOT NULL NUMBER(5)
AFK_3 NOT NULL NUMBER(5)
AFV_3 NOT NULL NUMBER(5)
AFK_4 NOT NULL NUMBER(5)
AFV_4 NOT NULL NUMBER(5)
AFK_5 NOT NULL NUMBER(5)
AFV_5 NOT NULL NUMBER(5)
FORGEL NOT NULL NUMBER(5)
JOGSISZAM CHAR(40)
JOGSIKAT CHAR(40)
JOGSIERV DATE
NAPIDAT NOT NULL DATE
FLKOD NOT NULL CHAR(4)
ML_ID_OLD NOT NULL NUMBER(10)
INSC NOT NULL CHAR(4)
INSD NOT NULL DATE
UPDC NOT NULL CHAR(4)
UPDD NOT NULL DATE
TOROLVE NOT NULL NUMBER(3)
SQL>
SQL> select "FRSZ", km FROM "dbo"."MenMl16"@ohvcont2012 where "HO"=4
and "NAP"=4 and FRSZ='AAH208' order by 1,2 ;
FRSZ KM
------------ ---------
AAH208 201
SQL>
Here, the real value of KM is 201.8
Thanks
[Edit MC: add code tags]
[Updated on: Fri, 27 May 2016 03:21] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Data problems using ODBC Gateway [message #651914 is a reply to message #651904] |
Fri, 27 May 2016 06:44 |
|
emkk
Messages: 9
Registered: May 2016
Location: Hungary
|
Junior Member |
|
|
I did it, the result is:
SQL*Plus: Release 11.2.0.2.0 Production on P. Mßj. 27 13:38:07 2016
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: jarmu/jarmu@volan
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select dump(FRSZ), dump(KM) from "dbo"."MenMl16"@ohvcont2012 where "HO"
=4 and "NAP"=4 and FRSZ='AAH208';
DUMP(FRSZ)
--------------------------------------------------------------------------------
DUMP(KM)
--------------------------------------------------------------------------------
Typ=96 Len=6: 65,65,72,50,48,56
Typ=2 Len=3: 194,3,2
SQL>
|
|
|
|
|
|
| Re: Data problems using ODBC Gateway [message #651973 is a reply to message #651955] |
Mon, 30 May 2016 02:33 |
|
emkk
Messages: 9
Registered: May 2016
Location: Hungary
|
Junior Member |
|
|
Thank!
But, I attached a scrrenshot of an MSSQL manager from an original MSSQL environment, and you can see, that the spacification of FRSZ is CHAR(6), and char(12) is seen only from our Oracle sql environmewnt. But why?
The KM field is number (6,1) and the attachment consists a part, where decimal values can be seen.
And, in our Oracle SQL, the following Select statement shows the real problem:
SQL> select "FRSZ", km,km*10 FROM "dbo"."MenMl16"@ohvcont2012 where "HO"=4 and "NAP"=4 and "FRSZ"='AAH208';
FRSZ KM KM*10
------------ ---------- ----------
AAH208 201 2016
SQL>
I can't understand it!
|
|
|
|
|
|
| Re: Data problems using ODBC Gateway [message #651981 is a reply to message #651977] |
Mon, 30 May 2016 07:13 |
|
emkk
Messages: 9
Registered: May 2016
Location: Hungary
|
Junior Member |
|
|
SQL*Plus: Release 12.1.0.2.0 Production on Mon May 30 13:45:10 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: jarmu/jarmu@volan
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show numwidth numformat
numwidth 10
numformat ""
SQL> select value from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS';
VALUE
----------------------------------------------------------------
BYTE
SQL> select "FRSZ", dump("FRSZ") FROM "dbo"."MenMl16"@ohvcont2012 where ml_id=8928;
FRSZ
------------
DUMP("FRSZ")
--------------------------------------------------------------------------------
LaE095
Typ=96 Len=6: 76,225,201,48,57,53
SQL>
However, the newly inserted non-ascii field looks like the attached .PDF file in the original MS-SQL environment.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Data problems using ODBC Gateway [message #652090 is a reply to message #652076] |
Wed, 01 June 2016 06:35 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
emkk wrote on Wed, 01 June 2016 01:26Sorry, I simply posted the SQL response of the command you wrote, I didn't analyze then command, because I don't now it.
Fortunally, the problem has been resolved today morning, as our Oracle administrator set the gateway's parameters to our proper NLS format.
Let me offer a little professional advice, from someone who has been in this business over 35 years.
If someone offers you a solution that uses a command (or construct, or syntax, or key word, or whatever) that you "don't know it", the very first thing you should do is look it up in the relevant reference manual and learn as much about it as you can. Only after you have done so should you then try to apply it as suggested by whoever provided it.
As for solving your problem by have the admin set NLS parameters at the gateway .... if that was the solution, then you could have solved it for yourself with proper use of to_char/to_date/to_number functions within your SQL. And doing so would have been the preferable solution, because then you would not be at the mercy of some settings outside of your control. What are you going to do now, it it is discovered that the change at the gateway broke someone else's code?
|
|
|
|
| Re: Data problems using ODBC Gateway [message #652167 is a reply to message #652090] |
Fri, 03 June 2016 02:58 |
|
emkk
Messages: 9
Registered: May 2016
Location: Hungary
|
Junior Member |
|
|
I fully admit your advices, naturally, you are right. But let me tell something in excuse of me.
I'm not an Oracle expert or administrator, but work as a programmer over 36 year, and use Oracle Forms&Reports since 2001 succesfully, developing and supplying programs and reports for whole ERP software requirements of a company of 1000 employees, without knowing or using or needing the SET RECSEPCHAR SQL command.
So, repeating that you are right, I will not be ashamed of simply using with copy/paste the command that I've got as a help, instead of analyzing it.
Beleive me, I tried dozens of to_char/to_date/to_number etc. functions, without any result.
And , specially, the gateway was created only and exclusively for me, for my use, so I don't see any risk for other users.
Once again, thank you for every help I got, but I consider this topic closed.
|
|
|
|
| Re: Data problems using ODBC Gateway [message #652174 is a reply to message #652167] |
Fri, 03 June 2016 06:46 |
|
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
emkk wrote on Fri, 03 June 2016 02:58I fully admit your advices, naturally, you are right. But let me tell something in excuse of me.
I'm not an Oracle expert or administrator, but work as a programmer over 36 year
If you've been working as a programmer for over 36 years, then checking the docs when you see unfamiliar code should be second nature to you. It has nothing to do with being "an Oracle expert or administrator". It has nothing to do with any specific technology. It has everything to do with fundamental problem solving skills and and on-going desire for continued professional growth.
|
|
|
|
Goto Forum:
Current Time: Tue Dec 17 20:17:25 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
