Oracle Cloud Infrastructure Documentation

Add a connection

You can manually create a connection to an autonomous database. Adding a connection allows you to specify the connection credentials to the database in which you will connect to the schema of your Autonomous Database.

This connection will allow you to use the database from Excel.
  1. Click on the Add Connection menu on the Manage Connections drop-down icon to add a connection. This opens an Add new connection dialog box.
  2. Specify the following fields on the Add new connection dialog box:
    • Connection Name: Enter the Connection Name for the Autonomous Database URL. For readability purposes, Oracle recommends using a name different name from the URL.
    • Autonomous Database URL: Enter the URL of the Autonomous Database you wish to connect to. Copy the entire URL from the web UI of the Autonomous Database. For example, enter or copy the following link "https://<hostname>-<databasename>.adb.<region>.oraclecloudapps.com/" to connect to the database. This will be provided to you by the administrator.
    • Schema Name: Enter the schema you use for this connection.
    • OAuth Client ID: Enter the Client ID for this connection. Refer to the Generate the client ID for a connection section to generate the client ID of this implicit connection and paste it on this field.
  3. Click Save to save the connection.

You should be able to view the new connection now.

Parent topic: Oracle Autonomous Database for Excel

Generate Client ID for a connection

The OAuth Client key is generated using SQL.

The Copy implicit connection query template button copies the connection query template. The template contains PL/SQL code that generates an OAuth Client ID. To create the Client ID, copy and run this PL/SQL code in the worksheet editor.

This section describes how to generate a client ID.

  1. On the Development tab of the Database Actions Launchpad, select SQL pane. This opens the SQL page.
    Description of sql-card.png follows

  2. Paste the implicit connection query template you copied as explained in the previous section. Here is a sample of the implicit connection query template in the image below.
    Description of paste-template.png follows

  3. On the worksheet editor, replace the "[PROVIDE_A_UNIQUE_CLIENT_NAME]" text in the variable name field with the client name of your choice. The name has to be unique. For example, no other OAuth client can have the same name as the name you provide in this field.
  4. In the worksheet editor, replace the"[PROVIDE_THE_SCHEMA_NAME]" text with your schema name in the variable name field.
  5. You could replace the support URI in the Create Client PL/SQL procedure with the email you used to create the OAuth Client. For example, “youremail@yourorg.com".
    Note

    Do not change the template otherwise, you might view errors that will cause the unsuccessful creation of the Client ID. The p_redirect_uri field is auto-generated and is different for each Autonomous Database.

  6. Click the Run Script icon on the worksheet toolbar to run the PL/SQL code.
    Description of worksheet-editor.png follows

  7. The following is the sample output you will view in the Script Output tab after you run the PL/SQL code.
    Description of script-output.png follows

  8. Copy the client ID from the first line of the script output. You can also copy the client ID equivalent to the client name you provided on the implicit connection query template. Here is the client ID, in the above example, OohrmcjhzmXh3skoeEusXA...
  9. Paste the Client ID on the Client ID value field of the Add new connection dialog box. Refer to the Add a connection for more details on this.
Once you have created a new connection, you can view the connection you have added in the Connections panel. A connection in the panel lists the following:
  • The Connection list displays the database and schema name of the Autonomous Database you connect to.
  • The connection list highlighted in different colours that identifies if the connection is connected or not. A green highlighted connection list indicates an active connection. Whereas a connection list highlighted in grey indicates the connection is not successful.
  • An actions icon rightmost to the connection panel.

Description of adp-connection-panel.png follows

Click the Actions icon on the connection. You can perform the following actions on the selected connection:
  • Connect: Click Connect to connect the add-in with the Autonomous Database. This opens the login page of Oracle Database Actions, the Autonomous Database you wish to connect to.

    Enter the schema name in the username field and the corresponding password.

    Note

    • The Oracle Autonomous Database for Excel asks for your permission the first time you log into the database. Select Approve to proceed with the login.

    You will view a notification page that says the authorization of the Excel with Autonomous Database is successful.

  • Edit: This button enables you to edit the existing connection. Click Edit to review, view or edit connection-based information. Selecting Edit opens the same dialog you view when you add a connection. Edit any information details, such as, Connection Name, Autonomous Database URL, Schema Name or the OAuth Client ID of the existing connection.
  • Duplicate: Select Duplicate to clone the connection from the list of connections displayed in the Connections panel. This creates a copy of the connection without having to enter the details again.
  • Disconnect: Select Disconnect to disconnect from the connection. Once the connection disconnects, you will see a cross mark beside the connection name. This indicates that the connection is terminated.
  • Remove: Select Remove to remove the connection from the list of connections displayed in the Connections panel.

Managing the Excel Add-in Panel


Description of position-addin.png follows

Click Move in the drop-down of the wizard pane to move the wizard to your preferred location.

The Resize option in the drop-down resizes the query window. This option allows you to resize the wizard window by moving the double-headed arrow sideways. The wizard expands when you move the arrow to the left and contracts when you drag it to the right.

Click Close to close the wizard.

Parent topic: Add a connection