This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

Oracle connector for pivot tables, charts

Oracle Database is supported by SQL data source via official Oracle client.

SeekTable bridges the gap between technical and non-technical users: it provides a user-friendly interface for non-technical users to explore Oracle data visually, while offering advanced SQL capabilities for data architects to construct complex data cubes.

Note that SeekTable is not a replacement for enterprise Oracle BI: it is more a complementary BI tool that adds modern self-service capabilties to the existing BI infrastructure.

Looking for a web pivot table component?

SeekTable's reporting engine PivotData microservice can be seamlessly integrated into any web application for pivot tables generation by Oracle data.

Oracle DB is used as a live data source: no need to refresh reports as users always get actual data (direct query mode).
This means that your Oracle DB server should be able to execute aggregate queries fast enough - ideally in seconds. Usually this is not a problem if configured query returns a reasonable number of rows; otherwise, you may try to configure report parameters to reduce data that needs to be aggregated or use pre-aggregated materialized views. For near real-time reporting on big tables Oracle data can be mirrored to specialized analytical columnar databases like ClickHouse, Snowflake or even DuckDB.
Reports may be shared with the team or published for embedding (or sharing via public link). Users can subscribe to reports in a self-service manner.
The cube itself may be shared too: users can create own reports without having direct access to Oracle database.
Web API can be used for automated reports generation (export to Excel, multipage PDFs).

How to configure Oracle cube

Click the "Connect to Database" to open a new data cube configuration form.
Choose Data Source Type: SQL-compatible database (this opens SQL Settings section).
Choose Database Connector: Oracle

Connection String should be a valid connection string for official .NET Oracle Data Provider. For example:

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostName)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=serviceName)));User Id=user;Password=password;Validate Connection=true;

HOST	Specifies the host name of the machine on which the Oracle Database is running. Do not use "localhost" or LAN server name; use only public IP address or server's domain name.
SERVICE_NAME	specifies the name of the database service.
User Id	Oracle user name. It is recommended to create special user with read-only rights.
Password	Password for the user specified by User Id.
Validate Connection	Add `Validate Connection=True;` to prevent ORA-12570: Network Session: Unexpected packet read error errors that may occur from time to time (this may happen if Oracle server closes 'idle' connections too fast).
Max Pool Size	Default value is 100. If your Oracle DB server is configured to allow rather limited number of opened connections it makes sense to decrease this value.

Configure other required settings as described in SQL-compatible data source. As a minimum you need to specify Select Query (which determine's the dataset) and keep Infer dimensions and measures by dataset checked.

Date-part dimensions calculated with SQL expressions

To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:

add new dimension with Type=Field
fill Name with some unique value: say, "date_column_year"
add one Parameter which should be a date-part Oracle SQL expression:
- For year: EXTRACT(YEAR FROM date_column)
- For month: EXTRACT(MONTH FROM date_column) + Format={0:MMM} (Jan, Feb etc) or {0:MMMM} (January, February etc)
- For day: EXTRACT(DAY FROM date_column)
- For day-of-week: EXTRACT(date_column, DAY_OF_WEEK)-1 + Format={0:ddd} (0=Sun, 1=Mon, 2=Tue etc) or {0:dddd} (0=Sunday, 1=Monday, 2=Tuesday etc)
- For quarter: EXTRACT(date_column, QUARTER)
- For week-of-year: EXTRACT(date_column, WEEK)

Troubleshooting

ORA-01843: not a valid month

This error may occur when your base SQL query (specified in cube's "Select Query") contains date constants in format that is not recognized by Oracle. To fix this you can use TO_DATE function with explicitely defined format:

DATE_COLUMN > TO_DATE('23/04/23','MM/DD/YY');

ORA-12570: Network Session: Unexpected packet read error

Add Validate Connection=True; to cube's "Connection String".

ORA-01882: timezone region not found

If you use a self-hosted SeekTable version please ensure that your installation uses the latest docker images. If problem persists please contact SeekTable support.

Oracle Communication: Failed to connect to server or failed to parse connect string

Ensure that your Oracle DB server can be accessed by SeekTable app. If this error occurs after suddenly (in seektable/pivotdataservice logs you might find "ORA-12516: TNS:listener could not find available handler with matching protocol stack" or "ORA-12520 TNS:listener could not find available handler for requested type of server") it may indicate that the number of connections reached the limit.
To fix this you can either configure Oracle server to allow more concurrent connections (if this number is too small), or decrease the max number of connections that SeekTable can use (see Max Pool Size connection string option).