SQL Developer 1.5 New Features

Purpose

This tutorial shows you how to use the new features added to SQL Developer 1.5.

Time to Complete

Approximately 1 hour

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Setting up the environment
 Additional ways to connect to your database
 Exporting objects
 Copying and comparing schemas
 Using extended search
 Setting preferences for SQL Worksheet
 Versioning your files
 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. To execute this you need to login to a Windows operating system
2.

Install Oracle Database 11g

Note: You can use any Oracle Database above 9.2.0.1

3. Download and install SQL Developer from OTN .
4.

Create a directory C:\wkdir. Download and unzip the sqldev_nf.zip file into your working directory C:\wkdir.

5.

Have access to the hr Sample Schema that is shipped with the database.

If not already done, a DBA user needs to unlock the hr schema to provide access. This can be done with the following commands:

                                 
                                   
ALTER USER hr ACCOUNT UNLOCK;
                                    
ALTER USER hr IDENTIFIED BY hr;

 

6.

Grant sysdba role to the user system.

A DBA user needs to Grant sysdba role to the user system. This can be done with the following commands:

                                 
GRANT sysdba to system;
                              

 

7.

If you want to do the section on Application Express, you can download and install Application Express from OTN . You can refer to the Install Guide, which explains how to install and configure Oracle Application Express.

8.

If you want to do the section on Application Express, you need to perform the following steps:

  1. Log in to Administration Services
  2. Create a Workspace
  3. Create an additional user account
  4. Install the Sample Objects
  5. Log in to your local instance using the credentials of the additional user created in step 5.4
  6. Create the initial Application
9. To do the section on versioning, you would need access to a Subversion software or a Subversion repository.

Back to Topic List

Setting Up the Environment

To execute this OBE you need to perform the following steps to set up the environment:

1.

Open SQL Developer. Click New Connection Icon in the Connection Navigator.

 

2.

Create system connection with following details:

                               
Connection Name:  
                                
system
Username:  
                                
                                   
system
                                
Password:  
                                
                                   
oracle
                                
Check the  
                                
Save Password checkbox
Role:  
                                
                                   
sysdba
                                
Connection Type:   
                                
Basic
Hostname:  
                                
localhost
Port:  
                                
1521
SID:  
                                
orcl
                              
                            


Click the Test button. Note the Status as Success
.

To save the connection, click Save.

Click Connect. Connect will save the connection, connect to system and close the dialog at the same time.
Expand Connection > system.

 

3.

Create a user Test

Connect to the connection: system. Right-click on the connection name and select Open SQL Worksheet.

Open the file C:\wkdir\create_test.sql. Alternatively you can also type the following command in the SQL worksheet. Click the Run Script icon.

                               
                                 
CREATE USER Test IDENTIFIED BY Test;
                                  
GRANT CREATE SESSION to Test;
GRANT UNLIMITED TABLESPACE to Test;
GRANT CREATE TABLE to Test;

Click New Connection icon in the Connection Navigator and create a connection Test with following details:

Connection Name: Test
Username: Test
Password: Test
Check the Save Password checkbox
Role: default
Connection Type: Basic
Hostname: localhost
Port: 1521
SID: orcl
                               
                                  
                              
                            

Test the connection and then click Connect.

 

4.

Click New Connection Icon in the Connection Navigator and in the New/Select Database Connection window click the connection Test. You can use the settings for the test connection to create a new hr connection.

Create hr connection by changing the following details in the Test connection:

Connection Name: hr
Username: hr
Password:
hr

Test the connection and then click Connect.

 

Back to Topic List

Additional Ways to Connect to your Database

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

A Connections with OS Authentication
B. Connections with Proxy Authentication

Back to Topic List

A. Connections with OS Authentication

This feature enables you to connect to the database without a user name and password. The database depends on the client OS to authenticate the user. Perform the following steps:

1.

Check the value of OS_AUTHENT_PREFIX database parameter. In the Reports tab of SQL Developer, expand All Reports > Data Dictionary Reports > Database Administration > Database Parameters > All Parameters.

Select system in the Select Connection window. Click OK.

 

2.

In the Enter Bind Values window, type authen in the Value textbox and then click Apply.

A report, containing the parameters that have authen in their name, is displayed. Note the value of the parameter OS_AUTHENT_PREFIX.

 

3.

If this value does not exist in the init.ora file, you need to add the value to your init.ora database initialization file. Ask your DBA or if you have access to your own, edit the init.ora file and add the following entry:

                                 
                                   
