Calling A Sql*Server Stored Procedure With Text Columns Causes Errors ORA-2055 and Ora-28511 (Doc ID 374989.1)

Last updated on JANUARY 19, 2017

Applies to:

Oracle Database Gateway for SQL Server - Version 9.2 to 10.2
Information in this document applies to any platform.
Oracle Transparent Gateway for Microsoft SQL Server - Version: 9.2 to 10.2

Checked for Relevancy on 30-Nov-2010


Symptoms

Using TG4MSQL to call a SQL*Server stored procedure that uses a table with a TEXT column gives errors ORA-2055 and ORA-28511.


The table in SQL*Server is created as -

create table KW_TAB_A (COL_A varchar(10),
COL_B text,
COL_C text
)

The stored procedure is in the format -

CREATE PROCEDURE dbo.kwsp2 @STR1 VARCHAR(10), @STR2 text, @STR3 text, @STR4
bigint OUT AS
INSERT KW_TAB_A VALUES (@STR1,@STR2,@STR3)
SELECT @STR4 = 1
GO

This can be called from SQL*Server successfully -

exec kwsp2 'TEST2','Long String 2','Longer string 2',null

 

Calling the procedure from Oracle causes the errors -

SQL> declare 
var1 varchar2(10); 
var4 number(10); 
ret integer; 
begin 
var1 := 'OraTest1'; 
var4 := null; 
"dbo"."kwsp2"@msql(var1,null,null,var4); 
dbms_output.put_line('Return value = '||var4); 
end; 
/
declare
*
ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required
ORA-02068: following severe error from MSQL
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=tg4msql)))
ORA-06512: at line 8

 

 










Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms