Unable to get output variable of Oracle procedure using Sql linked server

Question

I have created a Heterogeneous connection between SQL Server and Oracle and tried to call a oracle procedure from SQL Server.I got below error when i tried to execute the procedure.

OLE DB provider "ORAOLEDB.Oracle" for linked server "TESTORACLE" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "TESTUSER.PKG_TEST", line 1744". Msg 7215, Level 17, State 1, Procedure SQLSTOREDPROCEDURE, Line 75 [Batch Start Line 0] Could not execute statement on remote server 'TESTORACLE'.

I have gone through the code and found that the error occurs only when assigning the value for OUT parameter.

SQL SERVER PROCEDURE

CREATE PROCEDURE [dbo].[SQLSTOREDPROCEDURE]  AS 
DECLARE @op_update_status Nvarchar(50) 
DECLARE @op_error_message Nvarchar(500)
BEGIN
 SET NOCOUNT ON;
 SET XACT_ABORT ON;
 SET @p_process_name = 'PROCESSNAME'
 EXECUTE ('BEGIN  TESTUSER.PKG_TEST.P_SET_VALUES(?,null,?,?); END; ',@p_process_name,@op_update_status ,@op_error_message ) AT [TESTORACLE];  

 RETURN 0;
END

ORACLE PROCEDURE

PROCEDURE  P_SET_VALUES  (   p_process_name      VARCHAR2,                                   
                             p_processed_dt      DATE,                                    
                             op_update_status OUT VARCHAR2,
                             op_error_message OUT VARCHAR2
                         ) AS

 v_update_status VARCHAR2(50);
 v_error_message VARCHAR2(500);

 BEGIN


    USER2.PKG_TEST2.P_SET_VALUE  ( p_process_name,'TEST STATION',p_processed_dt,v_update_status,v_error_message) ;
     op_update_status := v_update_status;
     op_error_message := v_error_message;


END;    

Then I googled and alter the procedure by specifying the OUTPUT parameter in SQL Server.

EXECUTE ('CALL  TESTUSER.PKG_TEST.P_SET_VALUES(?,null,?,?)',@p_process_name,@op_update_status OUTPUT ,@op_error_message OUTPUT) AT [TESTORACLE];

After that I get below error,

Msg 7215, Level 17, State 1, Procedure SQLSTOREDPROCEDURE, Line 75 [Batch Start Line 0] Could not execute statement on remote server 'TESTORACLE'.

Please help me to overcome this error.

Thanks.

UPDATE

Even though Sql server return a error message, oracle procedure was called. Checked with dummy insert in application log table.

Answer 1

You have wrong string length in your SQL Server proc, look at this:

v_update_status VARCHAR2(50);
v_error_message VARCHAR2(500);


DECLARE @op_update_status Nvarchar(50) 
DECLARE @op_error_message Nvarchar(500)

Original oracle parameters are of length 50 and 500 bytes, but you use NVARCHAR and your SQL Server variables are 100 and 1000 bytes long (UCS-2 uses 2 bytes to store each symbol)

Update: I've found Oracle data types and saw that varchar2 is not unicode data type, Oracle uses nvarchar also, so all what you need is to change your Nvarchar type to varchar in your SQL Server code