OS_AUTHENT_PREFIX="OPS$"           
                                
                              

Note: If you edit the init.ora file to add the parameter, you need to restart the database to set the initialization parameter.

 

4.

Create the new user using your operating system connection name.

  • Connect to the system connection.
  • You need the value of the OS_AUTHEN_PREFIX (Refer to step 1 and 2), the domain name (if you are connected to a network domain) and the username you use to connect to the machine on startup. To obtain your domain name and username go to Start > Settings > Control Panel > User Accounts .

  • If you are connected to a network domain, create the user using the command create user "< OS_AUTHEN_PREFIX ><domain name>\<username>" identified externally . For example, if your domain name is ST-IDC and username TSRIVAST type the following create user command and then click the Run Script icon.
                                 
                                   
                                     
CREATE USER "OPS$ST-IDC\TSRIVAST" identified externally;
                                  
                                
                              

Note: The username should be in upper case.

  • If you are connected locally, create the user using the command create user "< OS_AUTHEN_PREFIX><machine name>\<username>" identified externally; For example, if your machine name is tsrivast-lap and username TSRIVAST type the following create user command and then click the Run Script icon.

CREATE USER "OPS$TSRIVAST-LAP\TSRIVAST" identified externally;

 

NOTE: If the OS_AUTHEN_PREFIX is an empty string, omit the OPS$ as shown in the above examples.

 

5.

Grant CONNECT and CREATE SESSION privileges to the user "OPS$ST-IDC\TSRIVAST" . Type the following commands in the SQL worksheet and click the Run Script icon:

                                 
                                   
GRANT  
                                  
                                  
CONNECT to "OPS$ST-IDC\TSRIVAST";
                                
                              
                                 
                                   
                                     
GRANT CREATE SESSION to "OPS$ST-IDC\TSRIVAST";
                                  
                                
                              

 

6 .

Thin driver does not support OS Authentication. To change the driver to thick driver click Tools > Preferences > Database > Advanced Parameters. Check the Use OCI/Thick Driver checkbox. Click OK.

 

7.

In the Connection navigator, click New Connection button.

 

8.

In the New Connection window enter the following details:

Connection Name: OS_Authent
Check the box OS Authentication
Change the SID to ORCL (database name)
Click the Test button
Note the status as Success.

You can now click the Connect button to get connected to the Oracle Database using OS Authentication.

 

Back to Topic

B. Connections with Proxy Authentication

Proxy authentication enables a JDBC connection to act as a proxy for an 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.

Open SQL Developer and connect to the connection, system.

 

2.

In SQL Worksheet type the following command to create a proxy user:

                                 
                                   
CREATE USER proxy_user IDENTIFIED BY pass;
                                
                              

 

3.

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

                                 
                                   
ALTER USER proxy_user GRANT CONNECT THROUGH Test AUTHENTICATED USING PASSWORD;
                                
                              

 

4.

Grant create session privilege to the user proxy_user.

                                 
                                   
GRANT CREATE SESSION to proxy_user;
                                
                              

 

5.

In the Connection navigator, click New Connection button.

 

6.

In the New Connection window enter the following details:

Connection Name: Proxy_conn
Username: Test
Password: test
Check the Save Password checkbox.
Check the Proxy Connection checkbox

 

7.

On clicking 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.

 

8.

In the Connection window, change the SID to orcl and click Test . Note the status as Success.

Click Connect

 

9.

Expand Connections > hr >Tables .

Expand Connections > Proxy_conn >Tables. Note that although proxy_conn connects using the credentials of the user hr, but since it actually connects to the schema of the user, proxy_user, you do not see the tables of hr schema under proxy_conn.

 

Back to Topic

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.

Back to Topic List

1.

Open SQL Developer. Click Tools > Export Wizard.

 

2.

To select the location and name of the export file click Browse.

 

3.

Browse and select a folder (in the example shown, the My Documents folder is used). Also provide a name for the .sql file (it defaults to export.sql), which SQL Developer uses to create and store the exported data. 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.

Select the DDL options . By default Terminator and Pretty Print DDL options are selected. Click Next.

 

6.

Select the Object Types, which you want to be exported from the connection hr . By default all the object types are selected. To select or deselect all the object types, click Toggle All check box. In this example you choose 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 run it as a script and perform other operations. In this example you run the script export.sql to create the tables EMPLOYEES and DEPARTMENTS in Test schema. Click the Run Script icon.

