Generally about cursors as a concept. When I run a simple select * query through jdbc I get a result set.That set is stored on the client right? But underneath the DBMS had opened a cursor.Since the database is accessed through an.stateless Http API/rest service,the connection closes and I have the result set in my client.And if the result set is stored on the client ,I can do whatever with it;go backwards, forward etc
So what is the point of a Scrollable cursor? Does it imply a stateful connection to the database and the result set being stored on the server's memory rather than on the client's? Is that a scenario that happens with explicitly opening a cursor and not with set based SQL?
Does it also imply locking rows?
In Oracle (at least up to version 18) scrollable cursors are stored at client side.
More on this you can find in the documentation (see below):
17.1 Oracle JDBC Implementation Overview for Result Set Support
Oracle JDBC Implementation for Result Set Scrollability
Because the underlying server does not support scrollable cursors, Oracle JDBC must implement scrollability in a separate layer.
It is important to be aware that this is accomplished by using a client-side memory cache to store rows of a scrollable result set.
Note:Because all rows of any scrollable result set are stored in the client-side cache, a situation, where the result set contains many rows, many columns, or very large columns, might cause the client-side Java Virtual Machine (JVM) to fail. Do not specify scrollability for a large result set.