Announcement

**Isomorphic** · 29 Sep 2025, 15:08

Greetings from 2025, oh developer of the future (your build date is 2029??).

If you are doing a series of inserts and do not need the reselect, it can be turned off or configured in a number of ways - see the docs for cacheSyncStrategy.

As far as not seeing the reselect use the same connection as the original insert, that's not normal, however you've shared not a single line of your code, so we can't even speculate as to how you broke this built-in behavior. But basically, it's automatic unless you explicitly turn things off - you might start here:

https://smartclient.com/smartclient-...inTransactions

**Sanmargar** · 3 Oct 2025, 12:53

Bug reproduction scenario

In the built-in-ds sample, I added a custom [Add300] button that inserts 300 records using sendQueue.

When enabling Oracle JDBC debug logging, you can clearly see the number of open cursors increasing until it hits the maximum cursor limit I’ve configured on the DB side (300).

With a smaller number of records, the number of open cursors grows but then eventually closes – most likely when the DB connection itself is closed.

The same scenario works fine on HSQL and MSSQL, but both of these DB engines handle cursors differently and do not have an explicit open cursor limit parameter like Oracle.

Another observation: the error is not always propagated back to the browser. Sometimes none of the items in the RPCResponses array contain any error information. However, starting from the record where the DB error occurs, the returned data contains duplicate values copied from previous rows.

In my specific test case, the failure happens on the 297th inserted record. The response for this record does not contain an error, but instead returns the data from record #61. [screenshot from SC Developer Console]

As mentioned earlier, I believe the issue is caused by missing Statement cleanup in SQLDataSource. In particular, around the code path where you log “Executing ROWID query:”, there seems to be no finally block that closes the JDBC statement. On Oracle this matters a lot because each unclosed Statement holds a cursor.

Something along these lines appears to be missing in a few places:

Code:

Statement stmt = null;
try {
    // execute query / ROWID lookup ...
} finally {
    if (stmt != null) {
        try { stmt.close(); } catch (SQLException ignore) {}
    }
}

I suspect this is not the only spot where statements aren’t being closed, but this one is easy to observe due to the “Executing ROWID query:” log. On Oracle, leaking statements quickly consumes the per-session open cursor budget and explains why the cursor count keeps growing during sendQueue bulk inserts.

I’m pretty sure this bug also showed up when adding records and doing the re-fetch using the primary key instead of ROWID.

the code changes I added into the BuiltInDS sample class:

Code:

IButton Add300Btn = new IButton("Add 300");
        Add300Btn.addClickHandler(new ClickHandler() {
            public void onClick(ClickEvent event) {
                DataSource ds = boundList.getDataSource();

                if (!ds.getID().startsWith("supplyItem"))
                    return;

                boolean wasQueuing = RPCManager.startQueue();
                try {
                    for (int i = 0; i < 300; i++) {
                        Record r = new Record();
                        r.setAttribute("itemName", "Item " + i);
                        r.setAttribute("SKU", "SKU-" + i);
                        r.setAttribute("category", "aaa");
                        r.setAttribute("unitCost", 0);
                        ds.addData(r, null);
                    }
                } finally {
                    RPCManager.sendQueue(new RPCQueueCallback() {
                        @Override
                        public void execute(RPCResponse[] responses) {
                            grid.invalidateCache();
                            grid.fetchData(grid.getCriteria());
                        }
                    });
                }
            }
        });

hLayout.addMember(Add300Btn);

the supplyItemOracle.ds.xml definition:

Code:

<DataSource
    ID="supplyItemOracle"
    serverType="sql"
    tableName="supplyItem"
    dbName="ORA"
    titleField="itemName"
    testFileName="/examples/shared/ds/test_data/supplyItem.data.xml"
    dbImportFileName="/examples/shared/ds/test_data/supplyItemLarge.data.xml"
    sequenceMode="jdbcDriver"
    allowAdvancedCriteria="true"
    useInsertReturning="true">
    <fields>
        <field name="itemID" type="sequence" primaryKey="true" canEdit="false" implicitSequence="true"/>
        <field name="itemName" type="text" title="Item" length="128" required="true"/>
        <field name="SKU" type="text" title="SKU" length="10" required="true"/>
        <field name="description" type="text" title="Description" length="2000"/>
        <field name="category" type="text" title="Category" length="128" required="true" foreignKey="supplyCategory.categoryName"/>
        <field name="units" type="enum" title="Units" length="5">
            <valueMap>
                <value>Roll</value>
                <value>Ea</value>
                <value>Pkt</value>
                <value>Set</value>
                <value>Tube</value>
                <value>Pad</value>
                <value>Ream</value>
                <value>Tin</value>
                <value>Bag</value>
                <value>Ctn</value>
                <value>Box</value>
            </valueMap>
        </field>

        <field name="unitCost" type="float" title="Unit Cost" required="true"> </field>
        <field name="inStock" type="boolean" title="In Stock"/>
        <field name="nextShipment" type="date" title="Next Shipment"/>
    </fields>