Select Test. Click OK.

You can see the newly added tables EMPLOYEES and DEPARTMENTS in Test schema.

 

Back to Topic

Copying and Comparing Schemas

You can use the Diff Wizard to find differences between objects of the same type and name (for example, tables named Employees) in two different schemas. You can then update the destination schema objects to reflect the differences in the source schema.

Back to Topic List

1.

Open SQL Developer. Click Tools > Diff Wizard.

 

2.

In the Source Connection drop-down list select the database connection for the source schema. In this example you will select hr as source.

 

3.

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

 

4.

In the Diff Objects list, select the objects that you want to compare. By default all the object types are selected. Click Toggle All to select or deselect all the object types. In this example you choose the object type Tables to be compared between the connection hr and Test. You must select at least one object type. Click Next.

 

5.

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

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

 

6.

Click to select the tables you want to compare and move them to the list box in right hand side of the window by using the arrow keys. In this example you select all tables of hr schema. Click Next.

 

7.

In the Diff Summary page, review the information that will be used to compare the source and destination connections. To perform the comparison, click Finish.

 

8.

The result of the comparison is displayed in a Diff Report window.

Click the table DEPARTMENTS. The Diff report window displays the difference between hr.DEPARTMENT and Test.DEPARTMENT.

Click the table JOBS . Since the Test schema does not contain a JOBS table, the Diff report window displays the DDL to create JOBS table.

 

9.

Click the Generate Script icon to create a file containing the DDL statements corresponding to the difference between hr and Test schemas.

Browse and select a folder (in the example shown, the My Documents folder is used). Also provide a name for the SQL file (it defaults to diff.sql ) which SQL Developer uses to create and store the DDL statements. Click Save.

 

10.

To execute the diff.sql file, click the Run Script icon.

In the Select Connection window select Test connection.

You should see the following output in the Script Output window.

 

11.

Expand Connection > Test > Tables. Click the Refresh icon.

The new tables added to Test schema are now displayed.

 

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. To use extended search feature perform the following steps:

1.

Open SQL Developer. Click the Open button in the standard toolbar.

 

2.

Open the file C:\wkdir\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 the occurrence of c_emp_cursor to view/edit it in SQL Worksheet pane.

 

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 SQL Worksheet

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

A Setting the Drag and Drop effect
B. Setting Code Templates
C. Changing settings for SQL Formatter

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. To set the Drag and Drop Effects according to your preference, perform the following steps:

1.

Open SQL Developer. Click Tools > Preferences.

 

2.

Click Database > Worksheet Parameters.

 

3.

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

 

4 .

In Connections Navigator window, expand the hr connection. Go to Tables > JOBS. Drag the JOBS table to the SQL Worksheet area.

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.

Open SQL Developer. Click Tools > Preferences.

 

2.

Click Database > SQL Editor Code Templates.

 

3.

Click the Add Template button to add a new template.

 

4 .

In the textbox labeled Id type an id (text you want to be replaced by the template) for the template to be created. In this example you will use the id, excep.

 

5.

To enter the template click the ellipses.

In the Template Editor window, type in the text you want to replace the id with. For this example you will use the following code. After typing the replacement text, click Close.

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

Click OK in the Preferences window.

 

6.

Open C:\wkdir\PROC_emp_cursor.sql.

Type excep in SQL Worksheet.

 

 

7.

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

Save the file. Click File > Save.

 

8.

You can also use the existing templates. In SQL worksheet type #date# and then hit the hot keys Ctrl+Shift+t. In the SQL worksheet #date# is replaced by current date.

 

Back to Topic

C. Changing The Settings For SQL Formatter

The SQL Formatter pane controls how statements in the SQL Worksheet are formatted when you click Format SQL.. To change the way SQL Formatter adds line breaks while formatting the statements in SQL Worksheet, perform the following steps:

1.

Open SQL Developer. Click Open button in the standard toolbar.

 

2.

Open C:\wkdir\longQuery.sql.

 

 

3.

Click Tools > Preferences.

 

4 .

Click Database > SQL Formatter > Line Breaks.

 

5.

Change the Number of Commas per Line to 2.

Click OK.

 

6.

Right-click in the SQL Worksheet area. Click Format.

You see the query in SQL Worksheet formatted according to your preference (two commas per line).

 

Back to Topic

Back to Topic List

