As Published In
Oracle Magazine
May/June 2006

TECHNOLOGY: SQL


Oracle SQL Developer Soars

By Jonathan Gennick

Bring the power of a GUI-based workbench to SQL and PL/SQL.

Every craftsman needs a workbench. With a good workbench, you have room to work. Tools are at hand for immediate use and yet out of the way when not required. Ultimately, a good workbench is an extension of the person using it.

Oracle has just released such a workbench in the form of Oracle SQL Developer (formerly code-named Project Raptor). It is a "workbench" with a clean yet powerful GUI interface. Using Oracle SQL Developer, you can browse all the objects in a database schema; edit, test, and format SQL statements; edit your database data; debug PL/SQL blocks; develop and run management reports; and more.

This article demonstrates how to use Oracle SQL Developer to complete a variety of common tasks very quickly.

Getting It

To install Oracle SQL Developer, download the version for your platform from oracle.com/technetwork/software/products/sql and unzip it.

To run Oracle SQL Developer, open the folder where it was installed and double-click the Oracle SQL Developer executable (sqldeveloper.exe in Microsoft Windows). Oracle SQL Developer launches in a window (shown in Figure 1) with two panes. The left pane displays a Connections navigator. Double-click the Connections icon to create a new connection, type in a name for the connection, type in your username, fill in the target host and database service names, and click the Connect button. A SQL Worksheet appears in the right pane when the connection is successful.

 

figure 1
Figure 1: Oracle SQL Developer connections navigator and columns tab


If you have Oracle client software and a tnsnames.ora file already installed on your machine, Oracle SQL Developer will automatically populate the Connections navigator from the net service names defined in tnsnames.ora. In that case, you'll be able to expand the Connections navigator and double-click on a net service name to connect to the target database. (In some cases, you may need to point to your tnsnames.ora directory via the TNS_ADMIN environment variable.)

The o36sql.zip download for this article includes the setup.sql script. Use this script to create the sample data:

1. Create, or make sure you have access to, a schema in which you can create tables and stored procedures.
2. Grant yourself (or have your DBA grant you) the DEBUG ANY PROCEDURE and DEBUG CONNECT SESSION system privileges.
3. Create three connections named dev, test, and prod. Point all connections to the schema in step 1. (For the steps in this article, treat these connections as separate databases.)
4. Right-click the dev connection, and select Open SQL Worksheet . A worksheet opens in the right pane.
5. Open the setup.sql script, using any text editor. Paste the contents of the script into the Enter SQL Statement text box of the SQL worksheet.
6. Press the F5 key to execute the script.
7. The sample data is created. Feedback appears in the Script Output tab.

The Pressure

Now suppose you work for a university research department charged with maintaining a database of waterfalls in Michigan's Upper Peninsula. The university runs Oracle Database, and you are the department's DBA. You have an important business lunch at noon, it's 11:45 a.m., and you've got a list of to-do items that you know you would not have been able to finish before noon in the past. Finally, suppose you installed Oracle SQL Developer last night, spent a few minutes getting to know the interface and capabilities, and have a very good feeling that you can finish everything on your to-do list before noon.

Quickly you close the door to your office, sit at your desk, and start Oracle SQL Developer. Your first task is to check on the size of a database column in the development database. One of the developers thinks he mistakenly created it to be 150 characters long, rather than the required 15.

