Unable to pass internal table data back to calling procedure

Question

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!

Answer 1

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.