Versioning Your Files

You can use version control feature of SQL Developer 1.5 to import files into your source control system, check them out, modify and commit the changes back. To version control your files perform the following steps:

1.

Open SQL Developer. To view the files to be version controlled, click View > File.

 

2.

Use File Navigator to browse and open file C:\wkdir\PROC_EMP_CURSOR.sql .

                                
                            



3.

To create a new Subversion repository, click Versioning > Subversion > Create Repository. If you already have a subversion repository you can skip this step.

Provide a Repository Path as C:\Documents and Settings\<user>\repos0 and a Connection Name as hr. Click OK.

 

4.

Click Versioning > Subversion > Import Files.

 

5.

In the Welcome page of the Import to Subversion wizard, click Next.

 

6.

Select C:\Documents and Settings\<user>\repos0 as the destination repository connection. Click Next.

 

7.

Click browse and select C:\wkdir as the source directory. Click Next.

 

8.

In the Filters page click Next.

 

9.

In the Options page, ensure that the Perform Checkout check box is checked. Click Next.

 

10.

Review the options for the Import operation and Click Finish.

 

11.

In the Version Navigator select Subversion > hr 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.

 

12.

In the File Navigator, browse and open file C:\wkdir\PROC_EMP_CURSOR.sql . Notice the new overlay icon on the file in the File Navigator.

Delete the Exception block.

Add the following comment and click the History tab.

--To add an exception block type excep and the press Ctrl+Shift+t.

 

13.

The History tab displays the changes you have done to PROC_EMP_CURSOR.sql . Right click on a version and move between various changes done.

 

14.

Click File > Save. Notice the change in the icon overlay.

 

15.

To check the file in, in the File Navigator, right-click the file PROC_EMP_CURSOR.sql and select Versioning > Commit.

 

16.

Click Ok in the Commit Resources window.

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.

Open SQL Developer. Click the File Open button on the standard toolbar.

 

2.

Open the file C:\wkdir\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

In SQL Developer 1.5 you can integrate Application express and perform the following operations:

A Browsing applications
B. Deploying an application
B. Executing an Application Express report

Back to Topic List

A. Browsing Applications

You can use the intuitive SQL Developer browser to view details about your applications. To browse through your application, perform the following steps:

1.

Open SQL Developer. In the Connection Navigator expand Connection > hr. You can see a new node named Application Express.

 

2.

Expand the Application Express node to display a list of all the applications owned by hr schema. Click the name of the application to display the application-level details, which includes Lists of Values, Lists, Templates, Tabs, etc.

                                
                            

 

3.

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

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

 

4.

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

 

5.

Open the Add Modify Customers page.

Drag the Add Modify Customers page under the Master Detail page.

Since the Add Modify Customers and the Master Detail page are now both open, you can easily compare the values.

Back to Topic

 

B. Deploying An Application

If you need to copy an application from one instance to another or one workspace to another, rather than exporting and then importing, you can just use the deploy feature. To deploy an application, perform the following steps:

1.

To execute this section, using application express admin account, create a workspace testWS in Test schema .

 

2.

Open SQL Developer. In the Connection Navigator expand Connection > hr > Application Express .

 

3.

Right-click on the Sample Application. Select Deploy Application.

                                
                            

 

4.

In the Deploy Application window choose the connection as test.

Then click Next.

 

5.

In the Choose Import Options window of the Deploy Application wizard, change the Application Alias to DEMO_APP3 and then click Next.

 

6.

Review the details of the deployment in the Import Summary window and then click Finish.

On successful import a window titled Success is displayed. Click OK.

 

Back to Topic

 

B. Executing An Application Express Report

If you need to copy an application from one instance to another or one workspace to another, rather than exporting and then importing, you can just use the deploy feature. To deploy an application, perform the following steps:

1.

Open SQL Developer. In the Reports Tab expand All Reports > Data Dictionary Reports > Application Express. There is a report for Applications, Pages, Schemas and Workspace.

 

2.

Click Application. A Select Connection window appears, select hr and click OK.

                                
                            

 

3.

A report consisting details for each application is displayed.

Select an application from the report above and then the details for that application will be displayed below.

 

4.

To run a report in SQL worksheet, select an application and then click the Run Report in SQL Worksheet icon.

Within the SQL Worksheet the SQL for the report is displayed which is customizable.

 

Back to Topic

Back to Topic List

Summary

In this tutorial, you learned how to use the new features added to 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