December 3, 2013 at 1:55 pm
<<previously posted to wrong forum>
I'm trying to return the contents of a select statement on Oracle into a table variable in SQL Server stored procedure. I can get the query to return a single value into multiple variables, but getting a result set to return to a table variable has been tricky.
DECLARE @vlparent varchar(10)
,@sqlstring nvarchar(4000);
DECLARE @rset TABLE
(ln varchar(64)
,fn varchar(64)
,bd varchar(10)
,vlname varchar(10)
);
SET @sqlstring = N' DECLARE CURSOR mb IS SELECT CUST_LASTNAME, CUST_FIRSTNAME,CUST_DOB,VOL_NAME
FROM ORASCHEMA.TABNAME WHERE VOL_PARENT = ?;
TYPE mb_array IS TABLE OF mb%ROWTYPE;
cur_array mb_array;
BEGIN OPEN mb;
LOOP
FETCH mb BULK COLLECT INTO cur_array LIMIT 1000;
EXIT WHEN mb%NOTFOUND;
END LOOP;
CLOSE mb;
END;'
EXEC(@sqlstring,@vlparent,@rset OUTPUT)AT ORA_LINKSERVER
With this method I receive an error about undefined scalar value. With a INSERT INTO ..EXEC receive an error about MSDTC not recognizing my server. Any ideas?
December 4, 2013 at 11:15 am
Went about it in a completely different fashion and solved it.
Note, EXEC <stmt> AT far_server didn't yield what was needed. Had to make dynamic sql with OPENQUERY.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply