Connector for Oracle — Features and settings

Page content

Connector features

The following functionality is available when you access data with the connector for Oracle.


Feature	Supported?
Load methods	Import (in-memory) External (in-database) On-demand
Custom queries	Yes
Stored procedures	Yes, with requirements. See section below.
Custom connection properties	Yes
Single sign-on with identity provider	Yes
Authoring in web client	Yes
Included in Spotfire distribution on Linux	Yes

Data source properties

The following are the supported data source properties that you can configure when you create a data connection with the connector for Oracle.


Option	Description
Connection type	Select how you want to enter the information about what Oracle database to connect to. Select from the following options: Server Connect to your database by entering the server name, and the Oracle System Identifier (SID) or Service name. Connect descriptor Connect to your database by entering a connect descriptor. Select this option to connect to Oracle Real Application Clusters (RAC) databases.
Server	The name of the server where your data is located. To include a port number, add it directly after the server name preceded by colon. Example with port number: MyOracleDatabaseServer:1234
SID	[Only available for Server connection type.] Select this option to specify an Oracle System Identifier (SID) to use when connecting to the database. SID is used to uniquely identify a particular database on a system.
Service name	[Only available for Server connection type.] Select this option to specify a service name to use when connecting to the database. The service name is the TNS (Transparent Network Substrate) alias that you give when you remotely connect to your database.
Connect descriptor	Enter the location of the database and the name of the database service to connect to your Oracle database. A connect descriptor is formatted in a specific way. For information about how to format a connect descriptor, see the official documentation from Oracle.
Authentication method	The authentication method to use when logging into the database. Windows authentication When using Windows authentication, e.g., Kerberos, the access token of the currently logged in user will be used. Users that have been given the appropriate access rights to the Oracle database will be able to connect and read data. Domain credentials are not stored in the analysis file. Oracle authentication With a database authentication, the authentication is done using a database user. Database credentials can be stored, unencrypted, as part of the analysis file, using a setting in the Data Source Settings dialog . If credentials are found in the analysis file they will be used to automatically authenticate against the database. If no credentials or credentials profiles are found in the analysis file, all who open the file will be prompted for database credentials. Note that there will be no prompting for credentials if the credentials embedded in the analysis file fail. Identity provider (OAuth2) Use an identity provider, such as Okta or Keycloak, to log in. When you open the data connection, a web browser window opens, and you log in with the identity provider's login procedure.
Identity provider	[Only applicable for Identity provider (OAuth2) authentication.] Select the identity provider you want to use for logging in to the data source. The options available in the drop-down menu are the identity providers you have added to the OAuth2IdentityProviders preference.
Scopes	[Only applicable for Identity provider (OAuth2) authentication.] Scopes determine what permissions Spotfire requests on your behalf when you log in to the data source. Default Use the default scopes that you have specified for your identity provider in the `OAuth2IdentityProviders` preference. Custom Enter scopes manually in the text box. Separate values with a space. `Scope_1 Scope_2`
HA events	If you are connecting to an Oracle RAC database, select HA (High Availability) events to enable the Oracle Data Provider for .NET (ODP.NET) connection pool to remove connections from the pool if an RAC service, service member or node goes down. Note: This option will only have effect if you are connecting to an Oracle RAC database.
Load balancing	If you are connecting to an Oracle RAC database, select this option to enable the Oracle Data Provider for .NET (ODP.NET) connection pool to balance work requests across RAC instances. Note: This option will only have effect if you are connecting to an Oracle RAC database.
Statement cache purge	Select this option to empty the statement cache for your connection when you close the connection in Spotfire. The statement cache is used to store copies of the SQL statements (queries) that are sent from Spotfire to Oracle, in the Oracle database memory.
Statement cache size	[Only available when Statement cache purge is enabled.] Specify the maximum number of statements that can be stored in the statement cache. The default value is 0.
Proxy user ID	If you want to use a proxy database user, specify the proxy user ID. Note that this is not the same as the Oracle username.
Proxy password	If you are using a proxy authentication, specify the password for that proxy database user. Note that this is not the same as the Oracle user password.
Connection lifetime (s)	In some situations, you might want certain connections to be removed from the ODP.NET connection pool. With the connection lifetime option, you can specify the minimum time (in seconds) that the connection is open. When you close the connection in Spotfire, if the connection had been open for longer than the lifetime value, it will be removed from the ODP.NET connection pool. The default value is 0, which means that the connection lifetime will not been checked.
Connection timeout (s)	Specify the maximum number of seconds allowed for a connection to the database to be established. In the case of Oracle, it is the maximum time allowed to wait for a free connection from the connection pool. The default value is 15. Note: If you set the connection timeout to zero, it will be interpreted as no timeout. That is, there will be no upper limit for trying to connect. This is generally not recommended.
Command timeout (s)	Specify the maximum number of seconds allowed for a command to be executed. The default value is 120. Note: If you set the command timeout to zero, it will be interpreted as no timeout. That is, there will be no upper limit for trying to execute the command. This is generally not recommended.
Fetch size (bytes)	Specify the value for the `FetchSize` property of the OracleDataReader. The default value is 131072.

Custom properties for Oracle connection data sources

The following is the default list of driver settings that are allowed as custom properties in Oracle connection data sources. To learn how to change the allowed custom properties, see Controlling what properties are allowed.

Default allowed custom properties

DBA Privilege, Decr Pool Size, Enlist, Incr Pool Size, Max Pool Size, Metadata Pooling, 
Min Pool Size, Persist Security Info, Pooling, Promotable Transaction, Self Tuning, Validate Connection

Supported data types

When you are setting up a connection to an external data source, Spotfire needs to map the data types in the data source to data types in Spotfire. The following are the data types that the Oracle connector supports.


Database data type	Spotfire data type
BFILE	Binary
BINARY_DOUBLE / REAL	Real
BINARY_FLOAT / FLOAT	SingleReal
BLOB	Binary
CHAR	String
CLOB	String
DATE	Date
INTERVAL DAY TO SECOND	TimeSpan
LONG	String
LONG RAW	Binary
NCHAR	String
NCLOB	String
NUMBER (NUMBER(x, s))	Boolean (NUMBER(1)): Boolean Integer (NUMBER(p) 2 <= p <= 9): Integer Long (NUMBER(p) 10 <= p <= 18): LongInteger Double (NUMBER(x, s) 16 > x > s > 0): Real
NVARCHAR / NVARCHAR2	String
RAW	Binary
RAW(16)	String
ROWID	String
SDO_GEOMETRY	Binary
TIMESTAMP	DateTime
UROWID	String
VARCHAR / VARCHAR2	String
XMLType	String

Supported functions

Supported functions are the functions that you can use when you work with in-database data tables, for example for calculated columns and custom expressions.

Note: Some supported functions might not be possible to use with your database. This depends on what functions are available in the database, which often differs between database versions and types.

The following are the functions that the Oracle connector supports.


Function type	Functions supported
Date and Time	`Year, Quarter, Month, MonthsBetween, AddMonths, Week, WeekOfMonth, Day, LastDay, NextDay, DayOfMonth, DayOfWeek, DayOfYear, Hour, Minute, Second`
Conversion	`SN`
Math	`Abs, ACos, ASin, ATan, ATan2, Ceil, Cos, Cosh, Exp, Floor, Ln, Log, Power, Sin, Sinh, Sqrt, Tan, Tanh`
Operators	`+, -, *, /, %, Mod`
Statistical	`Sum, Avg, Min, Max, Count, UniqueCount, Median, Variance, Var_Pop, Var_Samp, StdDev, StdDev_Pop, StdDev_Samp, Corr, CoVar_Pop, CoVar_Samp, Regr_AvgX, Regr_AvgY, Regr_Count, Regr_Intercept, Regr_R2, Regr_Slope, Regr_SXX, Regr_SXY, Regr_SYY`
Text	`Concat, Instr, Length, Lower, LTrim, RTrim, Soundex, Trim, Upper`

Binning is also supported by Oracle.

Stored procedures in Oracle

For stored procedures in Oracle, the following requirements must be met:

Input (IN or IN/OUT) parameters must not be cursors.
Input (IN or IN/OUT) parameters must be placed before all OUT parameters.
The first OUT parameter will be used to read data and it must be a REF CURSOR.
All subsequent OUT parameters will be ignored.

The schema of the stored procedure must not change from time to time (for example, the use of parameters must not change the schema).

Note: Generally, stored procedures that do not result in a data table output will not be visible in the Views in Connection dialog for some connectors.