SQL Developer 1.5 New Features

Purpose

This tutorial introduces some of the features added to or enhanced in Oracle SQL Developer 1.5.

Time to Complete

Approximately 1 hour.

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Setting up your environment
 Alternative Connections
 Exporting Objects
 Copying Schemas
 Using Extended Search
 Setting Preferences for SQL Worksheet
 Working with Trace Files
 Integrating Application Express
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using SQL Developer, users can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created. Oracle SQL Developer 1.5 introduces a long list of new features; these include file based development, integrated version control, and an array of updates to existing features. The code formatter supports a number of formatting choices. This version also supports a new code template, which means you can now define pieces of frequently used code and using a key stroke combination quickly recall the code. New reports, more Application Express features all feature in the product.

This tutorial takes you through a few of the new connection types supported, you create a code template, use the extended search capabilities and use the schema copy and difference features. For users working with Application Express there is a section on that too.

Developed in Java, Oracle SQL Developer runs on Windows, Linux and the Mac OS X. With the default connectivity to the database is through the JDBC Thin driver, no Oracle Home is required. To install Oracle SQL Developer simply unzip the downloaded file. This tutorial uses Oracle Database 11g R1. You can use any of the Oracle Databases 9.2.0.1 and later, and any Oracle database edition including the Express Edition.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install the Oracle Database 10g or later or Oracle Database XE.

2.

Install Oracle SQL Developer 1.5.1.
Note: Oracle SQL Developer is available for download for FREE from OTN. To install Oracle SQL Developer, unzip it into any directory on your machine.

3.

Download and unzip the sqldev15nf.zip file into your working directory (i.e.wkdir)

4.

Make sure that the Sample Schemas are installed. You will use the HR schema in this tutorial.

5.

Install and configure Oracle Application Express in order to do the last section in this tutorial.

Back to Topic List

Setting up Your Environment

This tutorial requires a selection of users. To prepare the environment you use SQL Developer to create a new user and variety of connections. To complete the setup you need to perform the following steps:

1.

Open Oracle SQL Developer from the icon on your desktop.

If you performed the previous tutorial, and did not exit Oracle SQL Developer close to the open tabs on the right hand side.

 

2.

In the Connections navigator, right-click Connections and select New Connection.



3.

Create the system connection with following details:

Connection Name: system
Username: system
Password: oracle
Hostname: localhost
Port: 1521
SID: orcl

Click Test to verify the status. Click Connect to connect and save the connection.

 

4.

Expand the new system connection. Select Other Users, right-click and select Create User.

 

5.

Complete the dialog using the following details:

User name: test
Password test
Default Tablespace Users
Temporary Tablespace Temp



Note: Passwords are case sensitive in Oracle Database 11g.


6.

Select the System Privileges tab and check the Granted checkbox for the privileges that will produce the code shown below :

GRANT CREATE SEQUENCE TO test ;
GRANT CREATE SESSION TO test ;
GRANT CREATE TABLE TO test ;

GRANT CREATE TRIGGER TO test ;
GRANT CREATE TYPE TO test ;
GRANT CREATE PROCEDURE TO test ;
GRANT CREATE VIEW TO test ;
GRANT UNLIMITED TABLESPACE TO test ;

Click Apply.

                                

                            


7.

Review the results.

Click Close.

 

8.

Create a new connection Test for the new user using the following details:

Connection Name: Test
Username: Test
Password: test
Hostname: localhost
Port: 1521
SID: orcl

Check Save Password

Save the connection.

 

9.

Create a connection for hr. (If you worked on the previous tutorial, you may use the hr_orcl connection you created there)

Create the hr connection using the following details:

Connection Name: hr
Username: hr
Password: hr
Hostname: localhost
Port: 1521
SID: orcl

Check Save Password

Test the connection and then click Connect.

 

Back to Topic List

Alternative Connections

Oracle SQL Developer 1.5 introduced new methods of connecting to the Oracle Database. In addition to the Basic, TNS and Advanced (JDBC URL) connections, you can use operating system (OS) Authentication, LDAP, and Proxy Authentication. In this section you connect to the database using Proxy Authentication.

Proxy authentication enables a JDBC connection to act as a proxy for another JDBC connection. When you use Proxy Connection option when creating a database connection, the connection will connect to the user specified in the connection, but authenticated using the user name and either the password or distinguished name of the proxy user. To create a connection with proxy authentication perform the following steps:

