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
- Displaying a Single Record View
- Counting Rows in a Query Result
- Finding and Highlighting Data
- Exporting the Query Result
- Running SQL*Plus
- Dropping a Table
- Conclusion
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.
Figure 1: Running an SQL Statement
The query result gets displayed, as shown in Figure 2.
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.
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.
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.
Figure 5: Navigating to the last record view
The last record view gets displayed (see Figure 6).
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.
Figure 7: Count Rows…
The Row Count dialog lists the number of rows as 7 Rows, as shown in Figure 8.
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.
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.
Figure 10: Find/Highlight
The data row with the search phrase gets highlighted (see Figure 11).
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.
Figure 12: Persist Highlight Option
The data row that includes the search phrase gets highlighted as before, as shown in Figure 13.
Figure 13: Search Result highlighted
Click the x to close the Find/Highlight dialog, as shown in Figure 14.
Figure 14: Closing Find/Highlight dialog
The highlighted row stays highlighted, as shown in Figure 15.
Figure 15: Highlighted row stays highlighted
To delete the persisted settings, click Delete Persisted Settings (see Figure 16).
Figure 16: Delete Persisted Settings
In the confirmation dialog, click OK, as shown in Figure 17.
Figure 17: Delete Persisted Settings Confirmation dialog
The persisted settings get removed, as shown in Figure 18.
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.
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.
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.
Figure 21: Configuring Source/Destination
In Summary, click Finish. An Excel spreadsheet gets exported. The exported file is shown in Figure 22.
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.
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.
Figure 24: Exporting to HTML
The HTML file exported is shown in Figure 25.
Figure 25: Exported HTML
To export to a PDF file, select Format as pdf (see Figure 26).
Figure 26: Exporting to PDF
The PDF exported to is shown in Figure 27.
Figure 27: PDF exported
The PDF is exported as two files; the 2nd PDF file is shown in Figure 28.
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.
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.
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).
Figure 31: SQL*Plus Location dialog>OK
The SQL*Plus tool gets launched, as shown in Figure 32.
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.
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.
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.
Figure 35: Running a DROP Table Statement
Right-click Tables in Resources, and select Refresh (see Figure 36).
Figure 36: Tables>Refresh
The WLSLOG table gets deleted, and is not listed in Tables in Figure 37.
Figure 37: Table Deleted
Conclusion
In two tutorials, we discussed using Oracle JDeveloper 12c with Oracle Database.