Skip to Main Content
  • Questions

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

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Arief.

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

Last updated: August 31, 2017 - 1:52 am UTC

Version: Oracle 11G

Viewed 10K+ times! This question is

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'.

Rating

  (6 ratings)

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

Comments

It cannot be done

Arief Rachman, August 28, 2017 - 3:10 am UTC

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
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.

Chuck Jolley, August 28, 2017 - 4:58 pm UTC

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
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

Arief Rachman, August 29, 2017 - 4:27 am UTC

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.

Chuck Jolley, August 29, 2017 - 6:40 pm UTC

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
August 31, 2017 - 1:52 am UTC

Nice input.

More info

Chuck Jolley, August 29, 2017 - 8:14 pm UTC

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
August 31, 2017 - 1:52 am UTC

Thanks for stopping by Chuck

londero, October 28, 2021 - 11:01 am UTC

Great!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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