'm working on a project that will involve a daily refresh from a table in a SQL Server 2012 database to an Oracle 11g database. Many of the columns in the SQL Server table are defined as nvarchar - two are nvarchar(MAX), some are nvarchar(2500), others are smaller. The Oracle database characterset is WE8MSWIN1252.
The SQL Server table is defined as
[GIS_ID] nvarchar(50) NOT NULL,
[ORGANIZATION] nvarchar(255) NOT NULL,
[CONTACT_NAME_FIRST] nvarchar(50) NOT NULL,
[TITLE] nvarchar(255) NOT NULL,
[ADDRESS] nvarchar(255) NOT NULL,
[CITY] nvarchar(50) NOT NULL,
[STATE] nvarchar(4) NOT NULL,
[ZIP] nvarchar(10) NOT NULL,
[PHONE] nvarchar(50) NOT NULL,
[EXTENSION] nvarchar(10) NOT NULL,
[EMAIL] nvarchar(50) NOT NULL,
[ORG_WEBSITE] nvarchar(50) NOT NULL,
[PROJECT_NAME] nvarchar(255) NOT NULL,
[DESCRIPTION] nvarchar(MAX) NOT NULL,
[LOC_STATE] nvarchar(255) NOT NULL,
[LOC_COUNTY] nvarchar(255) NOT NULL,
[WATERSHED] nvarchar(2500) NOT NULL,
[COMMENTS] nvarchar(MAX) NULL)
The Oracle table is defined as
GIS_ID VARCHAR2(50 BYTE),
ORGANIZATION VARCHAR2(255 CHAR),
CONTACT_NAME_FIRST VARCHAR2(50 CHAR),
TITLE VARCHAR2(255 CHAR),
ADDRESS VARCHAR2(255 CHAR),
CITY VARCHAR2(50 CHAR),
STATE VARCHAR2(4 CHAR),
ZIP VARCHAR2(10 CHAR),
PHONE VARCHAR2(50 CHAR),
EXTENSION VARCHAR2(10 CHAR),
EMAIL VARCHAR2(50 CHAR),
ORG_WEBSITE VARCHAR2(50 CHAR),
PROJECT_NAME VARCHAR2(255 CHAR),
DESCRIPTION CLOB,
LOC_STATE VARCHAR2(255 CHAR),
LOC_COUNTY VARCHAR2(255 CHAR),
WATERSHED CLOB,
COMMENTS CLOB
If I insert from SQL Server:
insert into [RIVENDELL_HBOSWELL]..[HBOSWELL].[RICS_PROJECT]
select * from PROJECT
All the data loads into the Oracle table. But if I run the insert from Oracle:
insert into rics_project
select * from project@godaddytest
I get "ORA-00947: not enough values", because the Oracle query simply ignores the nvarchar(MAX) columns. So SQL Server is apparently converting the Unicode characterset to Oracle's non-Unicode set, but Oracle doesn't do the conversion when I run the insert from Oracle. Am I interpreting this correctly? Short of reconfiguring the Oracle database as Unicode, is there a workaround that would allow me to control this process from Oracle?
Thanks,
Harry