Home » SQL & PL/SQL » SQL & PL/SQL » call sql server procedure from oracle (Oracle 12c 2)
call sql server procedure from oracle [message #683864] Fri, 26 February 2021 06:21
sss111ind
Messages: 623
Registered: April 2012
Location: India
Senior Member

Hi All,

I want to call sql server procedure from oracle to get the data. I am using the below example to call it.
But I am getting the below error because there are 2 parameters in sql server procedure and 3 parameters in oracle side.
Error(33,3): PLS-00306: wrong number or types of arguments in call to 'REFCURPROC'
However this example is documented in oracle docs. Please suggest what is going wrong with this or any parameters need to be enabled for this.
For small result set we cab achieve through dbms_hs_passthrough package, if thousands of required then we need to call procedure


--sql server procedure with having 2 parameters
create procedure REFCURPROC (@arg1 varchar(255), @arg2 varchar(255) output)
as
select @arg2 = @arg1
select * from EMP
select * from DEPT
go


--oracle call to sql server procedure which calls sql server procedure with 3 parameters
create table outargs (outarg varchar2(255), retval number);

create or replace package rcpackage is
  type RCTYPE is ref cursor;
end rcpackage;
/
declare
  rc1 rcpackage.rctype;
  rec1 loc_emp%rowtype;
  rc2 rcpackage.rctype;
  rec2 loc_dept%rowtype;
  rc3 rcpackage.rctype;
  rec3 outargs%rowtype;
  out_arg varchar2(255);

begin

  -- Execute procedure
  out_arg := null;
  refcurproc@MSQL('Hello World', out_arg, rc1);

  -- Fetch 20 rows from the remote emp table and insert them into loc_emp
  for i in 1 .. 20 loop
    fetch rc1 into rec1;
    insert into loc_emp (rec1.empno, rec1.ename, rec1.job,
    rec1.mgr, rec1.hiredate, rec1.sal, rec1.comm, rec1.deptno);
  end loop;

  -- Close ref cursor
  close rc1;

  -- Get the next result set returned by the stored procedure
  rc2 := dbms_hs_result_set.get_next_result_set@MSQL;

  -- Fetch 5 rows from the remote dept table and insert them into loc_dept
  for i in 1 .. 5 loop
    fetch rc2 into rec2;
    insert into loc_dept values (rec2.deptno, rec2.dname, rec2.loc);
  end loop;

  --Close ref cursor
  close rc2;

  -- Get the output arguments from the remote stored procedure
  -- Since we are in sequential mode, they will be returned in the
  -- form of a result set
  rc3 := dbms_hs_result_set.get_next_result_set@MSQL;

  -- Fetch them and insert them into the outargs table
  fetch rc3 into rec3;
  insert into outargs (rec3.outarg, rec3.retval);

  -- Close ref cursor
  close rc3;

end;
/
Thanks
Previous Topic: Generate month wise data using SQL (Sort data based on month)
Next Topic: Auto Sequence number for specific subinventory transactions
Goto Forum:
  


Current Time: Sat May 08 21:37:01 CDT 2021