Calling SQL Server stored procedures from Oracle

In SQL Server, create and populate a sample table.

CREATE TABLE EMP (
   EMPNO    FLOAT(4) NOT NULL PRIMARY KEY,
   ENAME    VARCHAR(10),
   JOB      VARCHAR(9),
   MGR      FLOAT(4),
   HIREDATE DATE,
   SAL      NUMERIC(7,2),
   COMM     NUMERIC(7,2),
   DEPTNO   NUMERIC(2));

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '12-17-1980', 800, NULL, 20)
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '02-20-1981', 1600, 300, 30)
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '02-01-1981', 1250, 500, 30)
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '04-02-1981', 2975, NULL, 20)
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '09-28-1981', 1250, 1400, 30)
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '05-01-1981', 2850, NULL, 30)
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '06-09-1981', 2450, NULL, 10)
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '12-09-1982', 3000, NULL, 20)
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '11-17-1981', 5000, NULL, 10)
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '09-08-1981', 1500, 0, 30)
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '01-12-1983', 1100, NULL, 20)
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '12-03-1981', 950, NULL, 30)
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '12-03-1981', 3000, NULL, 20)
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '01-23-1982', 1300, NULL, 10)

Create a stored procedure that returns the data from this table. We will be calling this procedure from Oracle.

CREATE PROCEDURE ReturnEmployees AS
BEGIN
  SELECT * FROM EMP
END

In SQL*Plus, use the following DBMS_HS_PASSTHROUGH functions to execute the stored procedure and display the results. In the example, sqlserverlink is the name of a database link that uses DG4ODBC and Easysoft's SQL Server ODBC driver to connect to SQL Server.

$ ./sqlplus / as sysdba
SQL> set echo on;
set serveroutput on;
DECLARE
 c INTEGER;
 R1 INTEGER;
 SOUT varchar2(100);
 C1 INTEGER;
BEGIN
 c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@sqlserverlink;
 DBMS_HS_PASSTHROUGH.PARSE@sqlserverlink(c,'exec ReturnEmployees');
 LOOP
  R1 := DBMS_HS_PASSTHROUGH.FETCH_ROW@sqlserverlink (c);
  EXIT WHEN R1 = 0;
  DBMS_HS_PASSTHROUGH.GET_VALUE@sqlserverlink (c, 1, SOUT);
  DBMS_OUTPUT.PUT_LINE(SOUT);
 END LOOP;
 DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@sqlserverlink(c);
EXCEPTION
 WHEN NO_DATA_FOUND
 THEN DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@sqlserverlink(c);
END;
/
SQL>
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.

SQL>