Calling an Oracle Stored Procedure from a Sql Server 2000 Stored Procedure using a Linked Server

I had to to this a while back -> Calling an Oracle stored procedure from a Sql Server stored procedure. The Oracle stored procedure accepted a number of parameters from the Sql Server stored procedure. The Oracle stored procedure would then execute and pass back a result set to the Sql Server calling procedure. A linked server was used in Sql Server to call the Oracle stored procedure. This is just to save anybody the pain in case they need to set this up.

This is a step by step example on how to set up the server where the Oracle database is installed
and the server where the Sql Server is installed. It also provides a test Sql Server stored proc calling an Oracle stored proc with parameters, with a result set being passed back to Sql Server.

The following are the steps necessary to call an Oracle Stored Procedure from Sql Server.

The test was done with two separate computers as below.

 

Pre Set Up:

 

Machine 1)

Windows XP , with Oracle Standard Server 8.1.7 installed.

Note: Other versions of Oracle may work.

 

 

Machine 2)

Windows XP , with Sql Server 2000 installed.

 

 

Machine 1) Oracle

 

1) Make sure the Distributed Transaction Coordinator Service is running.

2) Run the following Below Oracle Packages and Procs into the Oracle Instance.

Note :The Oracle stored procedure that Sql Server calls must be within a package.

IN parameters can be passed into the Oracle Stored Procedure, but any results

should be passed back in a ReturnVal OUT Table type parameter.

 

-----------------------------------------------------------------------

 

CREATE OR REPLACE PACKAGE CallFromSqlServerPackage

AS

TYPE SqlReturnTbl IS TABLE OF Varchar(500)

/* The index is important, otherwise {resultset} doesn't work. */

INDEX BY BINARY_INTEGER;

PROCEDURE OracleProc

(

Param1 IN varchar2,

Param2 IN varchar2,

Param3 IN varchar2,

Param4 IN varchar2,

ReturnVal OUT SqlReturnTbl

);

END CallFromSqlServerPackage;

/

 

CREATE OR REPLACE PACKAGE BODY CallFromSqlServerPackage

AS

PROCEDURE OracleProc

(

Param1 IN varchar2,

Param2 IN varchar2,

Param3 IN varchar2,

Param4 IN varchar2,

ReturnVal OUT SqlReturnTbl

)

IS

Begin

   /* From this package would call out to the Oracle Proc that would do the actual work */

   /* IN and OUT type parameters could be used to pass data out and get data back in from this Oracle SP.*/

   /* For example the call to the other proc would look like the below */

   /* SomeRealSP( Param1, Param2, Param3, Param4 ); */     

   /* From the results from the above proc, place into the TABLE that will be returned, */

   /* back to the calling Sql Server Proc. All data passed back would have to be in a Character format */

   /* If necessary, the Sql Server Stored procedure, would have to convert the data into Integers/ Datetimes etc. */

   /* The Sql Server Proc, would have to know the positions of the data in the rows returned back from Oracle. */

   ReturnVal( 1 ) := 'Some Character Data';  

   ReturnVal( 2 ) := '340';  

   ReturnVal( 3 ) := '100.22';     

   ReturnVal( 4 ) := 'Dec 22 2004';

     

  

   /* Below was test to see if doing a Rollback on the Sql Server Side, would roll this back */

   /* If a Rollback was issued in the Sql Server proc, then the below statement was rolled back */

   /* Insert into SCOTT.ACCOUNT values (3000,5); */

 

           

  

End OracleProc;

End CallFromSqlServerPackage;

/

 

 

Machine 2) Sql Server

 

The following Steps were followed as detailed below:

(How to set up and troubleshoot a linked server to Oracle in SQL Server) :

http://support.microsoft.com/kb/280106

 

1)  The Oracle Client Software (8.1.7) was installed.

2) Install MDAC version 8, as outlined in the KB article

http://www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c&DisplayLang=en

3) Modify the registry as outlined in the KB article.

4) Restart the Server.

5) After the server is re-started make sure that the Distributed Transaction Coordinator Service is running.

6) The PATH System variable was edited, so the Sql Server Path, preceded the Oracle Path(s), see below:

 

The C:\Program Files\Microsoft SQL Server\80\Tools\BINN;C:\oracle\ora81\bin;C:\Program Files\Oracle\jre\1.1.7\bin;

 

7) A Service Naming Entry was created as below, to connect to the proper Oracle Instance on Machine 1)

8) The following statement was run in Query Analyzer, to set up the correct linked server:

 

Use master

go

EXEC sp_addlinkedserver   'Ora817Link',  'Oracle',  'MSDAORA',  'test2'

go

 

