In my previous post, I documented the steps to create an ATP service in Oracle Cloud Infrastructure. Here, I will describe how to connect and use that same ATP service using SQL Developer and SQL*Plus.

Note – this post assumes that you have already provisioned an ATP Service in Oracle Cloud. If you haven’t see here.

---

SQL Developer

Step 1: Ensure that you have SQL Developer installed on your desktop. If not, you can download from here. (I am currently using v22.2.1)

Step 2: In order to connect to the ATP service, you’ll need the client credentials wallet which you can download from the Oracle Cloud console. Navigate to your ATP Service and click “Database Connection”.

Step 3: Click “Download Wallet”

Step 4: Enter a password to protect the credentials wallet and click “Download”

Step 5: Open SQL Developer and click the green “+” symbol in the “Connections” pane.

Step 6: Enter the following required information:

• Connection Name: Enter a user friendly connection name so that you can easily identify the database connection
• Username: here I have used “admin” which you will recall was a user set up during the provisioning of the ATP service. generally this isn’t good practice. I would advise that you create additional users with the appropriate privileges
• Password: Enter the password for your user account
• Connection Type: Select “Cloud Wallet”
• Configuration File: Browse for the Zip file downloaded in step 4

Step 7: You can use the “Test”, “Save” and “Connect” buttons as follows:

• Test – Test the connection to ensure that you have entered correct credentials. Note – see the “Success” message in the bottom left corner. This is present as I’ve clicked “Test”
• Save – Save the connection so that you don’t have to enter these credentials every time that you want to connect to the database. Note – see the connection name and details in the left pane. This is present as I’ve clicked “Save”
• Connect – Use this to connect to the database and exit the database connection wizard

Now, you will see the new database connection in the connection pane within SQL Developer and an opened worksheet. In this worksheet, you can start working with your database.

SQL*Plus

Step 1: Make sure that you have Oracle Instant client & Sqlplus downloaded to access SQL*Plus. If not, I would recommend following a tutorial to do this such as this one here.

Step 2: In order to connect to the ATP service, you’ll need the client credentials wallet which you can download from the Oracle Cloud console. Navigate to your ATP Service and click “Database Connection”.

Step 3: Click “Download Wallet”

Step 4: Enter a password to protect the credentials wallet and click “Download”

Step 5: Choose a location to save your client credentials.

Step 6: Unzip the downloaded wallet.

Step 7: Edit the sqlnet.ora file with the correct location of the wallet data. For me this is as below:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\Users\agrange\Documents\_Blog\ATPDB2\wallet")))
SSL_SERVER_DN_MATCH=yes
EOF

Step 8: Make sure that your device PATH environment variable is set to your instant client location to enable use of sqlplus

Step 9: As I’m not using the “Standard” location for my sqlnet.ora file, before I can start sqlplus, I need to set my TNS_ADMIN environment variable too:

Step 10: Now, you can connect to the database using sqlplus. You will need to use one of the entries in the tnsnames.ora file which came downloaded with your client credentials wallet.

sqlplus <username>/<password>@<dbname>_high