Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting Data back from a Oracle/SQL Stored Procedure

Posted on 2013-02-01
2
Medium Priority
?
494 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 77

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to recover a database from a user managed backup
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

670 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