1.

Select the system SQL Worksheet tab.

If the SQL Worksheet is closed, right-click the system connection in the navigator and select Open SQL Worksheet.

 

2.

In SQL Worksheet type the following command to create a proxy user and click F5 or the Run Script button.

                                 
                                   
CREATE USER proxy_user IDENTIFIED BY pass;
                                
                              

 

3.

Alter the proxy_user so that you can connect to the user using the username and password for the user HR :

                                 
                                   
ALTER USER proxy_user GRANT CONNECT THROUGH HR AUTHENTICATED USING PASSWORD;
                                     
                                                                                                  
                                   
Click
                                  
                                     
F5
                                    
                                  
or the Run Script button to execute the statement
                                  
                                     
.
                                                                  
                              

 

4.

Grant create session privilege to the user proxy_user.

                                 
                                   
GRANT CREATE SESSION to proxy_user;
                                
                              

Click F5 or the Run Script button to execute the statement .

 

5.

In the Connection navigator, click New Connection button.

 

6.

In the New Connection window enter the following details:

Connection Name: Proxy_conn
Username: hr
Password: hr
Hostname: localhost
Port: 1521
SID: orcl

Check Save Password

Even though you have called the Connection Proxy_conn, you are providing the HR username and password.

 

7.

Click Connect and expand the new Proxy_conn connection. Expand the Tables node.

Notice that the list of objects displayed are for HR.
8.

Right-click the Proxy_conn and select Properties to invoke the Connection Dialog.

 

9.

Check the Proxy Connection checkbox , Oracle Proxy Connection window appears. In Oracle Proxy Connection window enter the following details:

Username: proxy_user
Password: pass
Click OK.

 

10.

In the Connection window, click Test and Connect.

 

11.

Expand Connections > Proxy_conn >Tables. Note that although proxy_conn connects using the credentials of the user hr, it actually connects to the schema proxy_user, and so does not see the tables of hr schema under proxy_conn.

 

Back to Topic List

Exporting Objects

You can use the Export Wizard to export some or all database objects of a database connection to a file. The export file will not only contain the SQL data definition language (DDL) statements to create the exported objects, but if you choose to export the data too, the export file will contain Data Manipulation Language (DML) to populate the exported objects.

1.

Open SQL Developer. Click Tools > Database Export

 

2.

Click Browse to select the location and name of the export file.

 

3.

Browse the files directory, accept the default file name, export.sql . Click Save.

 

4.

In the Connection drop down list, select the connection from which you want to export objects . In this example you choose the connection hr .

 

5.

Under the DDL options the Terminator and Pretty Print DDL options are selected by default. Review the rest of the options available, leaving them unchecked. Click Next.

 

6.

The Types to Export screen includes the Object Types that you want to be exported from the connection hr . By default all the object types are selected. Click Toggle All check box to deselect all the object types.

Select only the object type Tables and then click Next.

Note : You must select at least one object type.

 

7.

Click the Go button to search all objects that meet the selection criteria that you specified in step 6.

In this example clicking Go lists all tables owned by hr.

 

 

8.

Click the tables EMPLOYEES and DEPARTMENTS and then use the arrow keys to move them to the list box on the right hand side of the window. Click Next.

 

9.

You can review the information that will be used to create the output file, which will contain statements to export database objects and data according to your specifications. To create the output file, click Finish.

 

10.

The file is also displayed in a SQL Worksheet window, where you can edit it and run it as a script.

In this example you run the script export.sql to create the tables EMPLOYEES and DEPARTMENTS in the Test schema. Click Run Script.

 

11.

The script is not yet associated with any user. Select Test from the drop list and click OK.

 

12.

In the Connections navigator, select Test and click Refresh to see the newly added tables EMPLOYEES and DEPARTMENTS.

 

Back to Topic

Copying Schemas

You can use the Database Copy to copy objects directly from a source to a target schema. This is a quick and very useful way of copying all tables and data from one schema to another. To copy tables between two schemas perform the following steps:

1.

Click Tools > Database Copy

 

2.

In the Source Connection drop-down list select the database connection for the source schema. Select hr as source.

 

3.

In the Destination Connection drop-down list, select the database connection for the source schema. Select Test as the destination.

 

4.

Select Create Objects. Click Next.

 

5.

