Node-oracledb: (Solved) Retrieve more than 100 rows in a single SELECT query

[jhereu]

Hey there,

I'm working on a Sails app which uses an Oracle database to store and retrieve info. I'm using this node-oracledb adapter with Node 0.10.35 version, after weeks of struggling with sails-oracledb and other variants...

So, the problem comes when I'm trying to execute a SELECT, only 100 rows are returned, even if I specify a higher ROWNUM value. i.e, when I execute the following statement, I get 100 rows even asking for 150. Obviously, there are far more than these in my database.

select * from some_table where ROWNUM<=150

There is few information about that fact through the Internet. I've already checked it and I know I can iterate multiple queries with a for (or BULK COLLECT). But I hope there is a chance I can retrieve more than 100 rows from a single query to the database without doing any of these things. I mean: hope its an issue of the node_module and not Oracle itself.

Thank you.

[jhereu]

Ok, sorry for that. I've already answered myself. I have cheked the https://github.com/oracle/node-oracledb/blob/master/examples/rowlimit.js file and found the adapter has a default row limit that can be set with maxRows.

Thanks anyway! :)

[cjbj]

@JHereu there is a node-oracledb project to add result set handling. See https://github.com/oracle/node-oracledb/issues/13

[cope]

I am also working on a Sails app.

I can understand that Model.find() would be limited by the maxRows setting, but why is Model.count() also limited in the same way?

Currently I have no way of finding the rowcount of a table unless I specify a maxRows value that is sure to be larger... which is not a good limitation to have, especially for fast growing tables :(

[sagiegurari]

That sounds more of the sails oracle adapter issue and not node oracle db issue.

[cope]

I was afraid of that...

I just posted an issue there a second ago...

[gkarthiks]

If it helps anyone, I gave some explanation for the workaround by @dmitrydyomin in this thread

[fmalk]

TL;DR

var oracledb = require('oracledb');
oracledb.maxRows = 0;

https://oracle.github.io/node-oracledb/doc/api.html#propdbmaxrows