dcsimg
August 21, 2018
Hot Topics:

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

  • December 20, 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 discussed creating two Oracle Database 12c service instances on Oracle Cloud Platform—one using the Quick Start wizard and the other using the detailed wizard. In the second article, "Using Oracle JDeveloper 12c with Oracle Database 12c on Oracle Cloud Platform, Part 2," we modified access rules to enable access to the database service instances. We also obtained connection parameters for the database service instances and connected to one of the database service instances using Oracle SID. In this article, we shall discuss creating a connection to the database service instance created by using the Quick Start wizard with the Oracle Service Name connection parameter. We also will discuss creating a connection to the Oracle Database 12c service instance created using the detailed wizard. We create a table, add data, and query the table using a SQL Worksheet. This article has the following sections:

Creating a Connection Using Oracle Database Service Name

If the Service Name option is used to connect, obtain the service name as shown in the second article ("Using Oracle JDeveloper 12c with Oracle Database 12c on Oracle Cloud Platform, Part 2"), Figure 11, and specify in the Service Name field, as shown in Figure 1 (this article). All the other settings are the same as when using SID. Click Test Connection; the Success! message gets displayed if a connection gets established, as shown in Figure 1.

Testing the Connection with Service Name
Figure 1: Testing the Connection with Service Name

After a connection has been configured and tested, click OK, as shown in Figure 2, to create the connection. Only one of the options, SID or Service Name, must be selected.

Create Database Connection>OK
Figure 2: Create Database Connection>OK

In the Resources view, click IDE Connections node to list the connections, as shown in Figure 3.

Resources>IDE Connections
Figure 3: Resources>IDE Connections

The ORCLConnection gets listed, as shown in Figure 4, and its database objects start to get loaded.

Resources>IDE Connections>Database>ORCLConnection
Figure 4: Resources>IDE Connections>Database>ORCLConnection

The database objects including database links, directories, functions, packages, procedures, tables, indexes and views get listed, as shown in Figure 5.

Database Objects for the ORCLConnection
Figure 5: Database Objects for the ORCLConnection

Creating a Connection with Oracle Database Service Created by Using the Detailed Wizard

The procedure to create a database connection with JDeveloper if the Oracle Database service instance is created using the detailed wizard is the same as when the service instance is created by using the quick start wizard. In New Gallery, select File>New>Application or File>New>From Gallery and select General>Connections in Categories and Database Connection in Items. Click OK in New Gallery. In Create Database Connection, specify a Connection Name (ORCLDBConnection) and select Connection Type as Oracle (JDBC) and specify Username as SYS.

Specify Passwordas the password configured when creating the service instance. Because the SYS connection must be SYSDBA, select the Role as SYSDBA (see Figure 6). Optionally, select the Save Password checkbox. Select Driver as thin and specify Host Name as Public IP obtained from service console as listed in Figure 8 of the second article ("Using Oracle JDeveloper 12c with Oracle Database 12c on Oracle Cloud Platform, Part 2"). Next, we shall discuss connecting using Oracle SID and using the Oracle Service name.

Using Oracle SID

If SID is to be used for connecting to the Oracle Database, select the SID radio button and specify the value as ORCL, as shown in Figure 6. Click Test Connection; a Success! message indicates that the connection is established.

Testing Connection with SID
Figure 6: Testing Connection with SID

Using Oracle Database Service Name

If a service name is to be used for connecting to Oracle Database, select the Service Name radio button and specify the value as the Connect String substring obtained in Figure 9 of "Using Oracle JDeveloper 12c with Oracle Database 12c on Oracle Cloud Platform, Part 2". Click Test Connection; a Success! message indicates that the connection is established, as shown in Figure 7.

Testing Connection with Service Name
Figure 7: Testing Connection with Service Name

After having configured a connection, click OK to complete the connection, as shown in Figure 8. Only one of the two options—SID or Service Name—can be selected at a time, not both.

Create Database Connection>OK
Figure 8: Create Database Connection>OK

A new connection (ORCLDBConnection) gets added in Resources>IDE Connections>Database, as shown in Figure 9.

Resources>IDE Connections>Database>ORCLDBConnection
Figure 9: Resources>IDE Connections>Database>ORCLDBConnection

The database objects—including database links, directories, functions, tables, and tablespaces—are shown in Figure 10.

Database Objects
Figure 10: Database Objects

Creating a Database Table and Adding Table Data

In this section, we shall create a database table using one of the connections. To create a database table, we shall run a SQL script. Click SQL Worksheet in the toolbar (see Figure 11).

SQL Worksheet
Figure 11: SQL Worksheet

A Loading Features dialog gets displayed and the required features to create a SQL Worksheet get loaded. A Select Connection dialog gets displayed. Select a connection from the drop-down list, as shown in Figure 12.

