Oracle Insert Row limit of 256 rows (no pagination)

[Anonymous]

Hi All,

The Oracle 'Insert Row' action has a row limit of 256. This action doesn't have a 'Pagination' option. How can I load more records in the database?

Thanks!

Marco

[yashag2255]

Hi @Anonymous 

Can you please share a screenshot of your flow? You could initialise an integer variable that holds the value of the number of rows to be insterted and then use a do unitl control to add rows until this value is zero. Take a look at the flow configuration below:

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

Ok if I need a 'do until' I need to:

1. Add a row number to the data set that I would like to load in the database

2. Load the first 256 rows in the database

3. Update a variable with the last row number that I've loaded

4. Loop over again and start with the last row number + 1

Something like this?

This is in my oppinion a horrible solution. It is not possible to load the whole data set at once?

Marco

[yashag2255]

Hey @Anonymous 

1. Yes. You just have to add the number of rows to be added once in the variable. 

2. The do until loop will do this. 

3. The do until loop does this on the increment variable action

4. It will be the last row number -1 until the total count goes to zero. 

It is a way to check the number of rows that were intended to be created in the Oracle DB and configure the count. You might add a small delay or check at the 256th count. 

This is not the only way to do it and there can be more horrible or better solutions to this but since the entire dataset cannot be loaded at once due to the thresholds, you will have to find a balanced solution to meet your requirements. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

And how can I count the number of rows in an Excel file? Ive uploaded a screenshot.

[yashag2255]

Hey @Anonymous 

Can you do one thing, I am guessing its not the oracle db connector that is having the limitation. Can you add a variable of type integer after the list rows action and in the expression editor type length() and place the cursor between the () and from the dynamic selector choose the excel list values and run the flow? Add a terminate action just after this variable so that rows are not created on oracle db as we will just be testing this one. 

[yashag2255]

Hey @Anonymous 

Hang on! I just checked and it is the excel connector at issue:

Add the number of rows to the top count here and I believe you should be good. 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-bacao-msft · Accepted solution]

Hi @Anonymous ,

As far as I know, there seems to be no such limitation in Oracle connector. Please check Online doc for more details on this connector:

https://docs.microsoft.com/en-us/connectors/oracle/#known-issues-and-limitations

According to the screenshot you provided, it is because List rows present in a table action only returns 256 rows, so the action in Apply to each is only executed 256 times.

You could set List rows present in a table action to adjust the Threshold to the expected value.

Image reference:

Hope it helps.

Best Regards,