Oracle SQL Developer 2.1 New Features

Install Oracle Database 11g with Sample Schema.

Note: You can also use earlier databases or XE.

Unlock the HR user. Login to SQL*Plus as the SYS user and execute the following command:
alter user hr identified by hr account unlock;

Unzip the Oracle SQL Developer software.

Note: This tutorial is developed using Oracle SQL Developer 2.1.1. However, you can also use Oracle SQL Developer 2.1.0.

Download and unzip files.zip to your system.

Open Oracle SQL Developer.

To create the connection, right-click Connections in the Connections navigator and select New Connection.

The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.

Connection Name: hr_orcl
User Name: hr
Password: hr (Select the Save Password checkbox)
SID: orcl (or your own SID)

If the Status is Success, click Connect.

A connection to the HR schema is created.

Expand the hr_orcl connection you just created.

To apply a schema level filter, select hr_orcl and click the Apply Filter... () icon. Alternatively, right-click hr_orcl and select Apply Filter....

Note: The Tables node is already filtered because an 'Exclude Recycle Bin' filter is applied to it by default.

To list all objects starting with the letter E, enter the E% in the textbox and click OK.

Expand the Tables, Views, and Indexes nodes to see the filter effect. Only object names starting with an 'E' are listed.

To apply a filter to the Tables node, right-click Tables and select Apply Filter.... Alternatively, select the Tables node and click the Apply Filter... icon.

A Filter dialog opens. To add a new filter, click the Add Criteria () icon.

Note: The choice of filter options varies depending on the object selected.

Click the first drop-down list to view the choices available.

Note: Prior to SQL Developer 2.1, only four options where available in this list.

To display tables starting with the letter D, enter D% in the textbox.

You have an option to override the schema filter applied previously. Select the Override Schema Filter checkbox and click OK.

Only the DEPARTMENTS table is displayed under the Tables node.

To remove the schema filter, right-click hr_orcl and select Clear Filter.

Expand the Indexes node. The schema filter is removed.

To remove the filter applied to the Tables node, right-click Tables and select Clear Filter.

Expand the Tables node. The object filter is removed.

Select the EMPLOYEES table from the Connections Navigator. The EMPLOYEES data grid opens in a new tab.

To invoke the context menu, right-click any of the column headers.

Select Columns....

A Manage Columns dialog opens, which allows you to reorder and hide or show the columns. To place the COLUMN_ID column after the COLUMN_NAME column, select COLUMN_ID and click the Up button () three times.

To hide the NULLABLE column, select NULLABLE and click the Remove () button.

Similarly, to hide the COMMENTS column, select COMMENTS and click the Remove button.

Click OK to apply the changes.

The changes you made are reflected on the data grid.

Invoke the context menu again by right-clicking any of the column headers.

Select Sort....

Note: As available in releases prior to SQL Developer 2.1, columns can also be sorted by double-clicking the sortable column headers. (Column header with this icon are sortable). However, with this method, you can perform only single column sort.

In the Sort Columns dialog, you can perform single or multiple column sort. Confirm that the COLUMN_NAME column is selected and click the Add button.

To also sort by the DATA_TYPE column, confirm that DATA_TYPE is selected and click the Add button.

To change the default Ascending order sort, select the Descending radio button.

Click OK to sort by the two selected columns.

The columns are displayed according to the sorted columns. The blue up and down arrows on the sorted columns represent the ascending and descending sorts. The numbers 1 and 2 signify the order of the sort.

Click the Constraints tab.

To apply a filter on the CONSTRAINT_TYPE column, click the CONSTRAINT_TYPE column header.

A filter popup opens. To display only Check constraints, select Check and press the Enter key, or double-click Check.

The CONSTRAINT_TYPE column is filtered to display only the Check constraints. A filter icon is displayed on the column header of the filtered column.

To remove the filter, right-click any of the column headers and select Remove All Filters.

You have cleared the applied Check filter.

Click the Data tab.

Right-click any of the data cells to invoke a context menu.

Select Find/Highlight....

A Find / Highlight popup opens. You can search for a string in any cell of the grid or in a particular column. To search for names starting with 'sa', enter sa in the textbox and select the Starts With checkbox. To ignore the casing, select the Ignore Case checkbox.

Click the Find next arrow () to the right of the textbox to start the search.

The search returns 40 results. This includes the SA_MAN and SA_REP values in the JOB_ID column as the search option is for all columns by default.

Note: You may get other search results (like 5). This may have to do with the cell/column you click to open the Find / Highlight window. You can repeat the search again by clicking elsewhere. If you do not get 40 results, its ok. You can proceed to the next step where you will refine the search.

To search in the FIRST_NAME column only, click the Find icon () to the left of the textbox and select FIRST_NAME.

Click the Find next arrow to start the search again.

The search now returns 3 results. Click the Find next arrow to see all the values.

You can highlight the rows that contain these values. Select the Highlight radio button, the Highlight Row checkbox and click the Find next arrow.

Scroll to see the highlighted rows.

You can search for different values and highlight rows with different colors. To search and highlight rows with manager ID 101, click the Find icon to the left of the textbox and select MANAGER_ID from the drop-down list.

Enter 101 in the textbox and select a pink color.

Select the Persist Highlight checkbox and click the Find next arrow.

Scroll to see the highlighted rows.

Note: The search criteria is now listed under Persisted Highlights.

To highlight rows with salary as 3000, click the Find icon to the left of the textbox and select SALARY from the drop-down list.

Enter 3000 in the textbox, select a yellow color, and click the Find next arrow.

The rows are highlighted in yellow. To disable the manager_id= 101 (pink) highlight, deselect the checkbox next to 101 under Persisted Highlights.

