Skip to Main Content
  • Questions
  • Error when using DBLINK to SQL Server from Oracle : does not support SYS_OP_C2C in this context

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Sergiusz Wolicki

Thanks for the question, Arief.

Asked: August 22, 2017 - 4:00 am UTC

Answered by: Sergiusz Wolicki - Last updated: August 31, 2017 - 1:52 am UTC

Category: PL/SQL - Version: Oracle 11G

Viewed 1000+ times

You Asked

Helloo ,

I've a problem with read and save data into Oracle Database form SQL Server using DBLINK DG4ODBC.
The problem is where clause condition must harcoded.

Spec of Table in SQL Server is
t_link
(NOS NVARCHAR(10),
DETAIL_SPEC NVARCHAR(MAX),
DETAIL_SPEC_CMMT NVARCHAR(MAX),
DATE NVARCHAR(20),
TRAN NVARCHAR(20))


below it's my script

 create or replace procedure c_get_nxauto_abon(vmaxdate varchar2) is
 cursor c_data is
 select NOS,DETAIL_SPEC, DETAIL_SPEC_CMMT
 from t_link@DG4ODBC
 where date > vmaxdate 
 and tran = 'GPA0    '
 order by date
 ;
 type clist is table of c_data%rowtype;
 l_rows clist;
 
 
 begin
  open c_data; fetch c_data bulk collect into l_rows; close c_data;
  for i in 1..l_rows.count
  loop
   begin
   insert into tab_les (NOS, DETAIL_SPEC, DETAIL_SPEC_CMMT, 
)
   values(  
             l_rows(i).NOS                  ,
             l_rows(i).DETAIL_SPEC        ,
             l_rows(i).DETAIL_SPEC_CMMT  );
   end;
   
   end loop;
   
   
 exception when others then
  verr := substr(sqlerrm,1,300);
     commit;
 end;




it produce error : ORA-02070: database DG4ODBC does not support SYS_OP_C2C in this context

but if

 
 ..
cursor c_data is
 select NOS,DETAIL_SPEC, DETAIL_SPEC_CMMT
 from t_link@DG4ODBC
 where date > '20170707000000' 
 and tran = 'GPA0    '
 order by date
 ..


it works.
please give any suggestions.
because it is must be parameterized.
tq




and we said...


I do not have an appropriate environment to verify my analysis, however here it goes:

The SYS_OP_C2C function is an internal function that does conversion between VARCHAR2 and NVARCHAR2 data types. It is added by the SQL engine when implicit data type conversion must be performed. In your case, the conversion is needed between the parameter "VMAXDATE" VARCHAR2 and the column "DATE" NVARCHAR. The SQL engine sends something like "where date > sys_op_c2c(vmaxdate)" to the ODBC gateway and the gateway does not know how to translate SYS_OP_C2C. Try changing the data type of "date" to NVARCHAR2. With "DATE" having the data type NVARCHAR2, the conversion should not be needed in SQL. The implicit conversion will be done by PL/SQL if the procedure C_GET_NXAUTO_ABON is called with a VARCHAR2 parameter such as '20170707000000'.

and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

It cannot be done

August 28, 2017 - 3:10 am UTC

Reviewer: Arief Rachman

Thanks Sergiusz Wolicki,

For your suggestions
" Try changing the data type of "date" to NVARCHAR2"

it cannot be done because its not our environment (others vendor/consultant) . We just have to get data from it.

Tq
Connor McDonald

Followup  

August 28, 2017 - 5:36 am UTC

Perhaps try dbms_hs_passthrough

https://connor-mcdonald.com/2012/07/18/dbms_hs_passthroughthe-magic-package/


Worth a shot.

August 28, 2017 - 4:58 pm UTC

Reviewer: Chuck Jolley from okc, ok USA

Try putting your argument into a local varchar2(20)
e.g.
create or replace procedure c_get_nxauto_abon(vmaxdate varchar2) is
 lvmaxdate varchar2(20);
 lvmaxdate := vmaxdate;
 cursor c_data is
 select NOS,DETAIL_SPEC, DETAIL_SPEC_CMMT
 from t_link@DG4ODBC
 where date > trim(lvmaxdate) /*just in case */
 and tran = 'GPA0    '
 order by date
...


describe "tab_les"
Sergiusz Wolicki

Followup  

August 28, 2017 - 11:17 pm UTC


All suggestions will, more or less, either require changes to the procedure or be an ugly workaround. Why not contact the other vendor/consultant to correct the procedure? Alternatively, create and use a corrected copy of the procedure, if you know the source code.

dbms_hs_passthrough

August 29, 2017 - 4:27 am UTC

Reviewer: Arief Rachman

Perhaps try dbms_hs_passthrough

https://connor-mcdonald.com/2012/07/18/dbms_hs_passthroughthe-magic-package/

its appear an error
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][ODBC SQL Server Driver]String data, right truncation {01004}[Microsoft][ODBC SQL Server Driver]String data, right truncation {01004

tq

So been there.

August 29, 2017 - 6:40 pm UTC

Reviewer: Chuck Jolley from okc, ok, usa

That's the error you get trying to return a varchar(max) through a database link to a varchar2.
An nvarchar(max) in sqlserver corresponds to an Oracle long or clob.
The way we've gotten around this is to use a view in the non-Oracle database to cast sections of the varchar(max) into separate columns that correspond to varchar2(4000) columns in the Oracle side.
Not sure you have access to that though.
Maybe something in here: https://asktom.oracle.com/pls/apex/f%3Fp%3D100:11:0::::P11_QUESTION_ID:52297289480186
Connor McDonald

Followup  

August 31, 2017 - 1:52 am UTC

Nice input.

More info

August 29, 2017 - 8:14 pm UTC

Reviewer: Chuck Jolley from okc, ok, usa

In case the chopped up view is obtainable for you,
I went over our code to remind myself of some details.

SQL Server side:
table (col varchar(max))

view vs is
select  convert(varchar(1000), substring(l.col, 1, 1000))    as col_1,
        convert(varchar(1000), substring(l.col, 1001, 1000)) as col_2,
        convert(varchar(1000), substring(l.col, 2001, 1000)) as col_3,
        convert(varchar(1000), substring(l.col, 3001, 1000)) as col_4,
etc
from table


Oracle side:
view vo is
select col_1,
       col_2,
       col_3,
       col_4,
       etc
from vs@alien_db


The reason for making the ss view columns varchar(1000) is two fold.
1, difference in bytes per character between the two DBs
2, MS odbc for linux 11 doesn't seem to support varchar2(4000) on the oracle side.
So the data coming from the MS side needs to be 1/4 the width you would expect. For all I know it will be 1/8th for an nvarchar.
Your mileage may vary.

Connor McDonald

Followup  

August 31, 2017 - 1:52 am UTC

Thanks for stopping by Chuck

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database