Using Oracle JDeveloper 12c with Oracle Database, Part 1

Oracle JDeveloper is an integrated development environment (IDE) for Java. JDeveloper also provides support for several other features, including the Application Development Framework (ADF), JDBC database connectivity, and XML processing. Oracle Database is the most commonly used relational database management system (RDBMS). In two tutorials, we shall discuss using JDeveloper with Oracle Database 11g. This tutorial has the following sections:

• Setting the Environment
• Configuring Connection Parameters
• Creating a Connection
• Setting AutoCommit
• Creating a Table
• Adding Table Data
• Conclusion

Setting the Environment

Download and install the following software:

Oracle JDeveloper 12c

Oracle Database 11g

Set or modify the environment variables listed in Table 1.

Environment Variable	Definition	Value
ORACLE_HOME	The directory in which Oracle Database is installed. The directory would be different based on the directory in which Oracle Database is installed and also based on the Oracle Database version used.	C:\oraclexe\app\oracle\product\11.2.0\server
TNS_ADMIN	The directory in which the network admin configuration files (LISTENER.ora, sqlnet.ora, and tnsnames.ora) are located.	C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
PATH	Add the Oracle Database 11g bin directory. The bin directory has the SQL*Plus command line client application sqlplus.exe.	C:\oraclexe\app\oracle\product\11.2.0\server\bin

Table 1: Environment Variables

Configuring Connection Parameters

In addition to setting the environment variables listed in the preceding section, we need to configure the network admin configuration files (LISTENER.ora and tnsnames.ora). To the tnsnames.ora file, add a service descriptor for the Oracle Database 11g XE service as follows.

XE=(description=
   (address=(protocol=tcp)(port=1521)(host=localhost))
   (connect_data=(service_name=XE)))

To the listener.ora file, add a listener configuration as follows.

SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
         (SID_NAME = PLSExtProc)
         (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0
            \server)
         (PROGRAM = extproc)
      )
   (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
      (PROGRAM = extproc)
   )
)

LISTENER =
   (DESCRIPTION_LIST =
      (DESCRIPTION =
         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
         (ADDRESS = (PROTOCOL = TCP)(HOST = dvohra-PC)(PORT =
            1521))
      )
   )

DEFAULT_SERVICE_LISTENER = (XE)

Creating a Connection

Having installed the software, set the environment variables, and configured the network configuration files, next we shall create a connection in JDeveloper. Launch JDeveloper and select a role from the Select Role window, as shown in Figure 1. The Studio Developer role includes all features, but if only database connectivity is to be used, select the Database Developer, role as shown in Figure 1.

Figure 1: Selecting the Database Developer Role

To create a new database connection, launch the Create Database Connection wizard. Several options are available to launch the connection wizard. One option is select New Application, as shown in Figure 2.

Figure 2: Selecting New Application

The New Application launches the New Gallery wizard, as shown in Figure 3. Select General>Connections in Categories and Database Connection in Items.

Figure 3: Selecting Database Connection in New Gallery Wizard

Some of the other options to launch the New Gallery wizard include selecting File>New>Application, as shown in Figure 4. Alternatively, select File>New>From Gallery…, also shown in Figure 4.

Figure 4: Selecting File>New>Application

On selecting Database Connection in New Gallery, the Create Database Connection wizard gets launched, as shown in Figure 5. Specify a Connection Name (OracleDBConnection) and select Connection Type as Oracle (JDBC). Specify Username as SYS and also specify the Password for the SYS user. The SYS user must connect as SYSDBA role. Select Role as SYSDBA.

Figure 5: Create Database Connection Wizard

Select the default Oracle (JDBC) Settings, which include Driver as thin, Host Name as localhost, SID as XE and JDBC Port as 1521. Click Test Connection (see Figure 6) to test the connection.

Figure 6: Test Connection

The output from Test Connection should be Success, as shown in Figure 7. Click OK to complete the connection.

Figure 7: Completing Connection Configuration

A new connection gets created, as shown in Figure 8.

Figure 8: New Connection added

Setting AutoCommit

The auto-commit feature commits all database transactions automatically, without requiring an explicit commit. The auto commit is enabled by default. The auto commit setting is accessed/configured by selecting Tools>Preferences, as shown in Figure 9.

Figure 9: Selecting Tools>Preferences

In the Preferences dialog, select Database>Advanced, as shown in Figure 10, and select the Autocommit checkbox if not already selected.

Figure 10: Autocommit setting

Creating a Table

Next, create an example database table. Two options are available to create a new table:

