Oracle Database Discussions

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!