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>