</DataSource>

and the connection settings from server.properties.

Code:

sql.ORA.database.type: oracle
sql.ORA.pool.enabled: true
sql.ORA.driver: oracle.jdbc.pool.OracleDataSource
sql.ORA.interface.type: dataSource
sql.ORA.interface.credentialsInURL: true
sql.ORA.autoJoinTransactions: true
sql.ORA.driver.networkProtocol: tcp
sql.ORA.driver.url: jdbc:oracle:thin:@172.22.3.130:1521/ORCLPDB
sql.ORA.driver.user: msdata
sql.ORA.driver.password: password

add ds in BuiltInDS.html

Code:

<!--load the datasources-->
<script src="builtinds/sc/DataSourceLoader?dataSource=supplyItem,animals,employees,supplyItemOracle"></script>

ORACLE DDL:

Code:

CREATE TABLE supplyitem (
  itemid NUMBER(19) GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  itemname VARCHAR2(255 CHAR),
  sku VARCHAR2(255 CHAR),
  description VARCHAR2(255 CHAR),
  category VARCHAR2(255 CHAR),
  units VARCHAR2(255 CHAR),
  unitcost BINARY_DOUBLE,
  instock NUMBER(1),
  nextshipment DATE,
  CONSTRAINT pk_supplyitemhb PRIMARY KEY (itemid)
);

--sample data:

INSERT INTO SUPPLYITEM (
  itemname, SKU, CATEGORY
) VALUES (
  'Rękawice jednorazowe nitrylowe', 'GLV-NIT-XS', 'Materiały ochronne'
);

INSERT INTO SUPPLYITEM (
  itemname, SKU, CATEGORY
) VALUES (
   'Strzykawka 5 ml z igłą 21G', 'SYR-5ML-21G', 'Materiały iniekcyjne'
);

**Isomorphic** · 3 Oct 2025, 13:45

You can use our SQLDataSource to commit 10s of thousands of rows in a transaction in Oracle, you just can't do that while also asking for cache sync values with an implicitSequence.

This is a limitation of Oracle's JDBCDriver - under the hood it's basically using INSERT ... RETURNING to deliver the sequence values back. That's why this is only an issue for Oracle - internally, they are burning tons of cursors (it's not our code).

It's not clear if this is a real use case or just a stress test, as 300+ rows with cache sync requirements is a little weird. If it's really a real situation in your app, we would recommend:

1. just raise the cursor limit. They aren't expensive and nothing is being leaked here; all cursors are freed at the end of the transaction

2. turn off cache sync (canSyncCache="false"). If you need to show updated data to the user, just re-fetch - because of Oracle's issues here, that's going to be much faster, and won't hit the internal cursor limit.

3. if you're really really stuck, you could sponsor an enhancement here, and we'll see if we can get around Oracle's issues in this use case.

**Sanmargar** · 3 Oct 2025, 14:44

The BuiltInDS example I shared was just a simplified test case for you and to verify the behavior.
Originally, I observed the problem on the server side, when I wanted to use refetch and perform a large number of INSERT/UPDATE operations within a single transaction.
In this context, cacheSync wasn’t relevant. The important point is that the JDBC driver returns the ROWID of the inserted record, which allows me to refetch the row, analyze it, and then perform the next operation depending on the results. In my use case, even doing this refetch 300 times is still far more efficient than fetching millions of rows.
The issue is that while the JDBC driver does return the ROWID, it is not propagated through the SmartGWT DataSource, so I cannot make use of it directly.
For now, I’ve worked around the problem by not using DataSource in this part of the code and generating the SQL manually.
That also confirmed my suspicion: if in my own SQL code I forget to close the Statement after execution, I quickly hit ORA-01000 — exactly the same behavior I see in the DataSource scenario.

====

Following the test I described earlier, I’ve noticed something more worrying: when using RPCManager.sendQueue, if the DB throws an error for one of the queued operations, the client may receive no error and the returned data gets corrupted (records shifted/duplicated). In other words, responses no longer correspond to the original requests. This makes it appear as if the batch succeeded while one item actually failed.
I’m concerned this isn’t limited to ORA-01000 (max open cursors) — it may also happen with other DB errors. I use sendQueue in several places in my application, so this is a significant risk.

**Isomorphic** · 3 Oct 2025, 15:58

You say in your use case "cacheSync wasn't relevant" but then you describe needing the ROWID. But needing the ROWID is basically needing cache sync. The purpose of using ROWID would be to do a later reselect to get sequence values (and other transforms, such as stored procedures).

