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

Setup Oracle for pivot table reports

Oracle Database can be used as a data source with SQL-compatible database connector. SeekTable uses 'live' connection to Oracle and suitable for near real-time reporting (pivot tables, charts, flat tables) and ad-hoc queries that can be made in a self-service manner even by non-IT users (without need to write an SQL). With SeekTable you can provide access to your Oracle database in a managed and secure way, without giving direct access to the DB for end-users. Also SeekTable can be used for automated reports generation and as an embedded BI.

There are no any limitations on the dataset size, but your Oracle should be able to execute aggregate queries fast enough (in seconds; 2 minutes max).

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 MongoDb with simple web API.

Oracle connection setup

Connection String should be a valid connection string for .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).

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:

  1. add new dimension with Type=Field
  2. fill Name with some unique value: say, "date_column_year"
  3. 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} (Mon, Tue etc) or {0:dddd} (Monday, 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.