Note: that Ora817Link, is the name of the Linked Server Entry, test2, is the name of the Service Naming entry that was created above in Step 7)

 

 

9) Create the Credentials that will be used  by the Linked Server to connect to the Oracle Server

 

Use master

go

/* Below will set the Credentials  */

EXEC sp_addlinkedsrvlogin 'Ora817Link', 'FALSE',NULL, 'scott', 'tiger'

Go

 

 

10) Run the following stored Procedure into Sql  Server

 

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CallOracleProcTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[CallOracleProcTest]

GO

 

 

CREATE Proc CallOracleProcTest

 

As

 

declare @createdTran tinyint

 

Set @createdTran = 0

 

-- Note : This must be set in order for Transactions to be Started and Rollbacked properly

SET XACT_ABORT ON

 

-- Starting a Transaction, Note: this is optional.

-- This technique, just describes how to test to see

-- if a transaction has already been started, by the caller

-- If not, then start the transaction.

If @@trancount = 0

Begin

 Begin Transaction

 Set @createdTran = 1

End

 

-- Use the below temp table, to capture the results returned back from the call to the Oracle Proc.

Create table #test (indicator int identity(1,1),

                Oracle_Result varchar(500))

 

 

 

 

Declare @parma1ForOracle varchar(100),

      @parma2ForOracle varchar(100),

      @parma3ForOracle varchar(100),     

      @parma4ForOracle varchar(100)

 

Set @parma1ForOracle = '1'

set @parma2ForOracle = '100.2'

set @parma3ForOracle = 'This is info for Oracle test'

set @parma4ForOracle = 'Dec 12 2004'

 

/* NOTE: Below would build the Dynamic Sql Statement. Parameters passed to Oracle are built into the Dynamic Sql*/

/* Results from Call are placed into a Temp table. NOTE: That the position of the passed back info, would have

   to be know by this proc*/

 

/* Note that resultset 25 , signifies that <= 25 rows can be returned back from the Oracle Stored proc*/

 

Declare @OracleCall varchar(8000)

set @OracleCall = 'Insert into #test(Oracle_Result) SELECT * FROM OPENQUERY(ORA817Link , ''{CALL SCOTT.CallFromSqlServerPackage.OracleProc('

set @OracleCall = @OracleCall + ''''''+ @parma1ForOracle + ''''''

set @OracleCall = @OracleCall + ',' + '''''' +  @parma2ForOracle + ''''''

set @OracleCall = @OracleCall + ',' + '''''' +  @parma3ForOracle + ''''''

set @OracleCall = @OracleCall + ',' + '''''' +  @parma4ForOracle + ''''''

set @OracleCall = @OracleCall + ',{resultset 25, ReturnVal})}'')'

 

-- Below is where actually call the Oracle stored procedure.

exec (@OracleCall)

if @@error <> 0  goto err_handler

 

 

-- Now get the info passed back from the oracle stored procedure.

-- Note: Must know the row number of the data passed back from the oracle stored procedure,

-- and its type.

 

Declare @parma1FromOracle varchar(100),

      @parma2FromOracle integer,

      @parma3FromOracle real,

      @parma4FromOracle datetime

 

Select @parma1FromOracle = Oracle_Result

From #test

Where indicator = 1

if @@error <> 0  goto err_handler

 

Select @parma2FromOracle = Convert(int,Oracle_Result)

From #test

Where indicator = 2

if @@error <> 0  goto err_handler

 

Select @parma3FromOracle = Convert(real,Oracle_Result)

From #test

Where indicator = 3

if @@error <> 0  goto err_handler

 

Select @parma4FromOracle = Convert(datetime,Oracle_Result)

From #test

Where indicator = 4

if @@error <> 0  goto err_handler

 

print 'Result of @parma1FromOracle = ' + ltrim(rtrim(@parma1FromOracle))

print 'Result of @parma2FromOracle = ' + ltrim(rtrim(str(@parma2FromOracle)))

print 'Result of @parma3FromOracle = ' + ltrim(rtrim(Convert(varchar(30),@parma3FromOracle)))

print 'Result of @parma4FromOracle = ' + ltrim(rtrim(Convert(varchar(30),@parma4FromOracle)))

 

 

if @createdTran = 1 and @@trancount > 0

Begin

  Commit Transaction

End

SET XACT_ABORT OFF

return 0

 

err_handler:

 

print 'Error in proc'

if @createdTran = 1 and @@trancount > 0

Begin

  Rollback Transaction

End

SET XACT_ABORT OFF

Return - 1

 

 

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

 

 

11)  Test the Procedure :

begin tran

exec CallOracleProcTest

rollback tran