1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Select money from MSSQL , throught DBLINK.

Discussion in 'SQL PL/SQL' started by Schmetterling, Nov 15, 2013.

  1. Schmetterling

    Schmetterling Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi there!
    This is my first post, I hope you can help me guys!

    ** Useful data:
    - Oracle 11.2g
    - DBLINK to MSSQL 2000

    ** Task: Migrate data from MSSQL to ORACLE.

    Here's my problem.

    I ve used a bcp command to export my data from MSSQL. Then I used SQLLDR to import the file.
    I have a column , in both tables called "amount": in MSSQL , its a number(18,2) type and in the oracle table, its a number(18,2) too.

    I have also a DBLINK connected to the MSSQL DB, that allows me to select directly to my table, the original one.
    I run the following select statement in ssql plus

    Code (Text):
    select  "amount" from transferencias@dblink_mssql
           where "tarjeta"='1111111111'
           and "secuencia" = 197870
     
    It should return a number with this format: "110,22" i.e with 2 decimals. But instead , it returns the number like a integer - > "110".

    I want to compare both tables using MINUS to be sure that the migration worked succesfully. But it always show differences because of the format for numbers.

    I've checked the default format for NLS_NUMERIC_CHARACTERS - > ".,"

    If I try to apply a format to my select it doesnt work either. to_number("amount", 999999.99).


    How can I select the amount with the decimals throught the dblink?

    Thanks in advance!

    Gaby
     
  2. Schmetterling

    Schmetterling Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    I forgot to mention that I have a Heterogeneous Services installed, working as a gateway between mssql 2k and oracle 11.2g.

    Tyvm.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    you can tell what ODBC version of drivers?

    show here an example of the file of unloading from ms sql and file of parameter for sqlldr
     
  4. Schmetterling

    Schmetterling Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi there. thanks for your replay.

    Atm I dont have the version, only the DBA area can access that information.

    Btw, When I use sqlldr all fields are succesfully loaded, even with decimals. I use in my ctrl file : // AMOUNT "TO_NUMBER:)importe, '999999999.99')" , // and it works fine.

    The problem is when I execute the select throught the dblink. Do I have a problem with the odbc 64bits version or should I configure my HS to set another default format ? Ive been reading about HS_NLS configurations, but I didnt find one for number formats.

    :( Im starting to think that I must compare the column as integer, and ignore the decimals.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "Atm I dont have the version, only the DBA area can access that information."

    That is incorrect as when you connect via SQL*Plus the banner clearly reports the version in use:

    Code (SQL):
    $ sqlplus / AS sysdba
     
    SQL*Plus: Release 11.2.0.3.0 Production ON Mon Nov 18 09:13:20 2013
    Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.

    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>
    Also pretty much every user can select from v$version:

    Code (SQL):
    SQL> CONNECT bong/######
    Connected.
    SQL> SELECT * FROM v$version;
     
    BANNER
    --------------------------------------------------------------------------------
    Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS FOR 64-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
     
    SQL>
    The user in the example above has no DBA privileges yet can easily retrieve the version information from the database.

    As far as the ODBC driver is concerned you might try this driver:

    http://www.easysoft.com/applications/oracle/database-gateway-dg4odbc.html
     
  6. Schmetterling

    Schmetterling Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi David, I cannot access to the gateway.
    There are 3 servers , one MSSQL DB (1) , another for HS ( working as a oracle gateway )(2) and the ORACLE DB where I need to leave the data (3)

    The ODBC is configured in the gateway, so I dont have access there. If you need this data I can check it with another workmates.

    About the oracle version (3) is

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE 11.2.0.3.0 Production
    TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production


    tyvm.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "If I try to apply a format to my select it doesnt work either. to_number("amount", 999999.99)."

    SQL Server isn't Oracle and you're attempting to use an Oracle function against a SQL Server database. SQL Server, as far as I know, has no TO_NUMBER function. The same function does work on SQL Loader since you ARE using an Oracle database.

    You might try re-writing your query to use CAST:

    Code (SQL):

    SELECT
       ...,
       CAST("amount",NUMBER(18,2)),
       ...
    FROM
       ...;
     
    Since CAST is also a SQL Server function it may return the values in the proper format.
     
  8. Schmetterling

    Schmetterling Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    David, when I run the select you mentioned I receive the following error: ORA-00905. Missing keyword. BC I must execute ora commands, Ive tried with DECODE too, but its useless.

    If I try with CAST for Oracle :
    Code (Text):
     
    select  CAST("amount"  AS NUMBER(18,2)) from someexample@dblink_1
    where "tarjeta"='1111111'
    And It doesnt show the decimals.


    Its seems that Im already receiving from the gateway a number without decimals. Doesnt matter what format I apply in the select, It always return integers.
     
  9. Schmetterling

    Schmetterling Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    I had another case. I needed to select a date from mssql and execute minus between oracle and mssql.

    I needed to setup : alter session set nls_date_format='dd/mm/yyyy hh:mi:ss a.m.'; To compare both date formats. It worked perfectly.

    Im stuck.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Have you set HS_LANGUAGE properly for the SQL Server database?
     
  11. Schmetterling

    Schmetterling Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    I tried with export
    Code (Text):
    NLS_NUMERIC_CHARACTERS=.,
    and it doesnt work.
     
  12. Schmetterling

    Schmetterling Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Yes, its correct. Everywhere: SPANISH_SPAIN

    I dont have this kind of problem with type money, I can select any amount with decimals, but not for number(18,2)
     
  13. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  14. Schmetterling

    Schmetterling Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    We finally solved the problem. We configured HS and ORACLE client to American. We figured out that there was smth configured as american defaults so we decided to change it.
    Needed to do:
    Change HS configuration to American
    Change Oracle client to American: on windows need to setting the enviroment variable NLS_NUMERIC_CHARACTER = .,
    Thats all.


    Thanks everyone!!
     
  15. Dennis

    Dennis Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Italy
    Thanks to you Schmetterling,
    you saved me from heavy working nights.