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:
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 2nd tutorial, we shall discuss running a SQL query, using query result set features, dropping a table, and deleting a connection.
