This discussion is archived
10 Replies Latest reply: Jan 7, 2013 12:59 AM by kgronau RSS

Insert into table from DB LINK to SQL Server

981025 Newbie
Currently Being Moderated
Hi,

I'm trying to insert a set of values from a SQL Server into an Oracle table through a DB link.

The character set at SQL Server is UNICODE and my Oracle databases' character sets are US7ASCII & WE8ISO8859P1.

The column in question is a nvarchar type.
Using PL/SQL developer:
I can run a select query successfully, and get back data. But when I do an insert into a table that has nvarchar column type something wrong happens, and I cannot see the data in the column.

insert into tmp_cparties select "lngCounterpartiesUniqueID","strCounterpartyName" FROM tblcounterparties@NEDSQL;

select * from tmp_cparties:

     lngCounterpartiesUniqueID     strCounterpartyName
1     134     NASDAQ
2     133     ENERGY_
3     128     TRADING_SE-N-P
4     129     TRADING_SE-PGBT

truncate table tmp_cparties;

But, inside a block, the query is like:

BEGIN
insert into tmp_cparties select "lngCounterpartiesUniqueID","strCounterpartyName" FROM tblcounterparties@NEDSQL;
COMMIT;
END;

select * from tmp_cparties:
     lngCounterpartiesUniqueID     strCounterpartyName
1     134     
2     133     
3     128     
4     129     

Using SQL*Plus:

lngCounterpartiesUniqueID strCounterpartyName
------------------------- ------------------------------
134 N A S D A Q
133 E N E R G Y_
128 T R A D I N G _ S E - N - P
129 T R A D I N G _ S E - P G B T



