Architecture & DesignUsing Oracle JDeveloper SQL Worksheets

Using Oracle JDeveloper SQL Worksheets

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Working with MySQL databases is made easy when using a tool like JDeveloper. If you are not familiar with setting up and configuring JDeveloper to access a MySQL table, can start by reading “Using MySQL Databases in Oracle JDeveloper.” Once you have a connection to a MySQL database, you are ready to take the next step of using JDeveloper Worksheets.

In this tutorial, you will build on the database built in the previous article. You’ll learn not only what Worksheets are, but how to start using them today. This tutorial has the following sections:

What Are SQL Worksheets?

SQL Worksheets are used to run SQL statements. They can be used to run DML/DDL SQL statements to create, update, and delete database tables, as well as to add table data and run SQL queries, all using SQL syntax and SQL object auto suggestion.

Using a SQL Worksheet

The best way to understand SQL Worksheets is to use them. One way to create a Worksheet is to start Oracle JDeveloper and select Tools>Database>SQL Worksheet, as shown in Figure 1.

Tools>Database>SQL Worksheet
Figure 1: Tools>Database>SQL Worksheet

This will display the Select Connection window, as shown in Figure 2.

Selecting a connection
Figure 2: Selecting a connection

From the Select Connection window, select the MySQLConnection a blank SQL Worksheet gets created (see Figure 3).

New SQL Worksheet
Figure 3: New SQL Worksheet

A Worksheet also may be created by selecting the SQL Worksheet icon from the JDeveloper toolbar, as shown in Figure 4.

Selecting SQL Worksheet from the toolbar
Figure 4: Selecting SQL Worksheet from the toolbar

Sharing Versus Not Sharing SQL Worksheets

The preceding two options to create a new SQL Worksheet create a shared SQL Worksheet that may subsequently be used with a specific SQL connection by selecting a connection, MySQLConnection, as an example.

To create an Unshared SQL Worksheet, click Unshared SQL Worksheet with the connection (MySQLConnection) already selected, as shown in Figure 5.

Unshared SQL Worksheet
Figure 5: Unshared SQL Worksheet

Creating a Table Using the SQL Worksheet

For the examples in this tutorial, the database created in the previous article (called mysql) is going to be used. The article, “Using MySQL Databases in Oracle JDeveloper,” shows how to create and connect to that MySQL database from within JDeveloper. Once you’ve created the database, you can use the SQL Worksheet to create your tables.

To create the wlslog table, you can enter the following SQL statement into the query builder area of the window shown in Figure 3:

CREATE TABLE wlslog(time_stamp VARCHAR(255)
   PRIMARY KEY,category VARCHAR(255),type VARCHAR(255),
   servername VARCHAR(255), code VARCHAR(255),msg VARCHAR(255));

If you then click the Run button, you should get a message that the table is created (see Figure 6). If the table already exists, you’ll be told that is well.

Creating a table in a SQL Worksheet
Figure 6: Creating a table in a SQL Worksheet

Adding Data Via a SQL Worksheet

With a table created, you now are able to add rows of data. Add a DML SQL statement to add a row of data to the wlslog table:

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');

Click Run Statement to run the SQL statement, as shown in Figure 7. As the message in the Script Output indicates, “1 row inserted.”

Run Statement
Figure 7: Run Statement

Running Statements in a SQL Worksheet

A SQL Worksheet can contain multiple SQL Statements. You then can choose to run one or all of those statements. As an example, add the following SQL statements in the SQL Worksheet.

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');

To run a single SQL Statement, position the cursor before the SQL Statement you want to execute, as shown in Figure 8.

Selecting a single SQL Statement
Figure 8: Selecting a single SQL Statement

Click Run Statement to run the single SQL statement, as shown in Figure 9.

SQL Worksheet Run Statement
Figure 9: SQL Worksheet Run Statement

To run the complete SQL script, right-click anywhere in the SQL Worksheet and select Run Script, as shown in Figure 10.

Run Script
Figure 10: Run Script

The Run Script option also is available in the Unshared SQL Worksheet toolbar (see Figure 11).

The Run Script icon in the toolbar
Figure 11: The Run Script icon in the toolbar

If you entered the SQL statements earlier, when you select Run Script, you will see that multiple SQL statements execute, and multiple rows of data get added, as shown in Figure 12.

Adding multiple rows
Figure 12: Adding multiple rows

Saving SQL Workbook Script Output

You can save the output from the scripts that you execute. The SQL Script output may be saved with the Save File, as shown in Figure 13. Another option available is Run Script Output as a Script.

Save File
Figure 13: Save File

Running SQL Queries in a SQL Worksheet

Next, we shall run an SQL query in a Worksheet. Create a new SQL Worksheet, as discussed earlier in the “Using a SQL Worksheet” section. In this new SQL Worksheet, add the following statement:

SELECT * from wlslog

Click to run this statement, as you were shown earlier. The Query Result output should be displayed, as shown in Figure 14.

Query Result
Figure 14: Query Result

Customizing SQL Worksheet Display Results

The display of the query result can be customized. To customize the columns, right-click and select Columns, as shown in Figure 15.

Selecting columns for customization of query result
Figure 15: Selecting columns for customization of query result

You should note that the order in which the columns are displayed is just one example of customization. Selecting Columns will present you with the window shown in Figure 16. You then can select a column and click the up/down arrows to change the column order.

Reordering columns
Figure 16: Reordering columns

Click OK after reordering the columns to save your changes.

The reordered columns are shown in Figure 17.

Reordered columns
Figure 17: Reordered columns

In addition to changing the order, the columns also can be auto-fit with the Auto-fit All Columns and Auto-fit Selected Column options (see Figure 18).

Auto-fit Columns
Figure 18: Auto-fit Columns

The settings column widths, column sorts, and column positions are only for customization of the query result display and may be deleted by selecting Delete Persisted Settings, as shown in Figure 19.

Delete Persisted Settings
Figure 19: Delete Persisted Settings

The Delete Persisted Settings option also may be selected from the Query Result toolbar, as shown in Figure 20.

Delete Persisted Settings in Query Result toolbar
Figure 20: Delete Persisted Settings in Query Result toolbar

What’s Next?

In this tutorial, you learned how to use SQL Worksheets in Oracle JDeveloper to manipulate your MySQL databases. In the next tutorial, “Using Oracle JDeveloper Snippets with MySQL,” you’ll learn how to work with SQL Snippets in JDeveloper.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories