Oracle DB

Oracle DB is a relational database management system. In Prophecy, you can connect to Oracle to read from and write to database tables as part of your pipelines. This page explains how to set up the connection, including required parameters, permissions, and how connections are shared within teams.

Prerequisites

Prophecy connects to Oracle using the database credentials you provide. These credentials are used to authenticate your session and authorize all data operations performed during pipeline execution. To use an Oracle connection effectively, your user account must have:

Read access to query data from tables
Write access to insert, update, or delete data

Feature support

The table below outlines whether the connection supports certain Prophecy features.

Feature	Supported
Read data with a Source gem	Yes
Write data with a Target gem	No
Browse data in the Environment browser	Yes
Index tables in the Knowledge Graph	No

Connection parameters

To create a connection with Oracle, enter the following parameters:

Parameter	Description
Connection name	A name to identify your connection in Prophecy
Server	Hostname of the Oracle database server
Port	Port used by the Oracle database (default is `1521`)
Username	Username for connecting to the Oracle database
Database	Oracle Service Name or SID of the target database
Password (Secret required)	Password for the specified user

Data type mapping

When Prophecy processes data from Oracle using SQL warehouses, it converts Oracle-specific data types to formats compatible with your target warehouse. This table shows how Oracle data types are transformed for Databricks, BigQuery, and Snowflake.

Oracle	Databricks	BigQuery	Snowflake
NUMBER	`decimal(38,5)` Alias: Decimal	`bignumeric(38,5)` Alias: BigNumeric	`number(38,10)` Alias: Number
SMALLINT / INTEGER / NUMBER(38)	`bigint` Alias: Bigint	`int64` Alias: Integer	`number` Alias: Number
FLOAT	`double` Alias: Double	`float64` Alias: Float	`float` Alias: Float
REAL / FLOAT(63)	`double` Alias: Double	`float64` Alias: Float	`float` Alias: Float
DOUBLE PRECISION / FLOAT(126)	`double` Alias: Double	`float64` Alias: Float	`float` Alias: Float
BINARY_FLOAT	`double` Alias: Double	`float64` Alias: Float	`float` Alias: Float
BINARY_DOUBLE	`double` Alias: Double	`float64` Alias: Float	`float` Alias: Float
DECIMAL	`decimal(38,5)` Alias: Decimal	`bignumeric(38,5)` Alias: BigNumeric	`number(38,10)` Alias: Number
NUMERIC	`decimal(38,5)` Alias: Decimal	`bignumeric(38,5)` Alias: BigNumeric	`number(38,10)` Alias: Number
CHAR	`string` Alias: String	`string` Alias: String	`string` Alias: String
VARCHAR	`string` Alias: String	`string` Alias: String	`string` Alias: String
VARCHAR2	`string` Alias: String	`string` Alias: String	`string` Alias: String
NCHAR	`string` Alias: String	`string` Alias: String	`string` Alias: String
NVARCHAR2	`string` Alias: String	`string` Alias: String	`string` Alias: String
LONG	`string` Alias: String	`string` Alias: String	`string` Alias: String
CLOB	`string` Alias: String	`string` Alias: String	`string` Alias: String
NCLOB	`string` Alias: String	`string` Alias: String	`string` Alias: String
BLOB	`binary` Alias: Binary	`bytes` Alias: Bytes	`binary` Alias: Binary
DATE	`timestamp` Alias: Timestamp	`timestamp` Alias: Timestamp	`timestamp` Alias: Timestamp
TIMESTAMP	`timestamp` Alias: Timestamp	`timestamp` Alias: Timestamp	`timestamp` Alias: Timestamp
TIMESTAMP WITH TIME ZONE	`timestamp` Alias: Timestamp	`timestamp` Alias: Timestamp	`timestamp` Alias: Timestamp
TIMESTAMP WITH LOCAL TIME ZONE	`timestamp` Alias: Timestamp	`timestamp` Alias: Timestamp	`timestamp` Alias: Timestamp
INTERVAL YEAR TO MONTH	`string` Alias: String	`string` Alias: String	`string` Alias: String
INTERVAL DAY TO SECOND	`string` Alias: String	`string` Alias: String	`string` Alias: String
RAW	`binary` Alias: Binary	`bytes` Alias: Bytes	`binary` Alias: Binary
LONG RAW	`binary` Alias: Binary	`bytes` Alias: Bytes	`binary` Alias: Binary
XMLType	`string` Alias: String	`string` Alias: String	`string` Alias: String
BOOLEAN	`boolean` Alias: Boolean	`bool` Alias: Boolean	`boolean` Alias: Boolean
URIType	`string` Alias: String	`string` Alias: String	`string` Alias: String
DBURIType	`string` Alias: String	`string` Alias: String	`string` Alias: String
XDBURIType	`string` Alias: String	`string` Alias: String	`string` Alias: String
HTTPURIType	`string` Alias: String	`string` Alias: String	`string` Alias: String
LongVarChar	`string` Alias: String	`string` Alias: String	`string` Alias: String
LongRaw	`binary` Alias: Binary	`bytes` Alias: Bytes	`binary` Alias: Binary

For Oracle number types with explicit precision and scale, Prophecy preserves those values when mapping to Snowflake. For example, Oracle number(10,2) maps to Snowflake number(10,2).

Learn more in Supported data types.

Connections in Prophecy are stored within fabrics, which are assigned to specific teams. Once an Oracle connection is added to a fabric, all team members who have access to the fabric can use the connection in their projects. No additional authentication is required—team members automatically inherit the access and permissions of the stored connection credentials.

Be mindful of the access level granted by the stored credentials. Anyone on the team will have the same permissions—including access to sensitive data if allowed.To manage this securely, consider creating a dedicated fabric and team for high-sensitivity connections. This way, only approved users have access to those credentials.

Getting started

Build with Agent

Build and inspect pipelines

Explore and visualize

Platform setup

Run and share

Collaboration

Prerequisites

Feature support

Connection parameters

Data type mapping

​Prerequisites

​Feature support

​Connection parameters

​Data type mapping

​Sharing connections within teams

Prerequisites

Feature support

Connection parameters

Data type mapping

Sharing connections within teams