Architecture & DesignUsing Oracle JDeveloper 12c with Oracle Database, Part 2

Using Oracle JDeveloper 12c with Oracle Database, Part 2

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

Oracle JDeveloper and Oracle Database provide an integral environment for managing a database, including creating database tables. In the first of two tutorials, “Using Oracle JDeveloper 12c with Oracle Database, Part 1,” we created a database connection to Oracle Database in JDeveloper, created a database table, and added table data. In this continuation tutorial, we shall query the database table and discuss some of the query-related features. This tutorial has the following sections:

Querying a Table

To query a table, run a SQL statement in a SQL Worksheet, which is launched by selecting Tools>Database>SQL Worksheet. Add a query statement—for example, SELECT * FROM WLSLOG—in the SQL Worksheet, and click Run Statement, as shown in Figure 1.

Running an SQL Statement
Figure 1: Running an SQL Statement

The query result gets displayed, as shown in Figure 2.

Query Result
Figure 2: Query Result

Displaying a Single Record View

The query result is displayed as a table. To display a single data record, right-click in the data record row and select Single Record View…, as shown in Figure 3.

Single Record View…
Figure 3: Single Record View…

The Single Record View gets displayed, as shown in Figure 4. The single record view dialog includes Next (>) and Previous (<) arrows to navigate to the next and previous data respectively.

Single Record View
Figure 4: Single Record View

The Single Record View also includes Last (>>) and First (<<) arrows to navigate to the last and first records respectively, as shown in Figure 5.

Navigating to the last record view
Figure 5: Navigating to the last record view

The last record view gets displayed (see Figure 6).

Last record view
Figure 6: Last record view

Counting Rows in a Query Result

To count the number of rows in a query result, right-click and select Count Rows…, as shown in Figure 7.

Count Rows…
Figure 7: Count Rows…

The Row Count dialog lists the number of rows as 7 Rows, as shown in Figure 8.

Row Count
Figure 8: Row Count

Finding and Highlighting Data

The example query result set used has only 7 rows; finding data is easy, but if the data set had several rows, thousands of rows as an example, finding a specific data becomes much more difficult. To find and highlight data, right-click in the query result and select Find/Highlight…, as shown in Figure 9.

Find/Highlight…
Figure 9: Find/Highlight…

The Find/Highlight dialog gets displayed, as shown in Figure 10. Search for a term or phrase in the query result. As an example, search for “Server state changed to running” by adding the phrase in the search field. Several options are provided from which you will select those needed, Ignore Case as an example. Also, select the option Highlight Row to highlight the row. The color used in highlighting data is also displayed. Position the cursor in the search field and select the Enter key to start the search & highlight.

Find/Highlight
Figure 10: Find/Highlight

The data row with the search phrase gets highlighted (see Figure 11).

Highlighted Row
Figure 11: Highlighted Row

If the Find/Highlight dialog is closed, the row/s highlighting is removed. The Find/Highlight tool includes an option to persist the highlighting. Select the Persist Highlight option, as shown in Figure 12.

Persist Highlight Option
Figure 12: Persist Highlight Option

The data row that includes the search phrase gets highlighted as before, as shown in Figure 13.

Search Result highlighted
Figure 13: Search Result highlighted

Click the x to close the Find/Highlight dialog, as shown in Figure 14.

Closing Find/Highlight dialog
Figure 14: Closing Find/Highlight dialog

The highlighted row stays highlighted, as shown in Figure 15.

Highlighted row stays highlighted
Figure 15: Highlighted row stays highlighted

To delete the persisted settings, click Delete Persisted Settings (see Figure 16).

Delete Persisted Settings
Figure 16: Delete Persisted Settings

In the confirmation dialog, click OK, as shown in Figure 17.

Delete Persisted Settings Confirmation dialog
Figure 17: Delete Persisted Settings Confirmation dialog

The persisted settings get removed, as shown in Figure 18.

Persisted settings removed
Figure 18: Persisted settings removed

Exporting the Query Result

