Skip to main content

About the Oracle collector

Use this collector to harvest metadata for Oracle tables and columns across the enterprise systems and make it searchable and discoverable in data.world. It is suitable for both self-hosted Oracle instances and managed Oracle instances, such as those hosted on AWS RDS.

Important

The Oracle collector can be run in the Cloud or on-premise using Docker or Jar files.

Note

The latest version of the Collector is 2.239. To view the release notes for this version and all previous versions, please go here.

What is cataloged

The collector catalogs the following information.

Note

The collector harvests all versions of overloaded functions and stored procedures. Each version has its own title/name in the catalog, but a distinct identifier.

Table 1.

Object

Information cataloged

Columns

Name, JDBC type, Column Type, Is Nullable, Default Value, Key type (Primary, Foreign), Column size, Column index

Extended metadata: Comment

Table

Name, Primary key, Schema

Extended metadata: Comment

Views

Name, Definitions

Extended metadata: Description

Schema

Identifier, Name

Database

Type, Name, Identifier, Server, Port, Environment, JDBC URL

Function

Name, Description, Function Type

Extended metadata: Function definition, Package Name

Stored Procedure

Name, Description, Stored Procedure Type

Extended metadata: Procedure Definition, Package Name

Synonym

Identifier

Extended metadata: Name

Package

Name, Specification

Extended metadata: Body definition, Package Body



Relationships between objects

By default, the harvested metadata includes catalog pages for the following resource types. Each catalog page has a relationship to the other related resource types. If the metadata presentation for this data source has been customized with the help of the data.world Solutions team, you may see other resource pages and relationships.

Table 3.

Resource page

Relationship

Table

Columns

Columns

Table

Schema

Database that contains Schema, Table that is part of Schema

Database

Schema contained in Database

Synonym

The schema which contains the synonym, The table, view, function, procedure, or package the synonym is for

Package

Schema that contains package

Extended metadata: Stored Procedures contained in package, Functions contained in package



Lineage for Oracle

The following lineage information is collected by the Oracle collector. To harvest lineage from referenced objects located in another server you must set up the --linked-host=<linkedHostMapping> while running the collector.

Note

Any lineage for SQL Statements defined via variable statements are not supported.

Table 4.

Object

Lineage available

Column in View

The collector identifies:

  • The associated column in an upstream view or table

    • Where the data is sourced from

    • That sort the rows via ORDER BY

    • That filter the rows via WHERE/HAVING

    • That aggregate the rows via GROUP BY

  • The downstream table that has its data updated by this function.

User-defined function

A Function and:

  • Views referenced in Function

  • Tables referenced in Function

  • Functions referenced in Function

  • Stored procedures referenced in function

A View and:

  • Function referencing View

The collector identifies:

  • The associated column in an upstream view or table

    • Where the data is sourced from

    • That sort the rows via ORDER BYThat filter the rows via WHERE/HAVING

    • That aggregate the rows via GROUP BY

  • The downstream table that has its data updated by this Stored Procedure.

Stored Procedure

  • A Stored Procedure and:

    • Views referenced in Stored Procedure

    • Tables referenced in Stored Procedure

    • Functions referenced in Stored Procedure

    • Stored procedures referenced in Stored Procedure

  • A View and:

    • Stored Procedure referencing View

The collector identifies:

  • The associated column in an upstream view or table

    • Where the data is sourced from

    • That sort the rows via ORDER BY

    • That filter the rows via WHERE/HAVING

    • That aggregate the rows via GROUP BY

  • The downstream table that has its data updated by this Stored Procedure.

Note: The following stored procedures are not supported:

  • Stored procedures with multitable inserts. However, multiple separate insert statements that insert into one table are supported.

  • Stored procedure with multiple SELECT and INSERT statements not separated by a semicolon delimiter.

  • Stored procedures used to create tables or any transient tables created during the execution scope of a stored procedure.



Oracle version supported

  • Oracle database 12.1.0.1 JDBC driver version available here.

Authentication supported

  • The Oracle collector supports username/password authentication to Oracle.