Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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
- Creating a Connection with Oracle Database Service Created by Using the Detailed Wizard
- Using Oracle SID
- Using Oracle Database Service Name
- Creating a Database Table and Adding Table Data
- Querying a Database Table
- Disconnecting From or Removing a Connection
- Removing an Oracle Database Cloud Service
- Conclusion
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.
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.
Figure 2: Create Database Connection>OK
In the Resources view, click IDE Connections node to list the connections, as shown in Figure 3.
Figure 3: Resources>IDE Connections
The ORCLConnection gets listed, as shown in Figure 4, and its database objects start to get loaded.
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.
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.
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.
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.
Figure 8: Create Database Connection>OK
A new connection (ORCLDBConnection) gets added in Resources>IDE Connections>Database, as shown in Figure 9.
Figure 9: Resources>IDE Connections>Database>ORCLDBConnection
The database objects—including database links, directories, functions, tables, and tablespaces—are shown in Figure 10.
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).
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.
Figure 12: Select Connection>Connection
Click OK, as shown in Figure 13.
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.
Figure 14: Run Script
A database table gets created and data gets added, as shown by Script Output in Figure 15.
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.
Figure 16: SQL Worksheet
Select a connection in the Select Connection dialog, as shown in Figure 17.
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.
Figure 18: Listing Columns for Table WLSLOG
Click Run Statement to run the SQL statement SELECT * FROM WLSLOG;, as shown in Figure 19.
Figure 19: Run Statement
A Query Result gets displayed, as shown in Figure 20.
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).
Figure 21: Selecting a Connection
Right-click the connection and select Disconnect, as shown in Figure 22, to disconnect the connection.
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.
Figure 23: Delete
A Delete Confirmation gets displayed, as shown in Figure 24. Click Yes.
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.
Figure 25: Delete
Click Delete in Delete Service dialog (see Figure 26).
Figure 26: Delete Service
The service instance starts to get deleted, as shown in Figure 27. Similarly, the other service instance may be deleted.
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.