In this section, we shall export the query result to some of the supported output formats, including Excel spreadsheet, PDF, HTML, and CSV. To export a query result, right-click in the query result grid and select Export…, as shown in Figure 19.

Export…
Figure 19: Export…

The Export Wizard gets launched, as shown in Figure 20. Select a Format from the drop-down. Select Excel 2003+, as an example.

Selecting an Output Format
Figure 20: Selecting an Output Format

Next, specify a Data Worksheet Name (WLSLOG) (see Figure 21). Select Save As Single File and keep the default Encoding. Specify an output File, and click Next.

Configuring Source/Destination
Figure 21: Configuring Source/Destination

In Summary, click Finish. An Excel spreadsheet gets exported. The exported file is shown in Figure 22.

Exported Excel Spreadsheet
Figure 22: Exported Excel Spreadsheet

The query result may be exported to excel.xml by selecting Format as excel.xml, as shown in Figure 23. Click Next and subsequently click Finish.

Exporting to excel.xml
Figure 23: Exporting to excel.xml

The excel.xml file exported is listed:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns_c="urn:schemas-microsoft-com:office:component:
      spreadsheet"
   xmlns_html="http://www.w3.org/TR/REC-html40"
   xmlns_o="urn:schemas-microsoft-com:office:office"
   xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance"
   
   xmlns_x2="http://schemas.microsoft.com/office/excel/2003/xml"
   xmlns_ss="urn:schemas-microsoft-com:office:spreadsheet"
   xmlns_x="urn:schemas-microsoft-com:office:excel">
   <OfficeDocumentSettings >
      <Colors>
         <Color>
            <Index>3</Index>
            <RGB>#c0c0c0</RGB>
         </Color>
         <Color>
            <Index>4</Index>
            <RGB>#ff0000</RGB>
         </Color>
      </Colors>
   </OfficeDocumentSettings>
   <ExcelWorkbook >
      <WindowHeight>9000</WindowHeight>
      <WindowWidth>13860</WindowWidth>
      <WindowTopX>240</WindowTopX>
      <WindowTopY>75</WindowTopY>
      <ProtectStructure>False</ProtectStructure>
      <ProtectWindows>False</ProtectWindows>
   </ExcelWorkbook>
   <Styles>
      <Style ss_ID="Default" ss_Name="Default"/>
      <Style ss_ID="Result" ss_Name="Result">
         <Font ss_Bold="1" ss_Italic="1" ss_Underline="Single"/>
      </Style>
      <Style ss_ID="Result2" ss_Name="Result2">
         <Font ss_Bold="1" ss_Italic="1" ss_Underline="Single"/>
         <NumberFormat ss_Format="Currency"/>
      </Style>
      <Style ss_ID="Heading" ss_Name="Heading">
         <Font ss_Bold="1" ss_Italic="1" ss_Size="16"/>
      </Style>
      <Style ss_ID="Heading1" ss_Name="Heading1">
         <Font ss_Bold="1" ss_Italic="1" ss_Size="16"/>
      </Style>
      <Style ss_ID="co1"/>
      <Style ss_ID="co2"/>
      <Style ss_ID="ta1"/>
      <Style ss_ID="ce1">
         <NumberFormat ss_Format="Short Date"/>
      </Style>
      <Style ss_ID="ce2">
         <NumberFormat ss_Format="General Date"/>
      </Style>
      <Style ss_ID="ce3">
         <NumberFormat ss_Format="General Date"/>
      </Style>
      <Style ss_ID="ce4">
         <NumberFormat ss_Format="Long Time"/>
      </Style>
   </Styles>
   <ss:Worksheet ss_Name="Sheet 1 Name">
      <Table ss_StyleID="ta1">
         <Row ss_Height="12.384">
            <Cell>
               <Data ss_Type="String">TIME_STAMP</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">CATEGORY</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">TYPE</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">SERVERNAME</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">CODE</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">MSG</Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss_Type="String">
                  Apr-8-2014-7:06:16-PM-PDT
               </Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">Notice</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">WebLogicServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">AdminServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">BEA-000365</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">
                  Server state changed to STANDBY
               </Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss_Type="String">
                  Apr-8-2014-7:06:17-PM-PDT
               </Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">Notice</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">WebLogicServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">AdminServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">BEA-000365</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">
                  Server state changed to STARTING
               </Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss_Type="String">
                  Apr-8-2014-7:06:18-PM-PDT
               </Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">Notice</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">WebLogicServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">AdminServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">BEA-000365</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">
                  Server state changed to ADMIN
               </Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss_Type="String">
                  Apr-8-2014-7:06:19-PM-PDT
               </Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">Notice</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">WebLogicServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">AdminServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">BEA-000365</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">
                  Server state changed to RESUMING
               </Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss_Type="String">
                  Apr-8-2014-7:06:20-PM-PDT
               </Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">Notice</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">WebLogicServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">AdminServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">BEA-000361</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">
                  Started WebLogic AdminServer
               </Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss_Type="String">
                  Apr-8-2014-7:06:21-PM-PDT
               </Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">Notice</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">WebLogicServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">AdminServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">BEA-000365</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">
                  Server state changed to RUNNING
               </Data>
            </Cell>
         </Row>
         <Row>
            <Cell>
               <Data ss_Type="String">
                  Apr-8-2014-7:06:22-PM-PDT
               </Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">Notice</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">WebLogicServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">AdminServer</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">BEA-000360</Data>
            </Cell>
            <Cell>
               <Data ss_Type="String">
                  Server started in RUNNING mode
               </Data>
            </Cell>
         </Row>
      </Table>
      <x:WorksheetOptions/>
   </ss:Worksheet>
