Oracle catalog

The Oracle catalog connects Gravitino to an Oracle database over JDBC, exposing Oracle users as schemas and Oracle tables as tables. It is a read-write catalog for tables and a read-only catalog for schemas, because Oracle schemas are database users and Gravitino does not create or drop them.

Oracle folds an unquoted identifier to uppercase and preserves the case of a quoted one. Gravitino reproduces that rule rather than hiding it, so the naming section below matters more here than it does for most catalogs.

Quick start

The example connects an Oracle database as the catalog oracle_catalog and lists its users as schemas. The Oracle JDBC driver ships in the image, so there is no driver to supply.

UI

1. Start the catalog. Open Connect → Catalogs and click + Catalog. Enter oracle_catalog as the Catalog name, keep Relational as the Catalog type, and choose Oracle under Databases and warehouses in Select provider.

2. Enter the connection. Under Connection, enter jdbc:oracle:thin:@{oracle_host}:1521:{oracle_sid} as the URL, and the User and Password of the Oracle account Gravitino connects as. The UI fills in jdbc-driver from the provider.

3. Test and create it. Click Test connection, then Create catalog.

4. List the schemas. In Explore, open oracle_catalog. Gravitino returns the Oracle users visible to the connecting account, filtered as described in Schemas.

REST

1. Create the catalog. The public REST endpoint does not fill in jdbc-driver, so the request names it.

   Create an Oracle catalog

   curl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs" \
     -H "Accept: application/vnd.gravitino.v1+json" \
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer $TOKEN" \
     -d '{
       "name": "oracle_catalog",
       "type": "RELATIONAL",
       "provider": "jdbc-oracle",
       "comment": "Oracle catalog",
       "properties": {
         "jdbc-url": "jdbc:oracle:thin:@{oracle_host}:1521:{oracle_sid}",
         "jdbc-user": "{oracle_user}",
         "jdbc-password": "{oracle_password}",
         "jdbc-driver": "oracle.jdbc.OracleDriver"
       }
     }'

2. List the schemas. Gravitino returns the Oracle users visible to the connecting account, filtered as described in Schemas.

   List the schemas in the catalog

   curl -sS "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/oracle_catalog/schemas" \
     -H "Accept: application/vnd.gravitino.v1+json" \
     -H "Authorization: Bearer $TOKEN"

Catalog properties

The Oracle catalog defines no properties of its own. It takes the jdbc-url, jdbc-driver, jdbc-user, jdbc-password, and pool properties described in Shared properties.

Gravitino issues ALTER SESSION SET CURRENT_SCHEMA on each connection before working with a table, so the connecting account needs privileges on every schema you intend to reach through the catalog, not only on its own.

Schemas

A schema in this catalog is an Oracle user. Gravitino lists and loads them but does not create, alter, or drop them, since managing Oracle accounts is a database administration task rather than a metadata one. Attempting to create or drop a schema raises an unsupported operation error. Schema comments are also unavailable, because Oracle has no COMMENT ON SCHEMA syntax.

Listing filters out Oracle's own accounts. On Oracle 12c and later Gravitino uses the ORACLE_MAINTAINED column of ALL_USERS, which covers accounts installed by optional components. On Oracle 11g that column does not exist, so a built-in list of known system users is used instead, and that list still applies on newer releases as a fallback for accounts that are not marked consistently.

Tables

Table create, load, list, rename, alter, drop, and purge are all supported. A drop is always issued as DROP TABLE ... PURGE, so the table does not land in Oracle's recycle bin and cannot be recovered with FLASHBACK TABLE.

Oracle does not support transactional DDL. An alter that carries several changes is executed as one statement per change in sequence, so a failure partway through leaves the earlier statements committed. The failing statement and the statements that already ran are both written to the server log.

Properties

Four properties come back from Oracle's ALL_TABLES view. Only tablespace can be supplied, and only at create time.

Property	Source	Settable
tablespace	ALL_TABLES.TABLESPACE_NAME	At table creation only
partitioned	ALL_TABLES.PARTITIONED	No, derived by Oracle
row_movement	ALL_TABLES.ROW_MOVEMENT	No, derived by Oracle
compression	ALL_TABLES.COMPRESSION	No, derived by Oracle

Table property changes are not supported at all, so a tablespace chosen at creation cannot be changed afterward through Gravitino.

Table and column comments are supported. Oracle's JDBC driver does not populate REMARKS in DatabaseMetaData.getColumns(), so Gravitino reads column comments from ALL_COL_COMMENTS directly.

Indexes

Primary key and unique key are the only index types this catalog supports, both at create time and through an alter. They are read back from ALL_CONSTRAINTS and ALL_CONS_COLUMNS, which means only constraint-backed indexes appear, not standalone Oracle indexes.

Index names are case-sensitive and are never folded, so a name must be given exactly as it was created for a delete to match it.

Partitioning

Oracle tables can be created with single-level range, list, or hash partitioning. Range partitioning takes a single column, and composite partitioning is not supported in either direction: a table already partitioned on more than one range column cannot be loaded.

