{"record":{"id":"c88a8502b48562c9","repo":"apache/seatunnel","slug":"failed-to-convert-timestamp-to-scn","errorCode":null,"errorMessage":"Failed to convert timestamp to SCN","messagePattern":"Failed to convert timestamp to SCN","errorType":"exception","errorClass":"SeaTunnelException","httpStatus":null,"severity":"error","filePath":"seatunnel-connectors-v2/connector-cdc/connector-cdc-oracle/src/main/java/org/apache/seatunnel/connectors/seatunnel/cdc/oracle/utils/OracleConnectionUtils.java","lineNumber":127,"sourceCode":"                    statement -> {\n                        java.sql.Timestamp timestamp = new java.sql.Timestamp(timestampMs);\n                        Calendar calendar =\n                                Calendar.getInstance(TimeZone.getTimeZone(effectiveServerTimeZone));\n                        statement.setTimestamp(1, timestamp, calendar);\n                    },\n                    rs -> {\n                        if (rs.next()) {\n                            final String scn = rs.getString(1);\n                            LOG.info(\"Converted timestamp {} to SCN: {}\", timestampMs, scn);\n                            return new RedoLogOffset(Scn.valueOf(scn).longValue());\n                        } else {\n                            throw new SeaTunnelException(\n                                    \"Cannot convert timestamp to SCN. Make sure the specified timestamp is valid.\");\n                        }\n                    });\n        } catch (SQLException e) {\n            LOG.error(\"Failed to convert timestamp to SCN\", e);\n            throw new SeaTunnelException(\"Failed to convert timestamp to SCN\", e);\n        }\n    }\n\n    public static List<TableId> listTables(\n            JdbcConnection jdbcConnection, String database, RelationalTableFilters tableFilters)\n            throws SQLException {\n        final List<TableId> capturedTableIds = new ArrayList<>();\n\n        Set<TableId> tableIdSet = new HashSet<>();\n        String queryTablesSql =\n                \"SELECT OWNER ,TABLE_NAME,TABLESPACE_NAME FROM ALL_TABLES \\n\"\n                        + \"WHERE PARTITIONED = 'YES' OR (TABLESPACE_NAME IS NOT NULL AND TABLESPACE_NAME NOT IN ('SYSAUX'))\";\n\n        try {\n            jdbcConnection.query(\n                    queryTablesSql,\n                    rs -> {\n                        while (rs.next()) {","sourceCodeStart":109,"sourceCodeEnd":145,"githubUrl":"https://github.com/apache/seatunnel/blob/cf67b549a7a6c35fa0beb12d83c62892427ea919/seatunnel-connectors-v2/connector-cdc/connector-cdc-oracle/src/main/java/org/apache/seatunnel/connectors/seatunnel/cdc/oracle/utils/OracleConnectionUtils.java#L109-L145","documentation":"OracleConnectionUtils.timestampToScn wraps any SQLException from the TIMESTAMP_TO_SCN query in a SeaTunnelException 'Failed to convert timestamp to SCN'. This is thrown when the conversion SQL itself fails (as opposed to error 558's empty result). The original SQL error is logged and kept as the cause.","triggerScenarios":"SQLException during the timestamp→SCN query: missing privileges on the conversion function / V$ views, ORA-08185 (flashback not enabled / SCN differs from timestamp), ORA-00904 invalid identifier on older Oracle versions lacking TIMESTAMP_TO_SCN, or connection failure.","commonSituations":"Old Oracle editions (e.g., standard edition without flashback) lacking TIMESTAMP_TO_SCN; missing flashback/execute privileges; database in mount state; connectivity drops.","solutions":["Read the wrapped SQLException/log lines for the exact ORA code","Ensure the Oracle edition supports TIMESTAMP_TO_SCN (Enterprise with flashback); otherwise avoid startup.mode=timestamp","Grant required privileges (SELECT_CATALOG_ROLE, EXECUTE on DBMS_FLASHBACK)","Verify database is open and reachable, then retry"],"exampleFix":"// before (DBA)\n-- missing privilege\n// after\nGRANT SELECT_CATALOG_ROLE TO seatunnel_user;\nGRANT EXECUTE ON DBMS_FLASHBACK TO seatunnel_user;","handlingStrategy":"try-catch","validationCode":"// Confirm the conversion function is available with the CDC user\nSELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) FROM DUAL;","typeGuard":null,"tryCatchPattern":"try {\n    RedoLogOffset off = OracleConnectionUtils.timestampToScn(conn, ts, tz);\n} catch (SeaTunnelException e) {\n    Throwable root = e.getCause(); // SQLException with ORA code\n    LOG.error(\"timestamp->SCN failed: ORA-{}\", root instanceof SQLException ? ((SQLException) root).getErrorCode() : -1, e);\n    throw e;\n}","preventionTips":["Verify the Oracle edition supports TIMESTAMP_TO_SCN before using timestamp startup mode","Grant SELECT_CATALOG_ROLE and EXECUTE on DBMS_FLASHBACK","Keep the connection alive during startup offset resolution","Check ORA-08185 means flashback/SCN mapping is unavailable — switch startup modes"],"tags":["oracle-cdc","scn","timestamp","sql"],"backgroundTag":"sql-query-failed","analyzedSha":"cf67b549a7a6c35fa0beb12d83c62892427ea919","analyzedAt":"2026-09-10T21:44:55.265Z","contentChangedAt":"2026-09-10T21:44:55.265Z","schemaVersion":2},"datasetVersion":"2026-09-23T08:17:48.524Z"}