| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| IIB9: Mapping Node, Oracle Sequence |
« View previous topic :: View next topic » |
| Author |
Message
|
| akil |
 Posted: Mon Apr 06, 2015 6:02 am Post subject: IIB9: Mapping Node, Oracle Sequence |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
When using a mapping node to insert data to table that needs an identity column, the current method that we follow is
# have a compute node before the mapping node, that gets the sequence value
# store the sequence in an LE variable
# use that in the mapping node.
This works for a single row insert at a time.
In case the model is an array, and the mapping node needs to insert multiple rows (for-each), this breaks,
Is there a way to fetch sequence.curval from dual in the mapping node?
_________________
Regards |
|
| Back to top |
|
 |
| martinb |
| Posted: Thu Apr 09, 2015 2:06 am Post subject: Re: IIB9: Mapping Node, Oracle Sequence |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
| akil wrote: |
...
In case the model is an array, and the mapping node needs to insert multiple rows (for-each), this breaks, |
In what way does it "break"?
| akil wrote: |
Is there a way to fetch sequence.curval from dual in the mapping node? |
Unclear what you want here?
Meanwhile consider these points
In the For Each transform, you can use the $VarName-index variable to provide the index of the input array that the Graphical Data Mapping editor is processing. Perhaps this could be added to the sequence value?.
The Join transform can be used to process two or more arrays. You can provide a simple Join expression to select matching index entries from each array to be passed into the nested mapping. Perhaps this could be used to join a insert data values array with a sequence values array? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Apr 09, 2015 3:18 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
IMHO, you would be better off doing the insert via a stored procedure.
That way you can be sure of getting the right sequence number for the insert. also if this is done inside a DB transaction, you can guarantee that no other insert will try to use the same sequence number value as you are.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| akil |
| Posted: Thu Apr 09, 2015 9:33 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
When the database table needs an identity column , we use a sequence In oracle ( as it does not have an auto increment option ).
So an insert flow, is something like the following
....compute ( fetch from sequence , store in LE ) --- ( mapping node, inserts into table, using the value from the LE .
When the mapping mode has to run a for each, then
.... compute ( fetch from sequence x times , store array in LE ) --- ( mapping node, inserts ,....)
When the mapping node has to run multiple for each...it just keeps getting messy ..that's what I meant by break.
It would have been so much more easier, if I could select the sequence from within the mapping node, avoiding the Compute / LE..is that doable?
Using a local index does not guarantee uniqueness across multiple instances.
Oracle Procedures can't be called from a mapping node
Sequences aren't subject to the database transaction, so that is not a concern, it's the clumsiness of fetching the values in a compute.
_________________
Regards |
|
| Back to top |
|
|
| aleksk |
| Posted: Tue Nov 10, 2015 2:55 am Post subject: |
|
|
Newbie
Joined: 02 Jul 2014
Posts: 4
|
If you don't need the ID for anything else besides insert, you can use BI trigger on the table and fetch the next value of the sequence in it. For example:
| Code: |
TRIGGER name BEFORE INSERT
ON table
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
IF :NEW.ID IS NULL THEN SELECT seq_name.NEXTVAL INTO :NEW.ID FROM DUAL; END IF;
END; |
|
|
| Back to top |
|
|
| maurito |
| Posted: Tue Nov 10, 2015 6:30 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| akil wrote: |
| When the database table needs an identity column . |
use a trigger.
| Code: |
create or replace trigger FOO_trg
before insert on FOO
for each row
begin
select FOO_seq.nextval into :new.x from dual;
end;
/
|
|
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
