Exec. Oracle SP From SQL Server SP By Linked Srv

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Exec. Oracle SP From SQL Server SP By Linked Srv

Author

Topic

raahul_v
Starting Member

6 Posts

Posted - 2006-11-10 : 09:27:59

Hi,
I'am using a Linked Server from SQL Server 2000 to Oracle 10G R2.I want to trigger an Oracle Stored Procedure from one of the SQL Server Stored Procedure,so that all the execution happens in Oracle and only the values are passed to SQL Server.

Say I have both IN and OUT parameters. I m able to execute it when I don’t have OUT parameters. But when I want the return values to be stored in OUT parameters I am not able to do that. Below is sample code I used.

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'

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

Test the Procedure :

begin tran
exec CallOracleProcTest
rollback tran

Appreciate your early response.

Thanks,
Raahul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources