December 23, 2015 at 7:27 am
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
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