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.