linked server return from Oracle into table variable

• sqlgirlatty

  SSC Veteran

  Points: 250

  More actions

  December 3, 2013 at 1:55 pm

  #283223

  <<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?

• sqlgirlatty

  SSC Veteran

  Points: 250

  More actions

  December 4, 2013 at 11:15 am

  #1670806

  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.