</Workbook>

To export to HTML, select Format as html, as shown in Figure 24. Click Next and subsequently Finish.

Exporting to HTML
Figure 24: Exporting to HTML

The HTML file exported is shown in Figure 25.

Exported HTML
Figure 25: Exported HTML

To export to a PDF file, select Format as pdf (see Figure 26).

Exporting to PDF
Figure 26: Exporting to PDF

The PDF exported to is shown in Figure 27.

PDF exported
Figure 27: PDF exported

The PDF is exported as two files; the 2nd PDF file is shown in Figure 28.

The 2nd PDF File
Figure 28: The 2nd PDF File

Running SQL*Plus

The SQL*Plus tool may be launched from JDeveloper by selecting Tools>Database>SQL*Plus, as shown in Figure 29.

Tools>Database>SQL*Plus
Figure 29: Tools>Database>SQL*Plus

In the SQL*Plus Location dialog, click Browse to select the sqlplus.exe application, as shown in Figure 30.

Selecting SQL*Plus Location
Figure 30: Selecting SQL*Plus Location

In the Open dialog, select the sqlplus.exe application. With the sqlplus.exe selected, click OK in the SQL*Plus Location dialog (see Figure 31).

SQL*Plus Location dialog>OK
Figure 31: SQL*Plus Location dialog>OK

The SQL*Plus tool gets launched, as shown in Figure 32.

SQL*Plus Tool
Figure 32: SQL*Plus Tool

Specify the password to log in to SQL*Plus. The SQL command prompt gets displayed, as shown in Figure 33.

SQL Command Prompt
Figure 33: SQL Command Prompt

Run a SQL*Plus statement, such as DESC SYS.WLSLOG, to display the table description, as shown in Figure 34.

Listing table description
Figure 34: Listing table description

Dropping a Table

To drop a table, run the DROP Table statement in a SQL Worksheet, as shown in Figure 35.

Running a DROP Table Statement
Figure 35: Running a DROP Table Statement

Right-click Tables in Resources, and select Refresh (see Figure 36).

Tables>Refresh
Figure 36: Tables>Refresh

The WLSLOG table gets deleted, and is not listed in Tables in Figure 37.

Table Deleted
Figure 37: Table Deleted

Conclusion

In two tutorials, we discussed using Oracle JDeveloper 12c with Oracle Database.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories