Unable to pass internal table data back to calling procedure

Balaji Ramnath 1 Reputation point
2021-06-19T18:27:53.783+00:00

Data is fetched into the below table entries using a cursor and when I display the table entries I can see the data to be populated correctly
@VTSAMPLETBL$MYTIMESTAMP,
@VTSAMPLETBL$LOT_NUMBER,
@VTSAMPLETBL$CONTAINER_NO,
@VTSAMPLETBL$PRPRTY_NAME,
@VTSAMPLETBL$UNITS,
@VTSAMPLETBL$RSLT_VALUE,
@VTSAMPLETBL$SEQ,
@VTSAMPLETBL$OFFSPEC_CHK

@VTSAMPLTBL is defined as collectionindexint OUTPUT and this field is being passed back to the calling procedure

The issue is the below statement does not the store the values onto @VTSAMPLETBL and hence the called procedure does not get the values passed to it

SET @VTSAMPLETBL = @VTSAMPLETBL.SetRecord(@J,@VTSAMPLETBL.GetOrCreateRecord(@J).AssignData(mig.fn_bulk_collect2Record(
(
SELECT
@VTSAMPLETBL$MYTIMESTAMP AS MYTIMESTAMP,
@VTSAMPLETBL$LOT_NUMBER AS LOT_NUMBER,
@VTSAMPLETBL$CONTAINER_NO AS CONTAINER_NO,
@VTSAMPLETBL$PRPRTY_NAME AS PRPRTY_NAME,
@VTSAMPLETBL$UNITS AS UNITS,
@VTSAMPLETBL$RSLT_VALUE AS RSLT_VALUE,
@VTSAMPLETBL$SEQ AS SEQ,
@VTSAMPLETBL$OFFSPEC_CHK AS OFFSPEC_CHK
FOR XML PATH
))))

Need expertise advise to fix this issue

Thanks in Advance!

SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
488 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Alexander Ivanov 491 Reputation points
    2021-06-22T18:57:17.413+00:00

    Hello,

    It is hard to tell what is exactly wrong with your scenario as you didn't provide the entire SQL you have there, but here's the example how to add new record element into the collection indexed by INT:

    DECLARE  
        @CollectionIndexInt$TYPE varchar(max) =  
            ' TABLE INDEX BY INT OF ( RECORD ( MGRID INT , MGRNAME STRING , HIREDATE DATETIME ) )'  
      
    DECLARE  
        @Mgr_rec$mgrid int,  
        @Mgr_rec$mgrname varchar(40),  
        @Mgr_rec$hiredate datetime2(0),  
        @Mgr_table_rec ssma_oracle.CollectionIndexInt =  
            ssma_oracle.CollectionIndexInt::[Null].SetType(@CollectionIndexInt$TYPE)  
      
    SET @mgr_rec$mgrid = 1  
    SET @mgr_rec$mgrname = 'Mike'  
    SET @mgr_rec$hiredate = sysdatetime()  
      
    SET @Mgr_table_rec =  
        @Mgr_table_rec.SetRecord(  
            1,  
            @Mgr_table_rec.GetOrCreateRecord(1).AssignData(  
                ssma_oracle.fn_bulk_collect2Record((  
                    SELECT  
                        @Mgr_rec$mgrid AS mgrid,  
                        @Mgr_rec$mgrname AS mgrname,  
                        @Mgr_rec$hiredate AS hiredate  
                    FOR XML PATH  
                ))  
            )  
        )  
      
    SELECT CAST(@Mgr_table_rec AS NVARCHAR(MAX))  
    

    You can run it and see if everything works in your environment. This is a modified example from our docs, so depending on the situation, you may not need to set the elements one by one, but rather AssignData on the entire collection, like demonstrated in the docs using fn_bulk_collect2CollectionComplex.

    Hope this helps.

    Regards,
    Alex.

    0 comments No comments