MSSQL SP retrieving data from Oracle SP
-
Hello,
I am currently unable to retrieve data from an Oracle package/procedure from within a MSSQL stored procedure. I can run queries to retrieve data fine. Below is the error message I am currently receiving, the call from MSSQL to Oracle, and the Oracle package/procedure. I am on the MSSQL side; however, am working closely with the Oracle developer.
We are connecting to Oracle via a linked server using "the most current Oracle OLE driver". Please check syntax too!
ERROR MESSAGE
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Provider does not support PL/SQL stored procedures/functions with RECORD or TABLE arguments.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare:
repare returned 0x80004005: ].CALL FROM MSSQL SP
select *
from openquery([RMSDEV02],
'{call mer_adbidsht_web.od_prc_get_price_in_effect_ole.Main_get_price_in_effect_ole (
''RDW'',
''REG'',
''N'',
442497,
''ZON'',
4,
''2004-10-30'',
{resultset 1, w_plsql_tbl}
 
} ')ORACLE PACKAGE/PROCEDURE
CREATE OR REPLACE Package BODY od_prc_get_price_in_effect_ole AS
-----------------------------------------------------------------------------------------
PROCEDURE Main_get_price_in_effect_ole (w_calling_app IN VARCHAR2,
w_business_func IN VARCHAR2,
w_rebate_reqd IN VARCHAR2,
w_sku_id IN NUMBER,
w_group_cd IN VARCHAR2,
w_group_id IN NUMBER,
w_effective_date IN VARCHAR2,
w_plsql_tbl OUT tab_type) IS
TYPE C_OUT_CURSOR IS REF CURSOR;
w_cursor C_OUT_CURSOR;
BEGIN
od_prc_get_the_price_in_effect.Main_get_the_price_in_effect(w_calling_app, w_business_func,
w_rebate_reqd, w_sku_id, w_group_cd, w_group_id, w_effective_date, w_cursor);
fetch w_cursor BULK COLLECT into w_plsql_tbl;
close w_cursor;
END main_get_price_in_effect_ole;
-----------------------------------------------------------------------------------------
END od_prc_get_price_in_effect_ole;
/
CREATE OR REPLACE Package od_prc_get_price_in_effect_ole AS
-- a wrapper package as ole does not support overloaded procedures
TYPE rec_plsql IS record(
sku NUMBER,
group_cd VARCHAR2(3),
loc_id NUMBER,
effective_dt VARCHAR2(10),
end_dt VARCHAR2(10),
price_type VARCHAR2(3),
price_change_type VARCHAR2(3),
cur_price VARCHAR2(9),
reg_price VARCHAR2(9),
return_code VARCHAR2(4),
error_msg VARCHAR2(100),
bulk_unit_1 NUMBER,
bulk_price_1 NUMBER,
bulk_unit_2 NUMBER,
bulk_price_2 NUMBER,
bulk_unit_3 NUMBER,
bulk_price_3 NUMBER,
bulk_unit_4 NUMBER,
bulk_price_4 NUMBER,
mir_count NUMBER,
mir_amt NUMBER,
mir_end_dt VARCHAR2(10),
inst_rebate NUMBER);
TYPE tab_type IS TABLE of rec_plsql
INDEX BY BINARY_INTEGER;
PROCEDURE Main_get_price_in_effect_ole (w_calling_app IN VARCHAR2,
w_business_func IN VARCHAR2,
w_rebate_reqd IN VARCHAR2,
w_sku_id IN NUMBER,
w_group_cd IN VARCHAR2,
w_group_id IN NUMBER,
w_effective_date IN VARCHAR2,
w_plsql_tbl OUT tab_type);
END od_prc_get_price_in_effect_ole;
/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply