Solved

Getting Data back from a Oracle/SQL Stored Procedure

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

630 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