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.
Best Answer
You have wrong string length in your SQL Server proc, look at this:
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 yourNvarchar
type tovarchar
in your SQL Server code