Select Connection>Connection
Figure 12: Select Connection>Connection

Click OK, as shown in Figure 13.

Select Connection>OK
Figure 13: Select Connection>OK

A SQL Worksheet gets opened. Copy and paste the following SQL script in the SQL Worksheet.

CREATE TABLE wlslog(time_stamp VARCHAR2(255) PRIMARY KEY,category
   VARCHAR2(255),type VARCHAR2(255),servername VARCHAR2(255),
   code VARCHAR2(255),msg VARCHAR2(255));
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg)
   VALUES('Apr-8-2014-7:06:16-PM-PDT','Notice','WebLogicServer',
   'AdminServer','BEA-000365','Server state changed to STANDBY');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg)
   VALUES('Apr-8-2014-7:06:17-PM-PDT','Notice','WebLogicServer',
   'AdminServer','BEA-000365','Server state changed to STARTING');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg)
   VALUES('Apr-8-2014-7:06:18-PM-PDT','Notice','WebLogicServer',
   'AdminServer','BEA-000365','Server state changed to ADMIN');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg)
   VALUES('Apr-8-2014-7:06:19-PM-PDT','Notice','WebLogicServer',
   'AdminServer','BEA-000365','Server state changed to RESUMING');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg)
   VALUES('Apr-8-2014-7:06:20-PM-PDT','Notice','WebLogicServer',
   'AdminServer','BEA-000361','Started WebLogic AdminServer');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg)
   VALUES('Apr-8-2014-7:06:21-PM-PDT','Notice','WebLogicServer',
   'AdminServer','BEA-000365','Server state changed to RUNNING');
INSERT INTO wlslog(time_stamp,category,type,servername,code,msg)
   VALUES('Apr-8-2014-7:06:22-PM-PDT','Notice','WebLogicServer',
   'AdminServer','BEA-000360','Server started in RUNNING mode');

Click Run Script, as shown in Figure 14, to run the SQL script.

Run Script
Figure 14: Run Script

A database table gets created and data gets added, as shown by Script Output in Figure 15.

Script Output
Figure 15: Script Output

Querying a Database Table

Click SQL Worksheet (see Figure 16) to open another SQL worksheet for the SQL script to run a query.

SQL Worksheet
Figure 16: SQL Worksheet

Select a connection in the Select Connection dialog, as shown in Figure 17.

Select Connection
Figure 17: Select Connection

A new SQL Worksheet gets opened. Specify a SELECT statement for the wlslog table in the SQL worksheet, as shown in Figure 18. Table details, such as columns and column types, may be obtained from the table structure, if required, by selecting the table in Resources to list the Columns in Structure, as shown in Figure 18.

Listing Columns for Table WLSLOG
Figure 18: Listing Columns for Table WLSLOG

Click Run Statement to run the SQL statement SELECT * FROM WLSLOG;, as shown in Figure 19.

Run Statement
Figure 19: Run Statement

A Query Result gets displayed, as shown in Figure 20.

Query Result
Figure 20: Query Result

Disconnecting From or Removing a Connection

A connection may be disconnected or removed when not being used or not required. Select the connection (ORCLConnection, as an example) to be disconnected in Resources (see Figure 21).

Selecting a Connection
Figure 21: Selecting a Connection

Right-click the connection and select Disconnect, as shown in Figure 22, to disconnect the connection.

Disconnect
Figure 22: Disconnect

A Save Files dialog prompt gets displayed to save the connection settings before disconnecting. Click Yes to save files and No to not save files, and the connection gets disconnected. To delete a connection, right-click a connection and select Delete, as shown in Figure 23.

Delete
Figure 23: Delete

A Delete Confirmation gets displayed, as shown in Figure 24. Click Yes.

Delete Confirmation
Figure 24: Delete Confirmation

The connection gets deleted.

Removing an Oracle Database Cloud Service

To delete an Oracle Database Cloud Service instance, click the icon to manage a service instance and select Delete, as shown in Figure 25.

Delete
Figure 25: Delete

Click Delete in Delete Service dialog (see Figure 26).

Delete Service
Figure 26: Delete Service

The service instance starts to get deleted, as shown in Figure 27. Similarly, the other service instance may be deleted.

Deleting Service Instance
Figure 27: Deleting Service Instance

Conclusion

In three articles, we discussed connecting to an Oracle Database service instance on Oracle Cloud Platform with Oracle JDeveloper 12c. First, we created the Oracle Database service instances by using each of the Create Instance options: Quick Start wizard and detailed wizard. Next, we enabled the access rules to allow access to the database service instances. Then, we obtained the connection parameters for the database service instances from the service console. Subsequently, we connected to Oracle Database service instances on Oracle Cloud Platform with Oracle JDeveloper using Oracle SID and Oracle Service name. We also used 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.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

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