terrymanu commented on issue #37766:
URL:
https://github.com/apache/shardingsphere/issues/37766#issuecomment-3771017728
Problem Understanding
- Scenario: ShardingSphere-JDBC 5.5.2, Oracle 11g, Hibernate 5.6.15 +
Spring 5.2.20.
- Symptom: Accessing Oracle dictionary views (e.g., ALL_SEQUENCES,
ALL_SYNONYMS) throws Table or view '...' does not exist
(TableNotFoundException).
Root Cause
- During binding, tables are allowed only if present in loaded metadata or
in the system-table whitelist. The whitelist is built by SystemSchemaManager
from schema/<db>/<schema>/<table>.yaml.
- The repository lacks Oracle system table/view definitions, and
OracleSchemaOption#getDefaultSystemSchema() returns empty, so Oracle dictionary
views are not recognized as system tables and are treated as missing by
SimpleTableSegmentBinder#checkTableExists.
Analysis
- SystemSchemaManager expects resources under
schema/{databaseType}/{schemaName}/{tableName}.yaml (see PostgreSQL at
database/connector/dialect/postgresql/src/main/resources/schema/postgresql/pg_catalog/...).
- For Oracle, there is no schema/oracle/... directory and no default
system schema, so ALL_SEQUENCES / ALL_SYNONYMS are not whitelisted, even though
they should be treated as system views.
- This is a framework-side gap (missing system-table definitions and
default system schema), not a user configuration issue.
Conclusion (with fix suggestion)
- We should fill Oracle system-table recognition:
1. Add Oracle system table/view YAML definitions following
schema/{db}/{schema}/{table}.yaml, e.g.,
database/connector/dialect/oracle/src/main/resources/schema/oracle/sys/all_sequences.yaml
and .../all_synonyms.yaml (mirror the PostgreSQL YAML format with column
definitions).
2. Return a default system schema for Oracle, so dictionary views
without explicit schema are recognized, e.g.:
```java
@Override
public Optional<String> getDefaultSystemSchema() {
return Optional.of("SYS");
}
```
3. Add tests for Oracle dialect that parse/bind queries to
ALL_SEQUENCES / ALL_SYNONYMS and assert no TableNotFoundException.
- Community PRs are warmly welcome to add these Oracle system-table YAMLs,
default system schema, and tests; we will gladly review.
--
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]
