TECHNOLOGY: SQL Developer
Managing File VersionsBy Sue Harper
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.)
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.
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.
/*+ 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.
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.
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.
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.
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.
5. Ensure that Perform Checkout is selected. Click Next , review the summary, and click Finish.
6. In the Versioning Navigator , select the Demo connection and click the Refresh icon.
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.
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.
Sue Harper is an Oracle senior principal product manager based in London. Her technical blog, sueharper.blogspot.com, explores Oracle SQL Developer features.