Notice in the Copy Summary that all the tables in HR will be created in Test, even the EMPLOYEES and DEPARTMENTS.

 

6.

Review the alternatives and the differences in the Copy Summary for each by clicking the Back button to return to the first panel and select each of the other two options in turn, clicking Next to review the Copy Summary for each.

For example, notice how the Truncate optin assumes all tables exist and does not create new tables.

 

7.

Once you have reviewed the differences between the options, select Create Objects a final time, click Next and Finish.

 

8.

The result of the comparison is displayed in a SchemaCopy log file in the SQL Developer window.

Review the full results log. Do you expect the errors you find?

Note: DEPARTMENTS and EMPLOYEES were already created.

 

9.

Select the Test connection and click Refresh to see the newly copied objects.

 

Back to Topic

Using Extended Search

Extended search enables you to find various types of objects (tables, columns, declarations within functions or procedures, and so on) associated with an Oracle database connection. After searching an object you can also open it in editing pane and work with it. While you can use the Extended Search facility with earlier versions of the Oracle Database, this feature takes advantage of the PLScope feature in Oracle Database 11g, which allows users to search for parameter declarations and references and is as such a powerful feature in Oracle Database 11g.

To use extended search feature perform the following steps:

1.

Select File -> Open.

 

2.

Open the file PROC_emp_cursor.sql .

 

3.

Click the Run Script to execute PROC_emp_cursor.sql .

 

4.

Select hr in the Select Connection window. Click OK.

 

5.

Click View > Extended Search.

 

6.

In the Extended Search window enter following details:

Connection: hr
Name: c_emp_cursor
Type: All
Usage: All

Click Lookup

 

7.

All occurrences of c_emp_cursor are displayed.

 

8.

Click each occurrence of c_emp_cursor to view and edit it in PL/SQL Editor.

 

9.

You can also use wild cards while searching database objects. Searching for %emp_cursor displays all occurrences of the cursor c_emp_cursor and also the procedure Proc_emp_cursor .

 

10.

You can apply a filter on the search results.

  • Filter on the basis of Usage. Select Declaration for Usage and click Lookup.

  • Filter on the basis of Type. Change Usage back to All. Enter %dept% for Name and select View for Type and click Lookup.

 

Back to Topic List

Setting Preferences for the SQL Worksheet

You can customize many aspects of the SQL worksheet by modifying SQL Developer preferences according to your preferences and needs. To set preferences for the SQL Worksheet, perform the following steps:

A Setting the Drag and Drop effect
B. Setting Code Templates

Back to Topic List

A. Setting the Drag and Drop Effects

Drag and Drop Effects determine the type of SQL statement (select, insert, update or delete) created in the SQL Worksheet when you drag an object from the Connections navigator into the SQL Worksheet. By default, drag and drop from the navigator to the SQL Worksheet creates and new Select statement with all the columns in the table included. You can modify these settings.

To set the Drag and Drop Effects according to your preference, perform the following steps:

1.

Select the menu Tools > Preferences.

 

2.

Click Database > Worksheet Parameters.

 

3.

Change the Drag and Drop effects to Insert. Click OK.

 

4 .

In Connections navigator, right-click HR and select Open SQL Worksheet.

 

5.

Expand the HR connection Expand Tables. Select and drag the JOBS table to the SQL Worksheet area.

 

6.

You will find an Insert statement for the table, JOBS in the SQL Worksheet area.

 

Back to Topic

B. Setting Code Templates

Oracle SQL Developer 1.5 lets you create Code Templates for frequently used code. You can create and use templates by using SQL Developer preferences. To create and use templates, perform the following steps:

1.

Select the menu Tools > Preferences.

 

2.

Click Database > SQL Editor Code Templates.

 

3.

Click the Add Template button to add a new template.

 

4 .

In the text box labeled Id type excep. This is the text that you want to be replaced by the template.

 

5.

To enter the template code, click the ellipses.

 

6.

In the Template Editor window, type in the text you want to replace the id with.

Use the following code:

                                 
                                   
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('The query did not return a result set');
                                
                              

Click Close.

 

7.

Click OK to close the Preferences window.

 

8.

Expand the HR Procedures node in the Connections navigator and select PROC_EMP_CURSOR, the procedure you created in the Extended Search exercise.

 

9.

Double-click to open the procedure in the PL/SQL Editor. You may need to click the second PROC_EMP_CUROSR tab to see the editor.

 

10.

