- This topic has 2 replies, 1 voice, and was last updated 6 years, 10 months ago by
.
-
Posts
-
-
Using Biztalk 2006 & Oracle connector.
I would like to insert a record into an Oracle table, for which I have to use the sequence MY_SEQUENCE to generate the values for column ID.
How can I tell the Orchestration to insert the value MY_SEQUENCE.NEXTVAL into the ID column ?
I tryed to specify ‘MY_SEQUENCE.NEXTVAL’ as the default value for the node ID in the schema, but I got an error saying that ID expected a numeric value (an got a string : ‘MY_SEQUENCE.NEXTVAL’.
Thanks for any help,
Attila -
Thank you for your answer.
I solved the problem by working on the Oracle side, I created (in my Oracle database) :
– a view based on my table,
– an INSTEAD OF INSERT trigger on the view. The trigger gets the next value of the sequence and inserts the data into my table.=> in the Biztalk orchestration, I insert into the view
(the value for the identity column is added by the trigger)-
I do not think you can do this from within the orchestration.
MY_SEQUENCE.NEXTVAL is a function that is evaluated by Oracle while processing the SQL.
You cannot put the text in the ID field as that is numeric.
One possibility would be an attribute on the field such as: <ID oracle:function=\”MY_SEQUENCE.NEXTVAL\”/> but the adapter would need to support this and I cannot see anywhere in the documentation where anything like this is supported.
You could use a stored procedure call.
Or adding the data to a temp table, with a trigger to populate the real table.
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.