[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Getting Data back from a Oracle/SQL Stored Procedure

Posted on 2013-02-01
2
Medium Priority
?
496 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

640 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