| Author |
Message
|
| visasimbu |
 Posted: Fri Apr 07, 2017 6:35 am Post subject: Multiple row insertion in oracle database using INSERT ALL |
 |
|
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 |
|
 |
| mqjeff |
| Posted: Fri Apr 07, 2017 6:36 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
COALESCE?
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| visasimbu |
| Posted: Fri Apr 07, 2017 6:42 am Post subject: |
|
|
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 |
|
|
| mqjeff |
| Posted: Fri Apr 07, 2017 6:50 am Post subject: |
|
|
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 |
|
|
| visasimbu |
| Posted: Thu Apr 13, 2017 3:03 am Post subject: |
|
|
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 |
|
|
| mqjeff |
| Posted: Thu Apr 13, 2017 4:05 am Post subject: |
|
|
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 |
|
|
| adubya |
| Posted: Thu Apr 13, 2017 7:55 am Post subject: |
|
|
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 |
|
|
| visasimbu |
| Posted: Tue May 09, 2017 10:37 pm Post subject: |
|
|
Disciple
Joined: 06 Nov 2009
Posts: 171
|
@adubya - Thanks!. It works  |
|
| Back to top |
|
|
|
|