I'm not able to make out what the issue is, and all my dependent procedures are failing because of this!! Please help!
  • 1. Re: Insert into table from DB LINK to SQL Server
    Bawer Journeyer
    Currently Being Moderated
    Using database links in Production systems (even using in programms) between an oracle and non oracle system is a bad idea. (between two oracle db, it works fine)
    you get more Problems depend on your database drivers.
    look for another solution like import/export.
  • 2. Re: Insert into table from DB LINK to SQL Server
    kgronau Guru
    Currently Being Moderated
    I'm sorry, but this suggestion is absolutely NOT true. With a gateway you're able to get realtime data and there are thousands of customers who rely on the gateway to foreign databases and use it in their daily business. They for example use it with Data Warehouse applications, with Web Servers as well as use it with Streams or simply with triggers to replicate data between an Oracle database and a foreign database.
  • 3. Re: Insert into table from DB LINK to SQL Server
    kgronau Guru
    Currently Being Moderated
    You mentioned the SQl Server table stores the data in nvarchar/nchar. It seems you're hitting an issue with the gateway which adds an additional space to every character replicating nvarchar data in PL/SQL code (defect 13950747 - NCHAR/NVARCHAR USING DG4MSQL ADDS A SPACE/BLANK TO EVERY CHARACTER USING PL/SQL ). It is currently being worked on this issue.

    Edited by: kgronau on Dec 21, 2012 6:50 AM

    Which gateway are you using and could you please provide the gateway init file and just in case you're using DG4ODBC also please upload the odbc.ini file.
  • 4. Re: Insert into table from DB LINK to SQL Server
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    To reinforce kgronau's comment about the gateway being a robust, reliable product to communicate with non-Oracle databases have a look at this note in My Oracle Support -

    Master Note for Oracle Gateway Products (Doc ID 1083703.1)

    which gives further information.

    Regards,
    Mike
  • 5. Re: Insert into table from DB LINK to SQL Server
    981025 Newbie
    Currently Being Moderated
    kgronau wrote:
    You mentioned the SQl Server table stores the data in nvarchar/nchar. It seems you're hitting an issue with the gateway which adds an additional space to every character replicating nvarchar data in PL/SQL code (defect 13950747 - NCHAR/NVARCHAR USING DG4MSQL ADDS A SPACE/BLANK TO EVERY CHARACTER USING PL/SQL ). It is currently being worked on this issue.

    Edited by: kgronau on Dec 21, 2012 6:50 AM

    Which gateway are you using and could you please provide the gateway init file and just in case you're using DG4ODBC also please upload the odbc.ini file.
    Hi,
    I'm using dg4odbc and my odbc.ini is:

    [ODBC 32 bit Data Sources]
    nedupgrade=Oracle in OraGtw11g_home2 (32 bit)
    SQLTEST1=SQL Server (32 bit)
    nedupg01a=Oracle in OraClient11g_home1 (32 bit)
    MDB_ETMS=Driver do Microsoft Access (*.mdb) (32 bit)
    NEDD_DEV=Oracle in OraClient11g_home1 (32 bit)
    MDB=Microsoft Access Driver (*.mdb) (32 bit)
    CONN_TO_SQL=SQL Server (32 bit)
    CONN_TO_MDB=Microsoft Access Driver (*.mdb) (32 bit)
    NED_ETMS=Microsoft Access Driver (*.mdb) (32 bit)
    MS Access Database=Microsoft Access Driver (*.mdb) (32 bit)
    Driver32=D:\app\icems-admin\product\11.2.0\tg_2\SQORA32.DLL
    [SQLTEST1]
    Driver32=C:\Windows\system32\SQLSRV32.dll
    [nedupg01a]
    Driver32=d:\app\icems-admin\product\11.2.0\client_1\BIN\SQORA32.DLL
    [MDB_ETMS]
    Driver32=C:\Windows\system32\odbcjt32.dll
    [NEDD_DEV]
    Driver32=d:\app\icems-admin\product\11.2.0\client_1\BIN\SQORA32.DLL
    [MDB]
    Driver32=C:\Windows\system32\odbcjt32.dll
    [CONN_TO_SQL]
    Driver32=C:\Windows\system32\SQLSRV32.dll
    [CONN_TO_MDB]
    Driver32=C:\Windows\system32\odbcjt32.dll
    [NED_ETMS]
    Driver32=C:\Windows\system32\odbcjt32.dll
    [MS Access Database]
    Driver32=C:\Windows\system32\odbcjt32.dll
    [Excel Files]
    Driver32=C:\Windows\system32\odbcjt32.dll
    [dBASE Files]
    Driver32=C:\Windows\system32\odbcjt32.dll


    Is there an alternate workaround to this, since it is stated as a defect? I need realtime data, and cannot use import/export or any other approach similar to that.
  • 6. Re: Insert into table from DB LINK to SQL Server
    kgronau Guru
    Currently Being Moderated
    So you're using Windows - what's the character set of your Oracle database (select * from v$nls_parameters)?
  • 7. Re: Insert into table from DB LINK to SQL Server
    981025 Newbie
    Currently Being Moderated
    kgronau wrote:
    So you're using Windows - what's the character set of your Oracle database (select * from v$nls_parameters)?
    Oh no, thats the odbc.ini on the windows machine where the HS listener is running from. My Oracle database is on Solaris.

    Character set is US7ASCII. I have another one with WE8ISO8859P1, and the query is behaving the same way on both.
  • 8. Re: Insert into table from DB LINK to SQL Server
    kgronau Guru
    Currently Being Moderated
    I see ...
    There are a few know work arounds like for example it worked for some customers when using a Unicode Oracle database.

    Could you please try to change the insert statement using a full qualified object name like which also includes the username:
    insert into tmp_cparties select "lngCounterpartiesUniqueID","strCounterpartyName" FROM "dbo"." tblcounterparties"@NEDSQL;
    Does that work for you?

    - Klaus
  • 9. Re: Insert into table from DB LINK to SQL Server
    981025 Newbie
    Currently Being Moderated
    kgronau wrote:
    I see ...
    There are a few know work arounds like for example it worked for some customers when using a Unicode Oracle database.

    Could you please try to change the insert statement using a full qualified object name like which also includes the username:
    insert into tmp_cparties select "lngCounterpartiesUniqueID","strCounterpartyName" FROM "dbo"." tblcounterparties"@NEDSQL;
    Does that work for you?

    - Klaus
    Hi Klaus,

    The approach seems to be working for now! Can this be taken as a permanent solution, as the packages are to deployed for production use ?
  • 10. Re: Insert into table from DB LINK to SQL Server
    kgronau Guru
    Currently Being Moderated
    It was working in the past without the need to specify the owner - but somehow a piece of code changed which now impacts the resolution of objects using PL/SQL. As I don't know what will change in future I'm not able to answer with yes or no - but what can be done all the time is to log a support ticket at Oracle support stating that is was working with release xyz and fails with abc and support can then go ahead and file a bug for this isue.

    -Klaus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points