Expose JDBC fetch size (defaultRowPrefetch) for the Oracle connector

1

Hi Dmitry and team,

I'd like to request the ability to configure the JDBC fetch size (row prefetch) for the Oracle connector.

Use case

I'm extracting base tables from an Oracle OPERA (hospitality PMS) database to load into Snowflake. One table has ~3.3M rows and 294 columns. A full import via Import from database took close to 2 hours.

The bottleneck appears to be the default JDBC fetch size. Oracle's JDBC driver defaults to a very small prefetch (10 rows), which means a huge number of network round-trips for a table this size. Raising the fetch size to a few thousand rows typically cuts extraction time dramatically.

What I tried

I added defaultRowPrefetch under the connector's Custom properties tab, but the connection test fails with:

'DEFAULTROWPREFETCH' is an invalid connection string attribute

So it looks like Custom properties are being passed as Oracle connection-string attributes, not as JDBC driver/statement properties. defaultRowPrefetch is a driver-level property (oracle.jdbc.defaultRowPrefetch), so it's rejected on that path. I couldn't find a fetch size field anywhere else in the connector config (Connection / Schemas / Custom properties / Advanced) or in the global Settings.

Request

Either of these would solve it:

  1. A "Fetch size" / "Rows to fetch" field in the Oracle connector config, applied via statement.setFetchSize(n) on the import statement, or
  2. Allow Custom properties to be passed as JDBC driver properties (not only connection-string attributes), so defaultRowPrefetch works.

Option 1 would be the cleanest for users.

Environment

  • EasyMorph Professional (Windows, running on Amazon WorkSpaces)
  • Oracle connector, Basic connection, SID-based
  • Oracle 19c (OPERA)

Thanks — this would be a big help for anyone moving large Oracle tables through EasyMorph. Happy to test a build if useful.

Albert

2

Hi Albert,

The native Oracle connector doesn't use a JDBC driver internally. It uses the ODP.NET Managed driver.

Which EasyMorph version are you using?

3

Hi Andrew, thanks for the quick reply!

I'm on EasyMorph 5.9.8.18 (Professional, Windows on Amazon WorkSpaces).

Knowing the connector is ODP.NET Managed explains my earlier failure: the `defaultRowPrefetch` I tried under Custom properties is a JDBC property, so an ODP.NET connector would naturally reject it as an invalid connection-string attribute. That makes sense now.

Here's why this matters for me. I let a full import of the table run, and after ~4 hours it failed with:

ORA-03135: Connection lost contact
An established connection was aborted by the software in your host machine

So the low default prefetch isn't only a speed problem — because the extract takes so long, the session stays open long enough for something in the network path (idle timeout or firewall) to drop it before it finishes. The table is ~3.3M rows × 294 columns.

My question: is there an ODP.NET connection property I can set (via Custom properties or elsewhere) to raise the row prefetch / fetch size? A faster extract would complete well within any timeout window, solving both the speed and the dropped-connection issue.

I'd much rather keep this inside EasyMorph if there's a supported way to do it. Otherwise I'll probably fall back to a small Python script (oracledb) where I can set the fetch size directly — but I'd prefer not to add a separate tool to the pipeline if I don't have to.

Thanks!
Albert

4

Hi Albert,

Had to research this topic a little bit.

Looks like there is no way to set fetch size or something like that through custom properties (i.e., connection string). But there is a way to specify either the number of bytes or the number of rows to fetch per round trip in the code. And those limits don't take the content of large object, user defined type, and XMLType columns into account.

We will have to discuss internally if it's feasible for us to expose those options for EasyMorph users. For now, please let us know if this looks like something that will meet your needs.

5

@AlbertFabritius why do you think the problem is with the default prefetch size? Did you try pulling data from the database with another prefetch size? I wonder if the database is just slow by the way it's designed - or it can be a not materialized dynamic data view.

6

Hi Andrew, Dmitry,

Thanks for digging into this. That clears it up: since the native connector uses ODP.NET Managed and the fetch size can only be set in code (not via the connection string), there was no way to tune it from the connector config — which matches what I was seeing.

To close the loop on my end, I solved it by moving the extract to a small Python script using python-oracledb, where the fetch size is a direct cursor setting. The part that matters:

python

import oracledb

# LOBs excluded so the fetch limits actually apply to the rows
oracledb.defaults.fetch_lobs = False

dsn = oracledb.makedsn("host", 1521, sid="opera")
conn = oracledb.connect(user=..., password=..., dsn=dsn)

cur = conn.cursor()
cur.arraysize = 10000       # rows the driver pulls from Oracle per round-trip
cur.prefetchrows = 10000    # driver prefetch

cur.execute('SELECT * FROM "RESERVATION_DAILY_ELEMENT_NAME"')
while True:
    rows = cur.fetchmany(100000)   # chunk size for the load into Snowflake
    if not rows:
        break
    # load chunk into Snowflake

Raising those from the defaults took the extract from hours (and the dropped ORA-03135 connection) to completing comfortably within any timeout window.

To be precise about the request: this isn't an Oracle limitation — the server wasn't specially configured and serves the rows fine. Fetch/prefetch size is a client-side setting, and the low default is just the driver's standard out-of-the-box behavior (tuned for OLTP, not bulk extracts), so anyone pulling large Oracle tables would hit it. It's not an ODP.NET limitation either, since the driver supports raising it in code — the connector just doesn't expose that setting. A "Fetch size" field in the connector config (Option 1) would let users handle large tables without dropping out to a separate tool.

I've got the Python path working as a fallback, so this isn't blocking me anymore — but I think the feature would help anyone moving large Oracle tables through EasyMorph. Happy to test a build if useful.

Albert

7

Thanks, @AlbertFabritius. That clarifies the case a lot, very informative.

We will discuss internally what we can do about it.