Skip to main content
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.
FeatureSupported
Read data with a Source gemYes
Write data with a Target gemNo
Browse data in the Environment browserYes
Index tables in the Knowledge GraphNo

​
Connection parameters

To create a connection with Oracle, enter the following parameters:
ParameterDescription
Connection nameA name to identify your connection in Prophecy
ServerHostname of the Oracle database server
PortPort used by the Oracle database (default is 1521)
UsernameUsername for connecting to the Oracle database
DatabaseOracle 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 and BigQuery.
OracleDatabricksBigQuery
NUMBERDECIMAL(38,5)
Alias: Decimal		BIGNUMERIC(38, 5)
Alias: BigNumeric
SMALLINT / INTEGER / NUMBER(38)BIGINT
Alias: Bigint		INT64
Alias: Integer
FLOATDOUBLE
Alias: Double		FLOAT64
Alias: Float
REAL / FLOAT(63)DOUBLE
Alias: Double		FLOAT64
Alias: Float
DOUBLE PRECISION / FLOAT(126)DOUBLE
Alias: Double		FLOAT64
Alias: Float
BINARY_FLOATDOUBLE
Alias: Double		FLOAT64
Alias: Float
BINARY_DOUBLEDOUBLE
Alias: Double		FLOAT64
Alias: Float
DECIMALDECIMAL(38,5)
Alias: Decimal		BIGNUMERIC(38, 5)
Alias: BigNumeric
NUMERICDECIMAL(38,5)
Alias: Decimal		BIGNUMERIC(38, 5)
Alias: BigNumeric
CHARSTRING
Alias: String		STRING
Alias: String
VARCHARSTRING
Alias: String		STRING
Alias: String
VARCHAR2STRING
Alias: String		STRING
Alias: String
NCHARSTRING
Alias: String		STRING
Alias: String
NVARCHAR2STRING
Alias: String		STRING
Alias: String
LONGSTRING
Alias: String		STRING
Alias: String
CLOBSTRING
Alias: String		STRING
Alias: String
NCLOBSTRING
Alias: String		STRING
Alias: String
BLOBBINARY
Alias: Binary		BYTES
Alias: Bytes
DATETIMESTAMP
Alias: Timestamp		TIMESTAMP
Alias: Timestamp
TIMESTAMPTIMESTAMP
Alias: Timestamp		TIMESTAMP
Alias: Timestamp
TIMESTAMP WITH TIME ZONETIMESTAMP
Alias: Timestamp		TIMESTAMP
Alias: Timestamp
TIMESTAMP WITH LOCAL TIME ZONETIMESTAMP
Alias: Timestamp		TIMESTAMP
Alias: Timestamp
INTERVAL YEAR TO MONTHSTRING
Alias: String		STRING
Alias: String
INTERVAL DAY TO SECONDSTRING
Alias: String		STRING
Alias: String
RAWBINARY
Alias: Binary		BYTES
Alias: Bytes
LONG RAWBINARY
Alias: Binary		BYTES
Alias: Bytes
XMLTypeSTRING
Alias: String		STRING
Alias: String
BOOLEANBOOLEAN
Alias: Boolean		BOOL
Alias: Boolean
URITypeSTRING
Alias: String		STRING
Alias: String
DBURITypeSTRING
Alias: String		STRING
Alias: String
XDBURITypeSTRING
Alias: String		STRING
Alias: String
HTTPURITypeSTRING
Alias: String		STRING
Alias: String
LongVarCharSTRING
Alias: String		STRING
Alias: String
LongRawBINARY
Alias: Binary		BYTES
Alias: Bytes
Learn more in Supported data types.

​
Sharing connections within teams

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.