• Products >
• Support >
• Solutions
• Company
• Blog

• < Back
• ODBC drivers >
• JDBC drivers >
• Bridges, gateways >
• Other >
• All products

• < Back
• JDBC-ODBC Bridge
• JDBC-Access Gateway

• < Back
• ODBC-ODBC Bridge
• ODBC-JDBC Gateway
• dbExpress-ODBC Gateway
• XML-ODBC Server

• < Back
• ODBC-ODBC Join Engine
• SDK
• Consultancy
• Roadmap

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>