Solved

Getting Data back from a Oracle/SQL Stored Procedure

Posted on 2013-02-01
2
488 Views
Last Modified: 2013-02-19
I have a SQL Server, setup with a linked server setup to an Oracle database.  I am trying to pass data to the Oracle equivalent of a stored procedure and with the data I get back, pass it to an update statement.  It doesn't seem I can specify the results to the variable, @O_WGH_GET_PART_CLASS_FNC.  Any ideas?  I can execute the statement if I pass a static value.

DECLARE @l_i_parameter1 varchar(50)

DECLARE @O_WGH_GET_PART_CLASS_FNC varchar(50)

DECLARE @ssPartNum varchar(50)
DECLARE bol_cursor cursor for
select field3 from ssfields where archiveID = '2'

open bol_cursor

fetch next from bol_cursor into @ssPartNum

while @@FETCH_STATUS = 0
BEGIN
SET @l_i_parameter1 = @ssPartNum
SET @O_WGH_GET_PART_CLASS_FNC = 'TEST-1'

EXECUTE ('Begin TEST.dual(?,?); end;',
	@l_i_parameter1,
	@O_WGH_GET_PART_CLASS_FNC Output)
	AT TEST;

Update ssfields
set field15 = @O_WGH_GET_PART_CLASS_FNC
where archiveID = '2'
and field3 = @ssPartNum

fetch next from bol_cursor into @ssPartNum

END
close bol_cursor
deallocate bol_cursor

Open in new window

0
Comment
Question by:lm1189
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38845805
I'm not a sql server person but I found this example using output parameters:
http://blogs.msdn.com/b/joaquinv/archive/2008/10/23/execute-oracle-stored-procedure-in-sql-server.aspx
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38847554
as the expert suggested, you can use output parameter with the stored procedure. Or you can create a function and stores the value in a parameter using the function.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now