Create an Oracle Database Data Source

Disclaimer - Beta Feature

Oracle Database data source is a beta feature. Beta features are available for users to test and provide feedback. They do not have their implementation finalized. The behavior or interface for these features may change in the future.

Do not use beta features in your production environment.

Follow these steps to connect to Oracle Database and create a Oracle Database data source:

  1. Configure User Access Rights

  2. Create a Oracle Database Data Source

Refer to Additional Information for additional performance tips and information about Oracle Database feature support.

Configure User Access Rights

We recommend that you create a dedicated user and user role for integration with the GoodData platform.

Steps:

  1. Create a user role and grant access rights:

    CREATE ROLE {role_name};
GRANT SELECT ON {schema_name}.{table_name} TO {role_name};
GRANT SELECT ON v$session TO {role_name};

  2. Create a user and grant it the user role:

    CREATE USER {user_name} IDENTIFIED BY {password};
GRANT {role_name} TO {user_name};

  3. Make the user role default for the user:

    ALTER USER {user_name} DEFAULT ROLE {role_name};

Create a Oracle Database Data Source

Once you have configured your Oracle Database user’s access rights, you can proceed to create a Oracle Database data source that you can then connect to.

Only API Available

Please note that we currently support this data source only using out API. UI support is planned to be released in one of our upcoming releases.

Steps:

  1. Create a Oracle Database data source with the following API call:

    curl $HOST_URL/api/v1/entities/dataSources \
-H "Content-Type: application/vnd.gooddata.api+json" \
-H "Accept: application/vnd.gooddata.api+json" \
-H "Authorization: Bearer $API_TOKEN" \
-X POST \
-d '{
    "data": {
    "type": "dataSource",
    "id": "<unique_id_for_the_data_source>",
    "attributes": {
    "name": "<data_source_display_name>",
    "url": "jdbc:oracle:thin:@//<ORACLE_HOST>:1521/<ORACLE_DBNAME>",
    "schema": "<ORACLE_DBNAME>",
    "type": "ORACLE",
    "username": "<ORACLE_USER>",
    "password": "<ORACLE_PASSWORD>"
}}}' | jq .

  2. To confirm that the data source has been created, ensure the server returns the following response:

    {
  "data": {
    "type": "dataSource",
    "id": "<unique_id_for_the_data_source>",
    "attributes": {
      "name": "<data_source_display_name>",
      "url": "jdbc:oracle:thin:@//<ORACLE_HOST>:1521/<ORACLE_DBNAME>",
      "schema": "<ORACLE_DBNAME>",
      "type": "ORACLE",
      "username": "<ORACLE_USER>"
    }
  },
  "links": {
    "self": "$HOST_URL/api/v1/entities/dataSources/<unique_id_for_the_data_source>"
  }
}

Additional Information

Ensure you understand the following limitations and recommended practice.

Data Source Details

  • Typical JDBC URL may look like this:

    jdbc:oracle:thin:@//<host>:<port>/<databaseName>

  • Basic authentication is supported. Specify user and password.

  • GoodData uses up-to-date drivers.

Unsupported Features and Limitations

GoodData does not currently support the following features:

  • SQL datasets are not currently supported for the Oracle Database.
  • Show all / FOR EACH is not supported for datetime attributes
  • DATETIME_DIFF is not supported
  • Adding years to weeks may fail in some cases
  • Date arithmetic only supports up to ±99 units (for example +99 days will work, +100 days will not work)
MariaDB
Dremio