SSMA - Convert oracle to Sql server 2008

Last Post 18 Nov 2011 05:03 AM by rm. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
Neeraj
New Member
New Member

--
17 Nov 2011 02:12 AM
Hi,
I have use this code to declare collection index

DECLARE
         @CollectionIndexInt$TYPE varchar(max) = ' TABLE OF ( RECORD ( UCID DECIMAL , C_TIME DATETIME , KEY DECIMAL , KEY_SET DECIMAL , VALUE DECIMAL ) )',
         @CollectionIndexInt$TYPE$2 varchar(max) = ' TABLE OF ( RECORD ( KEY_SET DECIMAL , VALUE DECIMAL ) )'

      DECLARE
         @main_sets_item dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE),
         @main_item dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE$3),



Now I want to use this like

FETCH @c_sysref
INTO
@ucid,
@c_time,
@key,
@key_set,
@value

IF @@FETCH_STATUS = -1
BREAK

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'VALUE', @value))

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'KEY_SET', @key_set))

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'KEY', @key))

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDatetime(N'C_TIME', @c_time))

SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'UCID', @ucid))


DECLARE
@key_set$2 float(53)

SELECT @key_set$2 = sysdb.ssma_oracle.db_get_next_sequence_value(N'Tific', N'DEV_LOG', N'LOG_SAC_SETS_SEQ')

SET @main_sets_item = @main_sets_item.SetRecord(@i, @main_sets_item.GetOrCreateRecord(@i).SetDecimal(N'KEY_SET', @key_set$2))

SET @main_item = @main_item.Extend()

SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDecimal(N'UCID', @main_sets_item.GetRecord(@i).GetDecimal(N'UCID')))

SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDatetime(N'C_TIME', @main_sets_item.GetRecord(@i).GetDatetime(N'C_TIME')))

SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDecimal(N'KEY', @main_sets_item.GetRecord(@i).GetDecimal(N'KEY')))

SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDecimal(N'KEY_SET', @main_sets_item.GetRecord(@i).GetDecimal(N'KEY_SET')))


But when i use this like

WHILE @ii <= @main_item.COUNT
INSERT INTO log_sac_main VALUES  @main_item.GetRecord(@ii)

This gives Error,

Please help me on this
russellb
New Member
New Member

--
17 Nov 2011 08:19 AM
Neeraj
New Member
New Member

--
17 Nov 2011 09:13 PM
Thanks russellb,

actually i am not able to use @main_item var to insert statement. when i use like this

INSERT INTO log_sac_main VALUES @main_item.GetRecord(@ii)

this gives error 'incorrect syntax error'. My problem is how i use @main_item var for insert record.

Thanks
rm
New Member
New Member

--
18 Nov 2011 05:03 AM
Is @main_item a schema in the db? How many columns in th table? Does function reture all columns needed? By the way, syntax of insert is insert into table ... values (..., ..., ...)



Acceptable Use Policy
---