Queries Hang When Doing Sqlserver Cross-Server Call via Dg4msql (Doc ID 1123058.1)

Last updated on JUNE 30, 2010

Applies to:

Oracle Database Gateway for SQL Server - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Microsoft Windows (32-bit)

Symptoms

The query hangs when doing SQL Server cross-server call using PLSQL Developer tool via Database Gateway for SQL Server 11.1.0.7, for example:

--- Oracle views

CREATE OR REPLACE VIEW MREF.VIEW_SECM_CUR_SECURITY AS
SELECT "PRODUCT_ID","START_DATE","END_DATE","PRODUCT_TYPE","PRODUCT_SUBTYPE","NAME"
FROM OUT_Cur_Security@dg4msqlpc
WITH READ ONLY;

CREATE OR REPLACE VIEW MREF.VIEW_SECM_CUR_REL_SEC_SEC AS
SELECT "PARENT_PRODUCT_ID","CHILD_PRODUCT_ID","START_DATE","END_DATE","REL_TYPE"
FROM OUT_Cur_Rel_Security_Security@dg4msqlpc
WITH READ ONLY;



CREATE OR REPLACE VIEW MREF.VIEW_SECM_CUR_IDENT_SECURITY AS
SELECT "PRODUCT_ID","START_DATE","END_DATE","IDENT_TYPE","IDENT_SUBTYPE","IDENTIFIER" FROM OUT_Cur_Ident_Security@dg4msqlpc
WITH READ ONLY;

--- hanging query

SELECT
I.Identifier AS Name_,
'SWAP_SEC_ID' AS Description_
FROM
MREF.VIEW_SECM_Cur_Security S,
MREF.VIEW_SECM_Cur_Rel_Sec_Sec R,
MREF.VIEW_SECM_Cur_Security CS,
MREF.VIEW_SECM_Cur_Ident_Security I
WHERE
S.Product_Type = 'SWAP'
AND S.Product_SubType IN ('INTEREST', 'CURRENCY', 'DEBT')
AND R.Parent_Product_ID = S.Product_ID
AND R.Rel_Type = 'Leg_Of'
AND CS.Product_ID = R.Child_Product_ID
AND CS.Product_SubType <> 'SPREAD_LEG'
AND I.Product_ID = CS.Product_ID
AND I.Ident_Type = 'FMC';

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms