As Published In July/August 2008

TECHNOLOGY: SQL Developer

Managing File Versions

Edit files and use version control in Oracle SQL Developer 1.5.

Oracle SQL Developer 1.5 expands its file-based support by introducing tight integration with the CVS and Subversion version control systems. In addition to letting you browse and edit files in the file system, this Oracle SQL Developer release enables you to import files into your source control system, check them out, modify them, and commit the changed files back into the source control system.

This column does not provide details about setting up source control systems, nor does it cover concepts and operations of these systems; rather, it discusses the use of these systems within Oracle SQL Developer and focuses on using Subversion.

To follow this column's examples, you need Oracle SQL Developer 1.5 and access to the HR sample schema in an Oracle Database instance. Start Oracle SQL Developer 1.5, create a new connection to the HR schema, and name it HR_ORCL. (For detailed information on creating a connection, see "Creating a Database Connection" in "Next Steps" and "Making Database Connections," in the May/June 2008 issue of Oracle Magazine , which provides details on the different connection alternatives available in Oracle SQL Developer.)

Browsing Files

You interact with your file and source control systems through the new File Navigator in Oracle SQL Developer. This tab is available on startup; if it is not visible, select View -> Files from the main menu. Using the File Navigator , you can navigate to files and open them; files with the SQL extension open in the SQL Worksheet , ready to be edited and run as shown in Figure 1.

You can run these SQL files by using F5 (Run Script) and F9 (Run Statement); the latter runs only the currently selected statement (not the full script). With each of these commands, you'll be prompted for the user connection.

If you associate the PKS, PKB, PLS, or PKH file extensions with Oracle SQL Developer, these files will open in an editor that provides compile and compile for debug operations. This means that you can edit and update these files as you would in the SQL Worksheet and when you click Compile (or Compile for Debug ), these files compile against the selected schema in the database. 

Editing Files in Oracle SQL Developer

One advantage of editing files in Oracle SQL Developer is that you have ready access to database connections to verify and test your code. Another advantage is that you have specialized editing tools on hand to assist in the process.

Create a new file in Oracle SQL Developer:

1. Click the File Navigator tab. If it is not visible, select View -> Files from the main menu.
2. Right-click the My Computer folder, and select New.
3. In the New Gallery dialog box, select Database Tier and SQL File and then click OK.
4. Enter tuning.sql for File Name , click Browse to provide a suitable location for the file, and click OK . (For this example, I use D:\version\files.) A new empty file is created, and it opens in the SQL Worksheet.
5. Enter the code in Listing 1 into the SQL Worksheet . When you enter the code, note that reserved words are displayed in bold and in a different color.

Code Listing 1: Initial code for tuning.sql

select * from employees e, departments d, job_history j
   where e.department_id = d.department_id and e.hire_date = j.start_date;

6. On the right-hand side of the SQL Worksheet is a Snippets tab. Move your cursor over the tab to reveal the full Snippets window.
7. With the Snippets window open, select Optimizer Hints from the list and drag LEADING (table-name, ...) —as shown in Figure 2—onto the SQL Worksheet , drop it just after select , and change the hint text to

 
/*+ LEADING(e j) */ 

Note that the tuning.sql text in the tab title is in italics, indicating that code has changed and has not yet been saved.
8. Click the Save icon. The tuning.sql text in the tab title changes to nonitalic (roman) text, and the file is saved.

In addition to editing individual files in Oracle SQL Developer, you can also use the new code templates feature to better manage frequently used code in multiple files.

To create a template,

1. Select Tools -> Preferences , and in the Preferences dialog box select Database -> SQL Editor Code Templates.
2. Click Add Template.
3. Enter ord for ID , enter order by e.last_name for Template , click OK to complete the code template, and close the Preferences dialog box.

This example is trivial, but it is designed to give you an idea of how you can add code templates of your own and use them. The best code templates to add are large chunks of frequently used code, such as user-defined exception handlers.

Return to your tuning.sql code in the SQL Worksheet , place the cursor just before the final semicolon, type ord, and then type the key combination Ctrl-Shift-T to replace the ord with the full piece of code.

Finally, format your code:

1. Return to the Preferences dialog box (select Tools -> Preferences ), and select Database -> SQL Formatter.
2. Expand the SQL Formatter node, and select Line Breaks.
3. Select 1 from the Number of Commas per line list, and check the Before Comma check box.
4. Select Other (under the SQL Formatter node), and select Keywords Uppercase from the Case list. Click OK.
5. Back in the SQL Worksheet , right-click the code and select Format.
6. Click the Save icon, and close the file (select File -> Close or click the X in the tuning.sql tab).

Click the File Navigator tab, and select View -> Refresh (or click the Refresh button) to refresh the folders and files listed. Navigate to the folder where you saved the tuning.sql file, and open it. Note that there are two tabs for this file: SQL Worksheet and History . If you saved the file at various points during this exercise, you'll see the different iterations displayed in the History tab, as shown in Figure 3. 

Now, associate the tuning.sql file with the HR schema. Click the SQL Worksheet tab, expand the list on the right-hand side of the SQL Worksheet , and select your HR_ORCL connection. After you select a connection, the Commit, Rollback, Execute Explain Plan , and Autotrace icons become available, and you can run the file against the HR schema.

Setting Up FOR Version Control

This section assumes that you have access to Subversion or have a Subversion repository. If you do not have a repository set up, begin by creating a new Subversion repository:

1. From the main menu, select Versioning -> Subversion -> Create Repository.
2. Enter a Repository Path , such as C:\Documents and Settings\<user>\demo , and a Connection Name , such as demo, and click OK.
3. Whether you have just created a repository or are using your own, you need to create a connection to it. From the main menu, select View -> Versioning Navigator . If you have just created a repository, expand the Subversion node in the Versioning Navigator to reveal your connection. If not, right-click Subversion and select New Repository Connection.
4. Populate the dialog box with your connection details using this repository example: for Repository URL , enter file:////c:/Documents and Settings/<user>/demo , and for Connection Name , enter demo . (In this example, there is no User Name or Password , but if you are using an external repository, you need to populate these fields too.)
5. Click Test Connection , and when a successful connection result appears in the Status box, click OK.

If you are connecting to an existing repository, expand the Subversion node and browse the objects in your repository. If you have just created your repository, populate it by importing files:

1. In the File Navigator , navigate to a folder you want to bring under version control; for this example, select the D:\version\files folder.

Figure 1: The File Navigator in Oracle SQL Developer

Figure 2: Inserting snippets

Figure 3: Reviewing the history of a file

Figure 4: Versioned files in Subversion

2. From the main menu, select Versioning -> Subversion -> Import Files.
3. Using the wizard, select the Repository Connection you created. Click Next to navigate to the Source panel.
4. Click Browse to navigate to D:\version\files. Click Next to go to the Filters panel, and click Next again to go to the Options panel.

You can now see the files in your Subversion repository. These are read-only files and display the current state of your files.

Click the File Navigator tab, click the Refresh button, and review the contents of the D:\version\files folder. These files are now marked as versioned, and the version number is displayed. You can open, edit, and save (commit to check in) these files. You can select any versioned file and select Versioning -> Subversion to review the versioning options available.

Now open the tuning.sql file you worked on earlier. Make a few changes or add a comment, and select the History tab to review the differences between the original file and any changes you have made. Right-click the file in the History tab, and review the versioning options available to you (as shown in Figure 4). Save the file and the changes. Note the new overlay icon on the file in the File Navigator . Right-click the filename, and select Commit to check the file in. 

Conclusion

Much more than just a database browser, Oracle SQL Developer offers utilities for day-to-day database tasks such as creating and altering database objects and an environment for running scripts and doing ad hoc reports.

Oracle SQL Developer 1.5 takes these activities to a new level, by introducing version control. Tightly integrated with CVS and Subversion, it enables you to navigate through and edit versioned files from within Oracle SQL Developer. The new versioning support provides merge and compare facilities, and you can import and check out files from within the tool. 

Send us your comments