Connect to Oracle Analytics Cloud Using a JDBC URL (with Resource Owner Assertiion)

Connect to Oracle Analytics Cloud using a JDBC connection with resource owner assertion so that you can query the semantic model.

  1. Create a bijdbc.properties file that contains the OAuth authentication and authorization properties required to connect to your Oracle Analytics Cloud instance.
    Use the details that you collected earlier. See Collecting Details That You Need for the bijdbc.properties File. Use the following format for your bijdbc.properties file:
    idcsEndpointUrl=https://<IDCS_hostname> 
idcsClientId=<ID string>
idcsClientScope=<ID string> 
idcsClientSecret=<secret>
user=<firstname.lastname@example.com>
password=<password>

    For example:

    idcsEndpointUrl=https://idcs-1a2bc345678901d2e34fgh56789j0ke.identity.c9abc1.oc9def.com
idcsClientId=12a000dc9ef345678000ghij2kl8a34   
idcsClientScope=https://<host>.com:443urn:opc:resource:consumer::all
idcsClientSecret=xyz
user=myuser@office.com
password=yourpassword
  2. Determine the URL required to connect to your Oracle Analytics Cloud instance. The format you use depends on when and how the instance was deployed.
    To connect to an instance deployed on Create date

    Oracle Cloud Infrastructure (Gen 2)

    Any

    Oracle Cloud Infrastructure

    12th May 2020 or later

    Use this URL format with OAuth: 

    jdbc:oraclebi:https://<host>:<port>/api/jdbc?BIJDBC_PROPERTIES_FILE=<fully qualified location and name of properties file>

    For example:

    jdbc:oraclebi:https://<host>:443/api/jdbc?BIJDBC_PROPERTIES_FILE=D:\\Workspace\\bijdbc\\bijdbc.properties
    To connect to an instance deployed on Create date

    Oracle Cloud Infrastructure

    Before 12th May 2020

    Use this URL format with OAuth: 

    jdbc:oraclebi:https://<host>:<port>/bimodeler/api/jdbc?BIJDBC_PROPERTIES_FILE=<fully qualified location and name of properties file>

    For example:

    jdbc:oraclebi:https://abcdefghi123-jklmnopqrs4t-je.analytics.ocp.oraclecloud.com:443/bimodeler/api/jdbc?BIJDBC_PROPERTIES_FILE=D:\\Workspace\\bijdbc\\bijdbc.properties
  3. Test the connection to the target Oracle Analytics Cloud instance.
    Use your favorite SQL command tool to connect to Oracle Analytics Cloud with the appropriate JBDC URL. For example:
    jdbc:oraclebi:https://abcdefghi123-jklmnopqrs4t-je.analytics.ocp.oraclecloud.com:443/api/jdbc?BIJDBC_PROPERTIES_FILE=D:\\Workspace\\bijdbc\\bijdbc.properties