In the first of three articles, “Using Oracle JDeveloper 12c with Oracle Database 12c on Oracle Cloud Platform, Part 1,” we created two instances of database services for Oracle Database 12c using the Oracle Cloud Platform. One instance is created by using the Quick Start Wizard, and the other by using the detailed wizard. In this second article, we shall modify access rules for the database services to enable access to the services. Subsequently, we shall connect to the Oracle Database Service instance created using the Quick Start Wizard. We shall use Oracle SID to connect. In the next article, we shall use Oracle Database Service Name.
This article has the following sections:
- Modifying Access Rules
- Obtaining Connection Parameters for Oracle Database Cloud Service
- Creating a Connection in JDeveloper Using Oracle SID
- Conclusion
Modifying Access Rules
The default settings for access rules do not allow the database instances on Oracle Cloud to be accessed by an external user. In this section, we shall modify the access rules to allow external access. The procedure to enable external access to Oracle database on the Cloud platform is the same for the database service instance created using the Quick Start Wizard and service instance created using the detailed wizard. Therefore, we shall discuss only for the service instance created using the detailed wizard. Form the service management drop-down options, select Access Rules, as shown in Figure 1.
Figure 1: Access Rules
Click the icon to manage the ora_p2_dblistener access rule, as shown in Figure 2.
Figure 2: Access Rule ora_p2_dblistener>Icon
Select Enable from the options listed, as shown in Figure 3.
Figure 3: Enable
In the Enable Access Rule dialog, click Enable, as shown in Figure 4.
Figure 4: Enable Access Rule
The Processing dialog gets displayed while the access rule is getting enabled. The ora_p2_dblistener access rule is shown enabled in Figure 5.
Figure 5: The ora_p2_dblistener access rule enabled
Similarly, enable the ora_p2_dblistener access rule for the database service instance created using the Quick Start Wizard, as shown in Figure 6.
Figure 6: The ora_p2_dblistener access rule enabled for the Quick Start service instance
Obtaining Connection Parameters for Oracle Database Cloud Service
We shall need the following connection parameters (see Table 1) for an Oracle Database service instance to connect to the database.
Connection Parameter | Description | Parameter Setting in Service Console |
Host Name | The database host public IP address | Resources>Public IP, as shown in Figure 8 |
SID | The Oracle Database SID. Only one of SID or service name is required for a connection. | Resources>SID, as shown in Figure 8 |
Service Name | The database service name. Only one of SID or service name is required for a connection. | The Connect String sub-string after the ‘/’, as shown in Figure 9 |
JDBC Port | The JDBC port required to connect to Oracle Database. | 1521 as in the Connect String shown in Figure 9 |
Username | The username to connect to Oracle Database. SYS or SYSTEM | |
Password | The password to connect to Oracle Database. The Password for the database service instance created using detailed start wizard is configured in Figure 14 of “Using Oracle JDeveloper 12c with Oracle Database 12c on Oracle Cloud Platform, Part 1” for the service instance. For the service instance created using the Quick Start Wizard the service_credentials.zip downloaded in Figure 6, also in the first article, includes a service_credentials.txt file with the database password. | Not displayed in Service Console |
Table 1: Connection Parameters
Next, we shall obtain the connection parameters for the Oracle Database 12c for the two service instances. Click the orcldb link in the Services, as shown in Figure 7, to list service details.
Figure 7: Service orcldb link
The service detail gets displayed. The Public IP is listed in the Resources header, as shown in Figure 8. The Host name is orcldb, but cannot be used to configure a connection in JDeveloper. The SID is listed as ORCL in the Resources section.
Figure 8: Resources>Public IP and SID
For the Service name, click the partially displayed value for Connect String. Copy the substring after the “/” to be used as the service name, as shown in Figure 9.
Figure 9: Obtaining Service Name from Connect String
Similarly, for the database service instance created using the Quick Start Wizard, obtain the Public IP from the Service console (see Figure 10). The SID is ORCL.
Figure 10: Public IP for the Oracle Database Cloud Service created using Quick Start Wizard
And, the service name is the substring after the “/” in the Connect String, as shown in Figure 11.
Figure 11: Service name is the substring after “/” in Connect String
The password for the Oracle Database service instance generated using the Quick Start Wizard is listed in the service_credentials.txt file (see Figure 12), which is included in the service_credentials.zip file downloaded when the service instance is created.
Figure 12: Password for Oracle Database service instance created using the Quick Start Wizard
Creating a Connection in JDeveloper Using Oracle SID
In this section, we shall discuss creating a connection with the Oracle Database instances on Oracle Cloud with Oracle JDeveloper. We shall connect with the Oracle Database service instance created by using the Quick Start Wizard. To create a connection from Oracle JDeveloper, select File>New>From Gallery, as shown in Figure 13.
Figure 13: File>New>From Gallery
Alternatively, select File>New>Application, as shown in Figure 14.
Figure 14: File>New>Application
In New Gallery, select General>Connections in Categories and Database Connection in Items and click OK, as shown in Figure 15.
Figure 15: New Gallery>Database Connection
The features for creating a database connection start loading. The Create Database Connection Wizard gets started, as shown in Figure 16.
Figure 16: Create Database Connection
Specify a Connection Name (ORCLConnection) and select Connection Type as Oracle (JDBC), as shown in Figure 17.
Figure 17: Selecting Connection Type as Oracle (JDBC)
Specify Username as SYS and Password as the password generated in service_credentials.txt file and shown in Figure 12. Because the SYS connection must be as SYSDBA, select the Role as SYSDBA, as shown in Figure 18. Optionally, select the Save Password checkbox.
In Oracle (JDBC) Settings, select the Driver as thin (default value). Specify Host Name as the Public IP from Figure 10. Select the option to use SID and specify the value as ORCL. Click Test Connection, as shown in Figure 18.
Figure 18: Test Connection
The Connecting dialog gets displayed, as shown in Figure 19.
Figure 19: Connecting
If the connection gets established, the Success! message gets displayed (see Figure 20).
Figure 20: Connection gets established and Success! Message
If a connection does not get established, an error message gets displayed. Several different types of error messages could get displayed, based on the type of error. As an example, if the ora_p2_dblistener access rule is not enabled as discussed in the Modifying Access Rules section, the error shown in Figure 21 gets displayed.
Figure 21: Error message if access rule ora_p2_dblistener not enabled
Conclusion
In this second of three articles on using JDeveloper with Oracle Database 12c running on Oracle Cloud Platform, we enabled the access rules to allow access to the database instances. Next, we obtained the connection parameters from the service console. Subsequently, we connected to Oracle Database on Oracle Cloud Platform with Oracle JDeveloper using SID. In this article, we connected to the Oracle Database 12c instance created using the Quick Start Wizard. In the third article, we shall connect to the database instance created using the Quick Start Wizard using Oracle Service name. In the third article, we also shall connect to the Oracle Database instance created using the detailed wizard. We shall also use a SQL Worksheet to create and query a database table.