Type excep in SQL Worksheet, in a new line below END LOOP;

 

11.

Hit the hot keys Ctrl+Shift+t. The template code replaces the id, excep.

Save the changes. Click File > Save.

 

Back to Topic

Back to Topic List

Working With Trace Files

In SQL Developer you can display and analyze the contents of a SQL Trace (.trc) output file.

1.

Click the File Open button on the standard toolbar.

 

2.

Open the file jdelocal_ora_2400.trc . Notice the size of the trace file which is more than 7 MB.

                                
                            

 

3.

A report containing the trace data in a tabular form is displayed.

 

4.

You can sort the report by Time, Count, CPU, Elapsed Time, Disk, Query, Rows and Current.

 

5.

To display the slowest transaction, sort the report by Time and in Desc order.

 

Back to Topic List

Integrating Application Express

Oracle SQL Developer provides an interface to Oracle Application Express applications and offers a number of useful activities to assist you when working with Oracle APEX. In this tutorial you perform the following operations:

A. Browsing an Application
B Importing an Application
C . Executing an Application Express Report

A. Browsing an Application

SQL Developer provides a variety of ways of browsing Oracle APEX applications. In this section you browse the OEHR Sample Objects application, which is part of the "Getting Started with Oracle Application Express" online guide.To browse through your applications, perform the following steps:

1.

Create a new database connection for the user HR_APEX. (If you still have a number of other tabs open from earlier sections, you can close those)

In the Connections navigator, right-click Connections and select New Connection.



2.

Create the HR_APEX connection with following details:

Username: hr_apex
Password: hr_apex
Hostname: localhost
Port: 1521
SID: orcl

Click Test to verify the status. Click Connect to connect and save the connection.

 

3.

In the Connections navigator, expand the Application Express node, under hr_apex, to display a list of all the applications owned by hr_apex schema.

                                
                            

 

4.

Click the name of the application to display the application-level details, which includes Lists of Values, Lists, Templates, Tabs, etc. In this case click the Sample Application.

 

5.

You can expand the Sample Application and you will see a list of the pages that make up that application.

 

6.

Click the Master Detail page to display a detail tab with all the page-level details. This includes Regions, Items, Buttons, Processes, etc.

 

7.

You can compare details of different pages or different applications. Click the Freeze View icon for the Master Detail page.

 

8.

Select the Add Modify Customers page to open a new tab and display the details for that page.

 

9.

Select the tab for the Add Modify Customers page and drag it below the Master Detail page and release. (As you drag you'll notice a blue box which shows the new positioning)

 

10.

Click the Items tab for each. Now that the Add Modify Customers and the Master Detail page are now both open, you can easily compare the values.

Close the two tabs.

 

Back to Topic

B. Importing an Application

You can use SQL Developer to import Oracle Application Express applications. In this section you import the TIMESHEETS sample application. Oracle APEX provides a selection of packaged applications, which you can install and use out-of-the-box or modify. You can access these applications from the Oracle APEX homepage on OTN. (http://apex.oracle.com)

To import and browse an Oracle APEX application, perform the following steps:

1.

In the Connection Navigator select the hr_apex connection, right-click the Application Express node and select Import Application.

 

2.

In the dialog, click Browse to locate the file to import.

 

3.

Browse to the files directory and select timesheets_installer_1.0.sql. Click Select

Click Next.

 

4.

In the Choose Import Options dialog, check the Run Install Script option and accept the rest of the defaults.

Click Next.

 

5.

Review the Results. Click Finish.

 

6.

The import process will take a short while. Click OK to complete the install process.

 

Back to Topic

Back to Topic

C. Executing an Application Express Report

SQL Developer provides a selection of shipped reports. These reports include a selection of Application Express reports. To review some of the reports, perform the following steps:

1.

Select the Reports navigator.

 

2.

Expand All Reports > Data Dictionary Reports > Application Express and review the available reports.

 

3.

Click Applications. A Select Connection window appears, select hr_apex and click OK.

                                
                            

 

4.

A report consisting details for each application is displayed. Click on the Sample Application.

 

5.

In the details report, select the LOV tab. Here you review the various Lists Of Values that exist with in the application.

To review the LOVs in the Timesheets application, just click on the application and the details for that application are now displayed.

 

 

Summary

In this tutorial, you learned how to use some of the new features added to Oracle SQL Developer 1.5.

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document