• Run a SQL Script in a SQL Worksheet
• Use the New Table wizard

We shall use the New Table wizard. The Tables node for the connection lists the tables in the database (see Figure 11).

Figure 11: Tables

To create a new table, right-click the Tables node and select New Table, as shown in Figure 12.

Figure 12: Tables>New Table

The Create Table wizard gets launched, as shown in Figure 13.

Figure 13: Create Table wizard

Next, create a new table called WLSLOG with columns TIME_STAMP,CATEGORY,TYPE,SERVERNAME,CODE and MSG. Modify the default column COLUMN1 to TIME_STAMP, as shown in Figure 14, and set the column as a primary key column. Select Data Type as VARCHAR2 and set Size (255). Select the Not Null checkbox to make the column as not nullable. Click Add Column, as shown in Figure 14, to add another column.

Figure 14: Adding a Column

Add one column at a time. All the columns added are shown in Figure 15. Click OK.

Figure 15: All columns added

The DDL tab displays the DDL used to create the new table (see Figure 16). OK may be selected from either the Table tab or the DDL tab.

Figure 16: DDL

The new table WLSLOG gets created. The new table gets listed in the Tables node. To list only the new table, right-click Tables and select Filter, as shown in Figure 17.

Figure 17: Selecting Tables>Filter

In Filter Tables, specify Filter as WLSLOG% and click OK, as shown in Figure 18.

Figure 18: Filter Tables

The WLSLOG table gets listed, as shown in Figure 19.

Figure 19: WLSLOG table

Double-click the WLSLOG table to display its Structure, as shown in Figure 20.

Figure 20: WLSLOG table Structure

Adding Table Data

Next, add the data listed to the WLSLOG table.

Apr-8-2014-7:06:16-PM-PDT Notice WebLogicServer AdminServer
   BEA-000365 Server state changed to STANDBY
Apr-8-2014-7:06:17-PM-PDT Notice WebLogicServer AdminServer
   BEA-000365 Server state changed to STARTING
Apr-8-2014-7:06:18-PM-PDT Notice WebLogicServer AdminServer
   BEA-000365 Server state changed to ADMIN
Apr-8-2014-7:06:19-PM-PDT Notice WebLogicServer AdminServer
   BEA-000365 Server state changed to RESUMING
Apr-8-2014-7:06:20-PM-PDT Notice WebLogicServer AdminServer
   BEA-000331 Started WebLogic AdminServer
Apr-8-2014-7:06:21-PM-PDT Notice WebLogicServer AdminServer
   BEA-000365 Server state changed to RUNNING
Apr-8-2014-7:06:22-PM-PDT Notice WebLogicServer AdminServer
   BEA-000360 Server started in RUNNING mode

To add data, we shall run a SQL script in a SQL Worksheet. Start a new SQL Worksheet by selecting Tools>Database>SQL Worksheet (see Figure 21).

Figure 21: Tools>Database>SQL Worksheet

In the Select Connection dialog, select the OracleDBConnection, as shown in Figure 22, and click OK.

Figure 22: Selecting Connection

Copy the following SQL script to the SQL Worksheet.

INSERT INTO wlslog(timestamp,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(timestamp,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(timestamp,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(timestamp,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(timestamp,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(timestamp,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(timestamp,category,type,servername,code,msg)
   VALUES('Apr-8-2014-7:06:22-PM-PDT','Notice','WebLogicServer',
   'AdminServer','BEA-000360','Server started in RUNNING mode');

The SQL statements may be run one at a time or all together. To run one statement at a time, position the cursor before the statement and click Run Statement, as shown in Figure 23.

Figure 23: Run Statement

We shall run the SQL script instead of running one statement at a time. Click Run Script, as shown in Figure 24.

Figure 24: Run Script

Data gets added to table as indicated by the Script Output, which lists a "1 row inserted" message for each row added, as shown in Figure 25.

Figure 25: Script Output indicates that data has been added

If auto commit were not enabled, we would need to commit the transaction with Commit (see Figure 26). But, because auto commit is enabled, Commit is not to be clicked.

Figure 26: Click Commit if auto commit is not enabled

To rollback a transaction, click Rollback, as shown in Figure 27.

Figure 27: Rollback

The clear the script output, click Clear, as shown in Figure 28.

Figure 28: Script Output>Clear

Conclusion

In this first of two tutorials, we introduced using Oracle JDeveloper 12c with Oracle Database by creating a connection in JDeveloper, creating a database table, and adding table data. In the 2^nd tutorial, we shall discuss running a SQL query, using query result set features, dropping a table, and deleting a connection.