Working quickly, you expand the Connections node in Oracle SQL Developer's Connections navigator (Figure 1, #1). Scanning the list of connections, you find the one for the dev database (Figure 1, #2) and expand it. You continue drilling down by expanding the Tables node, and then you click the UPFALL table (Figure 1, #3). In the right pane, you click the Columns tab (Figure 1, #4), and sure enough, the name column's data type is VARCHAR2(150) (Figure 1, #5), indicating that the column data type is indeed 150 characters long.

Before changing the column length, you decide to make a quick check of the data. Clicking the Data tab (Figure 2, #1) displays a list of all the waterfalls. You're concerned about the effect of shortening the name field, so you type length(name) > 15 into the Filter field (Figure 2, #2) and press Enter. Sure enough, one name, Little Miners Falls, is too long. You count the characters; at least four need to go.

 

figure 2
Figure 2: Connections navigator and data tab


You're working with data the developers put in for testing, so you're free to make some changes. Clicking in the NAME field displays a cursor. You delete the word Falls entirely, leaving the name as Little Miners (Figure 2, #3). (That strikes you as a far more graceful solution than just truncating it to Little Miners F. ) You click the Commit Changes toolbar icon to save your changes (Figure 2, #4).

With the data in good shape, you right-click the UPFALL table in the Connections navigator and select Edit to bring up the Edit Table dialog box (Figure 3). You click the Columns tab (Figure 3, #1), the NAME column (Figure 3, #2), change the value in the Size field to 15 (Figure 3, #3), and click OK .

 

figure 3
Figure 3: Edit table dialog box


The waterfall name column length issue is resolved.

One task down. Time now: 11:47.

Exporting and Scripting

One of the test technicians asked you to put a copy of the development waterfall table into the test database, so you turn your attention to the UPFALL table's Data tab (Figure 2, #1). You want to copy all rows, so you remove the filter, by clearing the Filter field and pressing Enter. Next, you begin the export process by right-clicking the UPFALL table in the Connections navigator and selecting Export->SQL Insert . Other export options include comma-separated values (CSV), XML, and SQL Loader. There's not much data to move, so you'll use the SQL Insert option to generate a file of INSERT statements.

The Export Table Data dialog box (Figure 4) prompts you to confirm your format choice (INSERT), confirm the output (File), and select the file location. Click Browse and navigate to a suitable location for the INSERT file you will create. Type UPFALL_COPY.sql as the filename, and click Save to return to the Export Table Data dialog box. Then type UPFALL_COPY into the Table field just above the filename. UPFALL_COPY will be the table name used in the INSERT statements you are about to generate. Finally, click Apply to export the UPFALL table's data as a series of INSERTs into the UPFALL_COPY table. The INSERTs will be written to a file named UPFALL_COPY.sql, at the location you specified.

 

figure 4
Figure 4: Export table dialog box


Now load that data into the test database, which you access through the test connection created earlier. Right-clicking the test connection and selecting Open SQL Worksheet opens a worksheet for the test connection. You now have tabs in the right pane for the connections and tables you've accessed (Figure 5, #1).

 

figure 5
Figure 5: Connections navigator and SQL worksheet for test connection


After selecting File->Open File , you navigate to the location where you saved the UPFALL_COPY.sql file and double-click the filename to bring that script of INSERT statements into the editing field. You add one statement to the beginning of the script (Figure 5, #2) to create a copy of the UPFALL table: 

CREATE TABLE UPFALL_COPY AS 
SELECT * FROM UPFALL WHERE 1=2;


Then you press the F5 key to execute the script.

The content in the Script Output tab looks good—the table was created, and the INSERTs all succeeded. You wouldn't use this method to move, say, a terabyte of data, but it gets the job done on a few dozen rows, getting you that much closer to lunch.

Time now: 11:50.

Querying and Optimizing

Next on your list is to write a query for a developer working on a new report for listing distinct waterfall names. You open a new worksheet on dev, selecting Tools->SQL Worksheet ; if prompted, choose the dev connection. You begin your query by typing 

select id, 


The developer wants the waterfall names capitalized and any inadvertent leading or trailing spaces removed. A click of the Snippets button on the far right (or selecting View->Snippet ) opens a window of code snippets, which you immediately tear off and drag to a convenient place on your desktop where it can float, always on top (Figure 6). After choosing Character Functions from the list at the top of the Snippets window, you drag and drop the UPPER and TRIM function prototypes directly into your query. A quick bit of editing yields

 

figure 6
Figure 6: Connections navigator, SQL worksheet, and floating Snippets window

 

select id, UPPER(TRIM(BOTH ' ' 
FROM name)) name
from upfall; 


Pressing Ctrl-Enter to execute the statement, you sanity-check the output in the Results tab. Then you skip a line and build a second query, this time against the LOCAL_FALL_NAMES table: 

select id, UPPER(TRIM(BOTH ' ' FROM name)) name
from local_fall_names; 


With the cursor in the second query, you press Ctrl-Enter again. Even though both queries are visible in the worksheet, only the one your cursor is in executes in response to Ctrl-Enter. Results from this second query look reasonable, too.

Next you delete the semicolon at the end of the first query and type UNION on the blank line between the queries, creating a single query that combines both SELECT statements. Pressing Ctrl-Enter, you execute the revised query. The results appear good.

Now for a quick check of the execution plan. Clicking the Execute Explain Plan toolbar icon displays the plan. You see a full scan of each table, which is what you expect for the query you've written. The plan is good.

You're almost ready to send the query to the developer, but first you decide to tidy up its appearance. Pressing Ctrl-B directs Oracle SQL Developer to nicely format your hastily written query. Keywords are capitalized, clauses are indented, and consistent line breaks are added (Figure 7).

 

figure 7
Figure 7: Reformatted SQL


It's all good. You copy and paste the query into an e-mail message and send it off to the developer. Another task is done.

Time now: 11:53.

Debugging

The last developer request is for help in debugging a stored procedure. Turning again to the dev connection in the Connections navigator, you expand the Procedures node and then click the FILL_PIVOT procedure (Figure 8, #1). The procedure text appears in a Code tab. The procedure is supposed to populate the PIVOT table with 1,000 rows numbered from 1 to 1,000, except right now it doesn't generate any rows at all.

 

figure 8
Figure 8: Debugging the FULL_PIVOT procedure


At first glance, the code in FILL_PIVOT looks OK and like it ought to generate 1,000 rows. You decide to follow the execution, using the debugger to see what is really happening. One click of the Edit icon (below the Code Debug icon (Figure 8, #2). You click the Compile for Debug toolbar icon (Figure 8, #3) and then set a breakpoint by clicking in the margin to the left of the WHILE line (Figure 8, #4).

Clicking the Debug toolbar icon (Figure 8, #2) opens a dialog box to begin your debugging run. You receive a prompt to fill in any needed parameter values. There are no parameters to add, so you click OK .

Execution begins and quickly stops when it reaches the breakpoint. Now you have a chance to view current variable values in the Smart Data tab. You notice that the pivot_index variable is null (Figure 8, #5). Aha! Bitten by "nothing" once again. After clicking the red, square Terminate button (Figure 8, #6) in the Debugging - Log tab, you change the declaratation of pivot_index to initialize the variable: 

pivot_index NUMBER := 0;

 

Next Steps


READ more about
Oracle SQL Developer
SQL editors
 "
SQL Tools Make Fast Work"
SQL*Plus User's Guide and Reference
SQL*Plus Quick Reference
Oracle Database PL/SQL User's Guide and Reference
Oracle Database SQL Reference
oracle.com/technetwork/documentation

DOWNLOAD
Oracle SQL Developer
sample data for this article

After making this change, you click the Compile button (Figure 8, #7)—not Compile for Debug this time. Then you execute the procedure, by clicking the green arrow. Selecting the PIVOT table in the Connections navigator and looking at the table's Data tab confirms that the 1,000 rows were properly generated.

Debuggers make light work out of finding and fixing many types of code bugs. With a smile, you cross one more task off your list.

Time now: 11:55.

Reporting

You have one last task to complete before running out the door for lunch: You've been a bit concerned about free space on the production database. To check on that, you click the Reports tab in the left pane (Figure 9, #1) to display the Reports navigator. Expanding the list of reports, you navigate through Reports, Data Dictionary Reports, Database Administration , and Storage to find the Free Space report (Figure 9, #2). Clicking it, you are asked to choose a connection. This time you select the prod connection. The Enter Bind Values dialog box opens, asking you for a tablespace name, which you leave blank, because you want to see free space on all the tablespaces. Clicking the Apply button displays the Free Space report (Figure 9, #3). No immediate crisis is apparent. All the tablespaces seem to have plenty of room for growth.

 

figure 9
Figure 9: Running the Free Space report


It's a fine, sunny day. You grab your sunglasses and head out the door in plenty of time for lunch.

Time now: 11:56.

You finish the last two tasks, look at the clock, and breathe a sigh of relief. It's a fine, sunny day. You grab your sunglasses and head out the door in plenty of time for lunch.

Time now: 11:56.

Productive Workbench

The story here is fictional, but the scenario is all too real. Database administrators and developers constantly manage ad hoc, unrelated database tasks to develop queries, change database tables, chase down bugs, develop applications, and generally manage the database. Frequently one task must be suspended midstream in favor of another, higher-priority job that must be done "right now." The more pending tasks that stack up, the more frequently the type of task changes—and the more difficult it is to manage using a nonintegrated set of tools.

Oracle SQL Developer is a software workbench. It provides space for you to lay out your work, not for just one task at a time but for many in parallel. Powerful tools are a quick right-click or toolbar icon away, there when you need them and out of your way otherwise. The ease with which you can maintain flow from one task to the next provides a significant productivity advantage while reducing stress. Whether you are new to Oracle Database or experienced with it, Oracle SQL Developer gives you a productive workbench on which to develop and debug SQL and PL/SQL code and manage your database. Maybe it'll even get you to lunch on time. 


Jonathan Gennick ( www.gennick.com ) is an experienced Oracle professional and member of the Oak Table Network. He wrote the best-selling SQL Pocket Guide and the Oracle SQL*Plus Pocket Reference, both from O'Reilly Media.

 



Send us your comments