Gravitino transform	Oracle clause	Restriction
Range	PARTITION BY RANGE	One column, at least one partition
List	PARTITION BY LIST	Single level
Bucket	PARTITION BY HASH	Single level

Range partition bounds are read back by evaluating Oracle's stored HIGH_VALUE expression, so a bound that Oracle cannot evaluate to a literal of a supported type causes the load to fail rather than returning a partial result.

Table distribution and sort orders are not supported.

Supported changes

Change	Supported
Rename table	Yes
Update table comment	Yes
Add column	Yes, at the default position only
Delete column	Yes
Rename column	Yes
Update column type	Yes
Update column nullable	Yes
Update column default	Yes
Update column comment	Yes
Add or delete index	Yes, for primary key and unique key
Update column position	No, Oracle cannot reorder columns
Auto increment	No, at create time or through an alter
Set or remove property	No

Nested column names are not supported by any of the column changes.

Identifier case and quoting

Gravitino follows Oracle's own rule rather than treating the catalog as uniformly case-sensitive or case-insensitive. An unquoted name is folded to uppercase. A name written with literal double quotes keeps its exact case, and the quotes are consumed as a signal rather than stored.

Name as given	Physical Oracle object	Validation
app_user	APP_USER	Must match ^[A-Za-z][A-Za-z0-9_$#]{0,29}$
"MyTable"	MyTable	Content is 1 to 30 bytes and excludes . and NUL

Two consequences follow. The quoting signal is not persisted, so a later reference to a case-sensitive object has to supply the quoted form again to resolve to the same physical name. And the 30-byte limit is measured in bytes of the database character set, not characters, so a multi-byte name reaches the limit sooner than its length suggests.

Oracle has no escape for a double quote inside a quoted identifier, so an embedded " is rejected rather than doubled.

Names returned by a list operation come back exactly as Oracle stores them, with no quoting added.

Type mapping

Gravitino reads each Oracle type as the Gravitino type in its row, and creates each Gravitino type as the Oracle type in its row, except where the list under the table says otherwise.

Oracle type	Gravitino type
NUMBER(1)	Boolean
NUMBER(p) where p is 2 to 3	Byte
NUMBER(p) where p is 4 to 5	Short
NUMBER(p) where p is 6 to 10	Integer
NUMBER(p) where p is 11 to 19	Long
NUMBER(p) where p is 20 or more	Decimal(p, 0)
NUMBER(p,s) with positive s	Decimal(p, s)
BINARY_FLOAT	Float
BINARY_DOUBLE, FLOAT	Double
VARCHAR2(n), VARCHAR(n)	Varchar(n)
CHAR(n)	Char(n)
CLOB, NCLOB	String
BLOB, RAW, LONG RAW	Binary
TIMESTAMP(p)	Timestamp without time zone, precision p
DATE	Timestamp without time zone
TIMESTAMP(p) WITH TIME ZONE	Timestamp with time zone, precision p
NUMBER with no precision or scale	External type NUMBER
NUMBER(p,s) with negative s	External type NUMBER(p,s)
NCHAR, NVARCHAR2	External type
Any other type	External type carrying the Oracle name

TIMESTAMP WITH LOCAL TIME ZONE is rejected rather than mapped. Gravitino's timestamp type cannot distinguish it from TIMESTAMP WITH TIME ZONE, so accepting it would silently change the semantics of the column on a round trip.

A precision omitted by the driver is treated as Oracle's maximum of 38.

When Gravitino creates a column:

• Byte, Short, Integer and Long become NUMBER(3), NUMBER(5), NUMBER(10) and NUMBER(19).
• Double becomes BINARY_DOUBLE, so a FLOAT column read as Double comes back as BINARY_DOUBLE.
• Varchar(n) becomes VARCHAR2(n), String becomes CLOB, and Binary becomes BLOB. A VARCHAR2 with no precision reads as String, and String is created as CLOB.
• Both timestamp types without a precision become TIMESTAMP(6) and TIMESTAMP(6) WITH TIME ZONE.
• Date has no mapping. Oracle's DATE carries a time component and reads back as a timestamp, so creating a column as Gravitino's date type is rejected. Use an external type of DATE when you need the Oracle type by name.
• An external type becomes the Oracle type it carries.

Column default values

Literal defaults are carried through in both directions. Five Oracle functions are recognized as function expressions rather than being treated as literal text: SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP, CURRENT_DATE, and SYS_GUID. Any other expression is preserved as an unparsed expression.

Oracle requires DEFAULT to precede NOT NULL in a column definition, and Gravitino generates the clauses in that order.

Error mapping

Oracle error codes are translated into Gravitino exceptions so that a caller sees the same failure types it would from any other catalog.

Oracle error	Gravitino exception
ORA-01918, ORA-01435 user does not exist	No such schema
ORA-01920 user name conflicts with an existing name	Schema already exists
ORA-00942 table or view does not exist	No such table
ORA-00955 name is already used by an existing object	Table already exists
ORA-01031 insufficient privileges	Runtime error naming the cause

Any other error code produces a generic runtime exception carrying the Oracle message, and the unrecognized code is written to the server log.

Related

• JDBC catalogs
• Catalog connections
• Tables and views