davidzollo commented on PR #10118:
URL: https://github.com/apache/seatunnel/pull/10118#issuecomment-3847511248
The current implementation utilizes `connection.createClob()` and
`connection.createBlob()` to generate temporary LOB objects for every row.
According to Oracle JDBC documentation, temporary LOBs created via these
factory methods generally persist for the duration of the session unless
`free()` is explicitly called.
In SeaTunnel's batch mode, a single database connection/session is often
reused to process millions of records. Since the `RowConverter` interface does
not provide a mechanism to track and explicitly `free()` these LOB objects
after the `PreparedStatement` is executed, this implementation will likely
cause the Oracle **Temporary Tablespace** to fill up rapidly during large data
syncs, leading to `ORA-01652: unable to extend temp segment` errors.
**Suggestion**:
I strongly recommend using the JDBC **Stream API** instead of creating
explicit LOB objects. The Stream API is the standard, resource-safe way to
handle large data without manual LOB lifecycle management. It is also generally
effective in bypassing the `ORA-01461` error by treating the data as a stream
rather than a direct string bind.
**Recommended Changes**:
```java
// ... inside setValueToStatementByDataType ...
if (seaTunnelDataType.getSqlType().equals(SqlType.BYTES)) {
if (ORACLE_BLOB.equals(sourceType)) {
// Use setBinaryStream to handle large data and avoid ORA-01461
// This avoids creating a temporary BLOB that needs manual freeing
byte[] bytes = (byte[]) value;
statement.setBinaryStream(statementIndex, new
ByteArrayInputStream(bytes), bytes.length);
} else {
statement.setBytes(statementIndex, (byte[]) value);
}
} else if (seaTunnelDataType.getSqlType().equals(SqlType.STRING)) {
if (ORACLE_CLOB.equals(sourceType) || ORACLE_NCLOB.equals(sourceType)) {
// Use setCharacterStream for CLOB/NCLOB to avoid ORA-01461 and
resource leaks
String strValue = (String) value;
statement.setCharacterStream(statementIndex, new
StringReader(strValue), strValue.length());
} else {
statement.setString(statementIndex, (String) value);
}
}
// ...
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