The pink highlight is removed. To delete the salary = 3000 (yellow) highlight, click the red cross next to 3000 under Persisted Highlights (use the scroll in the popup to see the yellow highlight).

Note: You can resize the Find / Highlight window to view all the persisted highlights.

The yellow highlight is removed and deleted from the Persisted Highlights section.

To close the Find / Highlight popup, click the close button ().

You can save the SQL query, which produced the grid, as a User Defined Report. Right-click any of the data grid cells to invoke the context menu.

Select Save Grid as Report....

A Create Report dialog opens. Enter Employees List for name and click Apply.

To view the report, click the Reports navigator.

Expand the User Defined Reports node.

The created report is listed. To run the report, select Employees List.

Confirm that the connection is hr_orcl and click OK.

The report data is displayed.

Close the open tabs and select the Connections navigator. To open the script file, select File > Open.

Browse to the location where you unzipped files.zip and open the update_employees.sql file.

Review the code. The input to the update_employees procedure is employee_id and percent. The procedure increments the employee's salary by the input percent value.

Select hr_orcl connection for the worksheet.

To extract a new procedure, select the UPDATE and DBMS_OUTPUT statements and right-click to invoke a context menu.

Note: If you select an incomplete piece of code, and error message is displayed.

Select Refactoring > Extract Procedure....

A New Procedure dialog opens. Ensure that the Stored radio button and the Call with Named Arguments checkbox are selected.

Note: If you do not see the Stored and Call with Named Arguments options, ensure that you performed step 4.

Enter raise_salary for Name and click OK.

A Confirm Running SQL dialog opens. Review the code and click Yes.

The procedure is created successfully and inserted in place of the existing code.

To view the procedure, expand the Procedures node.

Note: If you do not see the new procedure, click the Refresh icon.

The RAISE_SALARY procedure is created. The green color on the procedure icon specifies a refactored code. Select RAISE_SALARY.

To execute the procedure, click the Run icon.

A Run PL/SQL dialog opens. Change the NULL values to 100 and 10 and click OK.

The procedure is executed successfully.

You need to rewrite the update_employees procedure to increment the salary of all employees who were hired before Jan '95. Click the update_employees.sql tab.

Copy and paste the following code before the BEGIN keyword. The code uses a cursor to store the EMPLOYEE_ID of the employees hired before Jan '95.

CURSOR emp_cursor
IS
SELECT employee_id FROM employees where hire_date < '01-jan-95';

Delete the parameters of the procedure.

To call the raise_salary procedure for each EMPLOYEE_ID retrieved, you need to enclose the statement in a loop. Select the statement and right-click to invoke the context menu.

Select Refactoring > Surround with > FOR Loop....

The statement is inserted inside a FOR loop.

Type emp_cursor in place of 1..n.

Delete the existing parameters of the raise_salary procedure and insert the following:

x.employee_id,10

To execute the code, click the Run icon.

The procedure is compiled successfully. To view the procedure, select the Procedures node and click the Refresh icon.

Select UPDATE_EMPLOYEES.

To execute the procedure, click the Run icon.

Click OK.

The procedure is executed successfully.

Select Tools > Preferences.

A Preferences dialog opens. Expand Database.

Select SQL Formatter and expand it.

Select Oracle Formatting.

To update preferences, click Edit.

Note: Ensure that SQL is selected for Profile.

A Oracle formatter dialog opens. Expand each node and review the settings. Check/uncheck some options and view its effect in the sample code shown on the right.

Expand Line Breaks.

Deselect the checkbox for After SELECT, FROM, WHERE and Before AND/OR.

Select checkbox for After AND/OR, enter 2 for Number of Commas per Line, and click OK.

Click OK in the Preferences dialog.

Right-click in the SQL Worksheet and select Format.

The code is formatted successfully.

To open the Data Modeler in SQL Developer, select Tools > Data Modeler.

From the Tables node in the Connections Navigator, select the DEPARTMENTS table.

Press and hold the Shift key and select the EMPLOYEES and JOB_HISTORY tables.

Select the Data Modeler tab.

Drag and drop the selected tables onto the Data Modeler Relational_1 tab.

You can view the structure of the tables. You can not save the model, but you can move the objects on the diagram to change the default layout.

To view the structure of the EMP_DETAILS_VIEW view, expand the Views node.

Drag and drop EMP_DETAILS_VIEW to the diagram.

The structure of the EMP_DETAILS_VIEW view is displayed.

To close the model, select File > Close Data Modeler Design.

To open a SQL Developer Data Model, select File > Open Data Modeler Design.

Browse to the location where you unzipped files.zip and open the OE_Model.xml file.

Click OK.

You have successfully opened the OE schema model.

To review the details of the model in a navigator, select View > Data Modeler > Browser.

After the model and the Data Modeler browser is opened, you can browse and review all of the model properties. From the Data Modeler browser, expand Relational Models [1].

Note: The model and properties are read only.

Expand Relational_1 and Tables[7].

Right-click the ORDERS table and select Properties.

Click Columns on the left navigator.

Review the column details and click Close.

You can also select a table from the diagram to view its properties. Right-click the CUSTOMERS table and select Properties.

Select Columns from the left navigator and review the column properties. When done, click Close.

You can check the design of the relational model. Select Tools > Data Modeler > Design Rules.

A Design Rules dialog opens. You have the option to apply all rules to the model or select a particular rule to apply. To identify tables without the Primary Key, expand Relational > Table.

Select Identify tables without primary key and click Apply Selected.

You get a Check Design - OK message. Click OK.

To apply all the design rules, click Apply All.

Any errors or warnings are displayed. View the warnings and click Close.