How To Call Function/procedures In The Linked SQL Server From Oracle Database (Doc ID 2362359.1)

Last updated on FEBRUARY 04, 2019

Applies to:

Goal

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?

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.