ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Multiple row insertion in oracle database using INSERT ALL

Post new topic  Reply to topic
 Multiple row insertion in oracle database using INSERT ALL « View previous topic :: View next topic » 
Author Message
visasimbu
PostPosted: Fri Apr 07, 2017 6:35 am    Post subject: Multiple row insertion in oracle database using INSERT ALL Reply with quote

Disciple

Joined: 06 Nov 2009
Posts: 171

My aim is to insert multiple rows in single table which is resides in oracle database.

Hence I have used esql statement like below.

Code:
SET ItemCount = 1;
DECLARE itemQuery CHARACTER '';
DECLARE insertValuesItem CHARACTER 'INTO SOME.ITEMTBL(ID,SQU,PRICE,QTY)
                         VALUES(?,?,?,?)';
         
       ItemLine : WHILE LASTMOVE(lineRef) DO
         SET itemQuery = itemQuery || insertValuesItem;
          SET lineItem.Row.col[ItemCount]         = lineRef.Id;
          SET lineItem.Row.col[ItemCount+1]      = lineRef.SQU;
         SET lineItem.Row.col[ItemCount+2]      = lineRef.PRICE;
         SET lineItem.Row.col[ItemCount+3]      = lineRef.QTY;
         SET  ItemCount =  ItemCount+4; --This will help to save next row.
          MOVE lineRef NEXTSIBLING REPEAT NAME;
       END WHILE ItemLine;
      
       .....
      
       PASSTHRU('INSERT ALL ' || itemQuery || 'SELECT 1 FROM DUAL', lineItem.Row.col[]);   


lineItem array will hold all the row information.

Question is if any of the value comes from the input line item is null. Then array is not happy to store null values which leads to consecutive element issue like 'array subscript error'. Since the incremental is not working as expected.

Any suggestion to resolve this issue ?

Or else i have to loop insert statement which will be costly than 'INSERT ALL'.
Back to top
View user's profile Send private message Send e-mail
mqjeff
PostPosted: Fri Apr 07, 2017 6:36 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

COALESCE?
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
visasimbu
PostPosted: Fri Apr 07, 2017 6:42 am    Post subject: Reply with quote

Disciple

Joined: 06 Nov 2009
Posts: 171

COALESCE can help us to insert something like empty string ''.

SET lineItem.Row.col[ItemCount] = COALESCE( lineRef.Id, '');

But like to insert NULL into database Column. Do we have any other suggestion ?
Back to top
View user's profile Send private message Send e-mail
mqjeff
PostPosted: Fri Apr 07, 2017 6:50 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

visasimbu wrote:
COALESCE can help us to insert something like empty string ''.

SET lineItem.Row.col[ItemCount] = COALESCE( lineRef.Id, '');

But like to insert NULL into database Column.


Ok. What does the database column expect as a null? just an empty field - like "fieldx,,filedx+2" ? Or "fiedx,0,fieldx+2"... ?

That's what coalesce will get you.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
visasimbu
PostPosted: Thu Apr 13, 2017 3:03 am    Post subject: Reply with quote

Disciple

Joined: 06 Nov 2009
Posts: 171

@mqjeff - Apologies for late reply.

I like to insert NULL into field1.. basically need something like this - "fieldx,NULL,filedx+2"
Back to top
View user's profile Send private message Send e-mail
mqjeff
PostPosted: Thu Apr 13, 2017 4:05 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

Right.

I did undesrtand that.

You need to figure out what the database expects that "NULL" means - maybe it just means the string "NULL".

Then you use the COALESCE function to assign that null value ("NULL"?) to the field when it is not defnied.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
adubya
PostPosted: Thu Apr 13, 2017 7:55 am    Post subject: Reply with quote

Partisan

Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK

Try this:

Code:


Code:
SET ItemCount = 1;
DECLARE itemQuery CHARACTER '';
DECLARE insertValuesItem CHARACTER 'INTO SOME.ITEMTBL(ID,SQU,PRICE,QTY)
                         VALUES(?,?,?,?)';
         
       ItemLine : WHILE LASTMOVE(lineRef) DO
          SET itemQuery = itemQuery || insertValuesItem;
          SET lineItem.Row.col[ItemCount]     VALUE    = lineRef.Id;
          SET lineItem.Row.col[ItemCount+1] VALUE    = lineRef.SQU;
          SET lineItem.Row.col[ItemCount+2] VALUE    = lineRef.PRICE;
          SET lineItem.Row.col[ItemCount+3] VALUE    = lineRef.QTY;
          SET  ItemCount =  ItemCount+4; --This will help to save next row.
          MOVE lineRef NEXTSIBLING REPEAT NAME;
       END WHILE ItemLine;
       
       .....
       
       PASSTHRU('INSERT ALL ' || itemQuery || 'SELECT 1 FROM DUAL', lineItem.Row.col[]);


i.e add the VALUE clause to your column assignments. This will preserve the NULL value rather than delete your left hand tree element.
_________________
Independent Middleware Consultant
andy@knownentity.com
Back to top
View user's profile Send private message Send e-mail
visasimbu
PostPosted: Tue May 09, 2017 10:37 pm    Post subject: Reply with quote

Disciple

Joined: 06 Nov 2009
Posts: 171

@adubya - Thanks!. It works
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Multiple row insertion in oracle database using INSERT ALL
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.