SQL Server to Oracle 10g – Correct Data Types for Function Call

oracle-10gsql-server-2008-r2

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

exec ( 'BEGIN ? := db.pkg.getPrice(?,?,?,?); END;', @price OUTPUT, @id1, @id2, @curr, @ed ) AT ORASRV