dcsimg
January 19, 2018
Hot Topics:

Using Oracle JDeveloper 12c with Oracle Database 12c on Oracle Cloud Platform, Part 2

  • December 13, 2017
  • By Deepak Vohra
  • Send Email »
  • More Articles »

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

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.

Access Rules
Figure 1: Access Rules

Click the icon to manage the ora_p2_dblistener access rule, as shown in Figure 2.

Access Rule ora_p2_dblistener>Icon
Figure 2: Access Rule ora_p2_dblistener>Icon

Select Enable from the options listed, as shown in Figure 3.

Enable
Figure 3: Enable

In the Enable Access Rule dialog, click Enable, as shown in Figure 4.

Enable Access Rule
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.

The ora_p2_dblistener access rule enabled
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.

The ora_p2_dblistener access rule enabled for the Quick Start service instance
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.

Service orcldb link
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.

Resources>Public IP and SID
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.

Obtaining Service Name from Connect String
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.

Public IP for the Oracle Database Cloud Service created using Quick Start Wizard
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.

Service name is the substring after "/" in Connect String
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.

Password for Oracle Database service instance created using the Quick Start Wiza
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.

File>New>From Gallery
Figure 13: File>New>From Gallery

Alternatively, select File>New>Application, as shown in Figure 14.

File>New>Application
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.

New Gallery>Database Connection
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.

Create Database Connection
Figure 16: Create Database Connection

Specify a Connection Name (ORCLConnection) and select Connection Type as Oracle (JDBC), as shown in Figure 17.

Selecting Connection Type as Oracle (JDBC)
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.

Test Connection
Figure 18: Test Connection

The Connecting dialog gets displayed, as shown in Figure 19.

Connecting
Figure 19: Connecting

If the connection gets established, the Success! message gets displayed (see Figure 20).

Connection gets established and Success! Message
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.

Error message if access rule ora_p2_dblistener not enabled
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.






Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date