In particular, the default SQL cacheSyncMode of "requestValuesPlusSequences" is basically asking the JDBC driver for sequences. This should be the most efficient possible way to get cache sync, and it is on every DB, including Oracle, except for Oracle's issue with internally burning cursors when asked to just return sequence values.

We wish we could close the Statement to help Oracle reclaim cursors, but unfortunately, there's a second Oracle bug in the way of that - Oracle's drivers are truly exasperating - we'll try to find time to describe this later.

Note, you haven't stated why you need cache sync data (inclusive of ROWID) in your use case, but it's hard to imagine a use case in which it's better to individually ask the driver for cache sync on 300+ rows vs just re-selecting once the insert is done. We would recommend that approach since it seems like a massive waste of time to write direct SQL here.

But if you did go with the direct SQL route, we hope you at least discovered SQLDataSource.getSQLClause(), which would make it a lot simpler.

---

As far as the claim of data scrambling, it is highly implausible that this is a SmartClient Server bug, given our suite of autotests that looks for exactly this, and also the experiences of various customers.

We could definitely imagine, however, that when Oracle runs out of cursors it tries to somehow "recycle" them, and starts returning scrambled results.

So, can you clarify:

1) are you saying your test case, by way of exhausting cursors and causing that specific error, will actually reproduce the data scrambling effect? Reliably?

2) if so, are you aware of "data scrambling" in any other error scenario?

**Sanmargar** · 5 Oct 2025, 00:20

For me, the need to obtain the ROWID was about fetching the data after the record is inserted, because some values are changed by triggers. I need those values for the next operation on the records, before I return the entire result to the client. I don’t need cacheSyncMode for this. I also modified the BuiltInDS example to use sequences, and in that case ORA-01000 occurred as well.
Unfortunately, in my case I can’t change the database schema or configuration parameters in any way.

Re 1) In my previous post you can see a screenshot showing the ORA-01000 error in the browser. In subsequent tests, that error generally stopped appearing in the browser and was visible only in the log. My assumption is that when the error did appear, the data were not mixed. Unfortunately, at that time I didn’t check the matching of requests to responses in detail. I verified this later, additionally surprised to see an error in the log but not in the response. I then ran the tests dozens of times; I repeated them today and, consistently, the data are scrambled.

Re 2) I ran next test: I added a CHECK (itemname <> 'Item 296') constraint to force an error on the record preceding the one where open cursors were exhausted. As a result, the responses look correct. For record 296, an ORA-02290 error is returned, and the subsequent records have ORA-01000. The mix-up of records occurs only when ORA-01000 is the sole error. It may be a timing issue and somewhat pseudo-random behavior. Note that ORA-02290 is an error on the INSERT operation, not SELECT like ORA-01000. I don’t really have an idea how to force a different error on the SELECT that follows the INSERT. That could be, for example, a network connection error or any other database resource error, but it’s hard to trigger without placing a breakpoint between the INSERT and the subsequent SELECT that performs the refetch.

**Isomorphic** · 6 Oct 2025, 11:42

OK, to sum up that last message:

1. you do need cache sync, but the mode you're looking for is "refetch", which would also deal with data modified during the commit via triggers. You've talked about doing it yourself, but we are doing exactly what the built-in cache sync "refetch" mode does.

2. your findings make it clear that Oracle has an internal data scrambling bug related to cursor exhaustion. In fact you've pretty much proven that the SmartClient framework is not scrambling the data just because there's en error - it only happens when Oracle runs out of cursors. Thanks for looking into that.

---

As we mentioned, we'll take a look at what the second Oracle bug is that makes it difficult to reclaim Statements early, but in a nutshell, Oracle has 3 interlocking bugs here which make it more difficult to commit hundreds of records at once, and we described the application-level workarounds above, as well as Oracle settings to eliminate the problem.

**Isomorphic** · 9 Oct 2025, 07:17

We found a different way to work around the second Oracle bug (to do with not returning sequence values like a normal JDBC driver), so it should no longer need one cursor per row in a big insert, fixing your issue.

Oracle is, unfortunately, less efficient than any other DB in this mode (sequenceMode:"jdbcDriver"), because it returns a ROWID instead of sequence values directly, requiring a re-select (basically the same thing you did in your hand-crafted code). So basically, cacheSyncStrategy:"requestValuesPlusSequences" and "reselect" have the same performance for Oracle with sequenceMode:"jdbcDriver". We'll be noting this in the docs.

Backported as far as 13.0.

**Sanmargar** · 19 Oct 2025, 13:56

I confirm, in 13.0 2025-10-17, the issue with ORACLE I described earlier no longer occurs.

Thank you.

Announcement

[Oracle] Open cursor leak during ADD: per-row reselect by ROWID opens a new cursor and doesn’t close it → ORA-01000 after ~300 inserts

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment