When calling an Oracle function with the following in parameter types
NUMBER(7,0)
VARCHAR2(8 BYTE)
VARCHAR2(4 BYTE)
DATE
and return type NUMBER(14,5)
from SQL Server 2008R2 via a linked Oracle server, I declared the parameter variables on SQL Server side as follows:
numeric(7,0) -- also tried INT, BIGINT
varchar(8)
varchar(4)
datetime -- also tried DATE
and return variable type numeric(14,5)
exec ( 'BEGIN ? := db.pkg.getPrice(?,?,?,?); END;', @price, @id1, @id2, @curr, @ed ) AT ORASRV
The call fails with the following error:
OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1".
What am I doing wrong?
To test that function I used the following PL/SQL script and it worked fine (printed 0
which is a correct result for the parameters):
declare
c varchar2(4);
ed date;
res number(14,5);
begin
c := 'USD';
ed := to_date('22-MAY-14', 'DD-Mon-YYYY');
res := pkg.getPrice(214360, '129746', c, ed);
DBMS_OUTPUT.PUT_LINE(res);
end;
Also created a function just for testing:
create or replace FUNCTION getTest (
a IN VARCHAR2 -- also tried INTEGER
)
RETURN VARCHAR2 -- also tried INTEGER
IS
res VARCHAR2(1); -- also tried INTEGER
BEGIN
res := a;
RETURN res;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
When I call it from SQL Server as follows
declare @a varchar(1),@b varchar(1) -- also tried INT
select @b = 'a'
exec ( 'BEGIN ? := db.getTest(?); END;', @a, @b) AT ORASRV
I get the following error:
OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1".
Msg 7215, Level 17, State 1, Line 172
Could not execute statement on remote server 'ORASRV'.
In case of INTEGER on Oracle side and INT on SQL Server side the message is just "ORA-06502: PL/SQL: numeric or value error
Best Answer
Your datatypes should be okay, however you need to change your call slightly and add OUTPUT after your @price. I've tested this against Oracle11g on INTEGER -> INT, VARCHAR2 -> VARCHAR, DATE -> Datetime using your getTest function