Post reply

Calling oracle function from sql does not return result

  • December 23, 2015 at 7:27 am

    #310435

    Hi,

    I am new to sql server and old to Oracle.

    I have the following Oracle function:

    create or replace function ak_del_febs (p varchar2) return number is

    begin

    return 20;

    end;

    Here is my code for sql server:

    begin

    declare @itemcode nvarchar(MAX) = 'test';

    declare @Stock numeric;

    EXECUTE ( 'BEGIN ? := ak_del_febs( ? ); END;', @Stock OUTPUT, @itemcode)at MegaOracle;

    print @Stock

    print @itemcode;

    end

    GO

    Problem is I get no result (null)...

    Any ideas?

    thx Andreas

  • December 26, 2015 at 9:49 pm

    #1848153

    EXECUTE syntax (https://msdn.microsoft.com/en-us/library/ms188332.aspx) states

    @parameter

    Is the parameter for module_name, as defined in the module.

    Key phrase being "in the module", where "module" refers to an object in sys.all_sql_modules. In contrast to harnessing SQL Server modules, you are instead passing a string to a linked server (with the expectation that the string will be parsed and executed at the linked server). The parameter arguments passed to EXECUTE refer to a SQL Server module that was not supplied, thus both arguments are unused.

    You should instead concatenate @itemcode within your string, and EXECUTE the resulting string. To deal with the output (as opposed to OUTPUT, which is moot per prior paragraph), you can instead DECLARE @Stock TABLE (Stock numeric);

    INSERT @Stock EXEC (@ConcatenatedString) AT OracleLinkedServer;

    Use OPENQUERY for sanity checks, and when useful.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply