MS SQL: Call function with parameter from linked Oracle server

Question

I've got MS SQL Server. There is a linked ORACLE server on it named HOST4KS. At Oracle DB I have two functions - one parameterless and one with parameter. I need to get their result in my tsql query.

1) function without parameter

RetVal:=KSOL.routines.Tst;

It always returnes int = 123

DECLARE @Resultint
select top 1 @Result= TST from openquery
(HOST4KS, 'SELECT KSOL.routines.Tst from dual') 
select @Result

Ok, I get the '123' in my variable @Result

2) function with parameter

RetVal:=KSOL.routines.Tst2(456);

It will always return passed parameter as result.

DECLARE @Result int
select @Result = TST from openquery
(HOST4KS, 'SELECT KSOL.routines.Tst2(455) from dual') 
select @Result

works fine. I get @Result=455

QUESTION: HOW TO PASS PARAMETER TO THAT FUNCTION AND GET RESULT INTO VARIABLE?

I tried:

1)

DECLARE @ReturnValue int
DECLARE @InputPara int
DECLARE @OutputPara int
set @InputPara = 456
EXECUTE ( 'BEGIN ? := KSOL.routines.Tst2(?); END;', @ReturnValue, @InputPara, @OutputPara OUTPUT) AT HOST4KS

RESULT: OLE DB provider "OraOLEDB.Oracle" for linked server "HOST4KS" returned message "The system cannot find message text for message number 0x80040e21 in the message file for OraOLEDB.". Msg 7215, Level 17, State 1, Line 86 Could not execute statement on remote server 'HOST4KS'.

2)

DECLARE @ReturnValue int
DECLARE @InputPara int
DECLARE @OutputPara int
set @InputPara = 456
EXECUTE ( 'BEGIN ? := KSOL.routines.Tst2(?); END;', @ReturnValue, @InputPara) AT HOST4KS

RESULT: OLE DB provider "OraOLEDB.Oracle" for linked server "HOST4KS" returned message "ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1". Msg 7215, Level 17, State 1, Line 92 Could not execute statement on remote server 'HOST4KS'.

3)

DECLARE @RetVal int
declare @Parameter int
exec HOST4KS.[defaul].dbo.sp_executesql N'SELECT KSOL.routines.Tst2(@Parameter)',N'@Parameter=10',@Parameter=10

RESULT: OLE DB provider "OraOLEDB.Oracle" for linked server "HOST4KS" returned message "Unspecified error". Msg 7323, Level 16, State 2, Line 111 An error occurred while submitting the query text to OLE DB provider "OraOLEDB.Oracle" for linked server "HOST4KS".

and etc. No luck...

Which Oracle client did you install or your SQL Server server? To which Oracle version are you connecting (11g, 12c)? — Brian Leach
– Brian Leach, Commented Jul 24, 2017 at 18:47

Brian Leach · Accepted Answer · 2017-07-24 20:26:29Z

Near as I have been able to discover, SQL Server does not allow you to call a remote function directly, you must use openquery. Openquery does not allow the string to be a variable, nor does it allow the string to have arguments. I had a similar problem, but I was making a function call to another SQL Server. The answer for Oracle was almost identical:

On Oracle:

CREATE OR REPLACE PACKAGE testuser.routines
AS
    FUNCTION tst
        RETURN INTEGER;

    FUNCTION tst2 (p_arg IN INTEGER)
        RETURN INTEGER;
END routines;

CREATE OR REPLACE PACKAGE BODY testuser.routines
AS
    FUNCTION tst
        RETURN INTEGER
    AS
    BEGIN
        RETURN 123;
    END tst;

    FUNCTION tst2 (p_arg IN INTEGER)
        RETURN INTEGER
    AS
    BEGIN
        RETURN p_arg;
    END tst2;
END routines;

grant execute on testuser.routines to public;

The SQL Server Call (CIST is my linked Server to the Oracle database)

create table #tempTable (TST int);
declare @value integer = 455;
declare @cmd nvarchar(2000);
declare @result integer;
set @cmd = 'insert into #tempTable(TST) select TST from openquery
(CIST, ''SELECT TESTUSER.routines.Tst2(' + cast(@value as varchar(20)) + ') as TST from dual'') ';


exec (@cmd);
select * from #tempTable;
drop table #tempTable;
go

Executing this successfully returns 455.

Collectives™ on Stack Overflow

MS SQL: Call function with parameter from linked Oracle server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related