In an earlier article, “Using MySQL Databases in Oracle JDeveloper,” you learned to use JDeveloper with MySQL databases. In this tutorial, you’ll learn about another feature in JDeveloper that can be used with MySQL: Snippets.
In this tutorial, the following topics will be covered:
- What Are SQL Snippets?
- Using a SQL Snippet
- Adding a New Snippet
- Editing a SQL Snippet
- Where Do We Go From Here?
What Are SQL Snippets?
JDeveloper provides built-in SQL snippets that may be added to a SQL Worksheet to create SQL statements. A snippet is simply a template containing a block of SQL code that you then can use elsewhere.
To use the SQL snippets, select Window>Database>Snippets, as shown in Figure 1.
Figure 1: Window>Databases>Snippets
Predefined snippets are available for several different functions, including Aggregate Functions, Analytic Functions, Character Functions, Conversion Functions, Date Formats, Date/Time Functions, Flashback, and Number Formats, as shown in the Snippets palette in Figure 2.
Figure 2: Snippets for functions and formats
Using a SQL Snippet
As an example of using Snippets, add SELECT in a SQL Worksheet and select COUNT(*) in the Aggregate Functions, as shown in Figure 3.
Figure 3: Selecting Aggregate Functions>COUNT(*)
|Note: If you are unfamiliar with SQL Worksheets, you can get a quick introduction by reading my previous article, “Using Oracle JDeveloper SQL Worksheets.” The examples assume you are using the database and table created in that article.|
Add the COUNT(*) function to the SQL Worksheet, as shown in Figure 4.
Figure 4: Adding COUNT(*) to the SQL Worksheet
Complete the SQL statement as SELECT COUNT(*) FROM wlslog; click the Run Statement icon to run the SQL statement.
A count of 7 gets displayed, as shown in the query result in Figure 5.
Figure 5: Query Result
Adding a New Snippet
New snippets may be added by selecting the Add Snippet icon, as shown in Figure 6.
Figure 6: Add Snippet
Once you’ve selected the icon, you will be presented with a Save Snippet window where you can enter the SQL for the snippet you want to create, as shown in Figure 7. You then can save the snippet by clicking the Apply button.
Figure 7: Save Snippet
Editing a SQL Snippet
To edit user snippets (not the built-in snippets), click Edit User Snippets, as shown in Figure 8.
Figure 8: Edit User Snippets
In Edit Snippets, select the snippet to edit and click Edit, as shown in Figure 9. The Save Snippets window shown in Figure 7 gets displayed, in which the snippet may be modified and saved with Apply.
Figure 9: Edit Snippets>Edit
Where Do We Go From Here?
This was just a quick overview of using SQL Snippets in Oracle JDeveloper. There are a number of SQL Snippets that can be found on the Internet, or you can create your own.
In the next article, I’ll show you another feature within Oracle JDeveloper that you can use with MySQL or other databases. You’ll learn how to use a table as a template to create new tables!