Solved

Getting Data back from a Oracle/SQL Stored Procedure

Posted on 2013-02-01
2
490 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

786 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