How To Call Function/procedures In The Linked SQL Server From Oracle Database
Last updated on FEBRUARY 22, 2018
Applies to:Oracle Database Gateway for SQL Server - Version 220.127.116.11 and later
Information in this document applies to any platform.
Q1 - It seems that the two parameters related to remote functions and procedures in gateway initialization file are mutually exclusive, and one works for functions and the other procedures. Why is this?
Q2 - 1. To call a stored procedure in SQL server via gateway, ONLY HS_FDS_RESULTSET_SUPPORT parameter needs to be configured. The other two parameters below for function calls must NOT be configured/present in initialization file.
- 2. HS_FDS_PROC_IS_FUNC and HS_CALL_NAME are ONLY used to call functions in remote SQL server. If configured, HS_FDS_RESULTSET_SUPPORT must NOT be configured/present.
- 3. If the above two assumptions are true, we can only use either stored procedure OR function to return results from SQL server via gateway, but NOT BOTH.
Q3 - There are two problems with this approach:
- 1. pass through approach, instead of direct procedure call to SQL server. Preference is the direct procedure call.
- 2. Pulling a massive amount of data from linked SQL server via gateway, one definitely cannot use CURSOR To process one by one. Is there a way to to bulk insert to a Oracle table from the remote procedure, with the pass through approach?
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms