Call ORACLE store procedure from MS Sql server via openquery

Background:

 

Before  deploy application in Windows 64 bits OS,  normally used  “MS provider for  ORACLE OLE DB 32 bits” in all the 32 bit OS environments. After move to Windows OS to 64 bits for MS SQL Server 2008 on wards. however, Microsoft had stopped to release the 64 bits provides for ORACLE OLE DB, And only ORACLE’s 64 bits provider for OLE DB is available to connect to ORACLE database via Link Server.

The functions and interfaces between ORACLE providers and MS providers is not fully compatible, Hence SQL server codes and ORACLE store procedure codes need be changed in order can use new ORACLE provider performs the remote calling and pass back the collection data in table/cursor type.

Existing calling interface:

 

1. Existing method in SQL Sever vis MS provider to call ORACLE store procedure.

In ORACLE DB , one interface — store procedure be created for MS SQL Server to call and  return the data from ORACLE in record/table type,

Interface to between SQL Server and ORACLE is used  “IN” and “OUT” parameter, the “OUT” is on the record/table type.

create or replace  package pkg_sqlserver as
procedure pr_sqlserver_execute(p_filter_v in varchar2, p_output_v in varchar2, p_status_t out typ_return_tbl, p_ret_code_t out typ_return_tbl);

2. In SQL Server, under MS provider for ORACLE OLE DB (32 bits)

select * from openquery(oradb, '{ call pkg_sqlserver. pr_sqlserver_execute\
''DATA_LVL=IDX|RETURN_TYPE=1|IDX_PRFM_TYPE=1|DATA_FM=20090101|DATA_TO=20090131'',
{resultset 1, p_status_t},{resultset 1, p_ret_code_t})  }' )

ISSUE:

When application deployed  into Windows 64 bits,  No MS provider (64 bits) for ORACLE OLE DB is available,  SQL Server link server only able to call ORACLE provider – 64 bits for OLE DB,  however the method to run the openquery as listed above in MS provider does not be supported in ORACLE provide-64 bits , and when switch to the ORACLE provide, the error will happen as in below:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "{ call pkg_sqlserver. pr_sqlserver_execute
('DATA_FM=20090101|DATA_TO=20080331|RETURN_TYPE=1|IDX_PRFM_TYPE=2|IDX_CODE=ERDUSMABPHARMM|IDX_CODE=FIAGG0107|IDX_CODE=GICEQXSGNIWT|IDX_CODE=GICFIAGG_C|IDX_CODE=SBG5|IDX_CODE=SBG59612|IDX_CODE=YGSCFIX|DATA_LVL=IDX|DATA_FREQ=D',
'DATATTR=IDX_DT|DATATTR=IDX_CODE',
{resultset 1, p_status_t},{resultset 1, p_ret_code_t})  }".

The OLE DB provider “OraOLEDB.Oracle” for linked server “gist” indicates that either the object has no columns or the current user does not have permissions on that object.

SOLUTION:

If application is conformable to deploy MS 32 bits providers for ORACLE OLE DB, then nothing need be changed in ORACLE and SQL Server.

If application need 64 bits provider and has to be switch to ORACLE provider, then following solution for ORACLE procedure and SQL Server openquery can be applied in application coding.

Use “PIPE ROW” function in PL/SQL to pass over the record set from ORACLE to SQL Server, function with return record type with PIPELINED.

In ORACLE, the store procedure(function) to return table/array need be wrapped and transfer back thru the “pipe” .

Simple Example  :

In ORACLE database which store the source of data:

(1). create the EMP table with (empno, ename, job, mgr, hiredate, sal, comm, deptno)

create table emp
(
empno number(4),
ename VARCHAR2 (10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);

(2). create the user type in ORACLE DB for defined the record data.

create or replace type emp_type as object
(
empno number(4),
ename VARCHAR2 (10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);
/

create or replace type pemp_type as table of emp_type;
/

(3). create the wrapped function and return value by “PIPELINED”. The function will pass into the dynamic “where” clause for dynamic “SQL”.

create or replace FUNCTION select_emp(i_query in sys_refcursor) return pemp_type PIPELINED AS
t_emp emp%rowtype;
BEGIN
loop
fetch i_query into t_emp;
exit when (i_query%notfound);
pipe row (emp_type(t_emp.empno,t_emp.ename,t_emp.job,t_emp.mgr, t_emp.hiredate,t_emp.sal,t_emp.comm,t_emp.deptno));
end loop;
close i_query;
return;
EXCEPTION
when others then
dbms_output.put_line('ERROR INSIDE PIPELINE FUNCTION');
if i_query%isopen then
close i_query;
end if;
END select_emp;
/

(4), Create normal store procedure for application .

CREATE or replace PACKAGE emp_data AS
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
function get_emp_cv (v_deptno INT) return empcurtyp;
END emp_data;
/

CREATE or replace PACKAGE BODY emp_data AS
function get_emp_cv (v_where varchar2) return empcurtyp
IS
emp_cv sys_refcursor;
sql_str varchar2(200);
BEGIN
sql_str := 'SELECT * FROM emp WHERE' || v_where;
OPEN emp_cv FOR sql_str;
return emp_cv;
END;
END emp_data;
/

In SQL Server :

(5), Calling template from SQL Server

In SQL Server: create one link server to the ORACLE DB (LINKSERVERNAME) with the name.

select * from openquery (LINKSERVERNAME,
'select * from TABLE(select_emp(emp_data.get_emp_cv(10)))')

LINKSERVERNAME build on ORACLE Provider 64 bits