Let's start with a walk-through of the product. This book is all about SQL Developer 4.1, using the product, and getting to know it. You may well ask yourself why there is a need for a book if we can walk through the product in twenty minutes or less. Generally only 10% of the SQL Developer features are used by people on average, but their sweet spots are probably different. By spending a little time delving into a number of areas of the product, you can start laying down a map of how the pieces connect and provide a base that you can drill down into later and become more productive.
To follow the examples in the book, you need access to SYSTEM schema of a database and some of the shipped sample schemas, HR, OE, SH, PM, and IX available in Oracle Database 9i, 10g, 11g, or 12c. Specifically, this book uses the sample schemas shipped with Oracle Database 11g R2 & 12c.
There are two ways to install the sample schema. The first way is when you install the database. You can elect to have the sample schema installed at that point.
Second, if you have not installed these, then you can locate the sample schema in the $ORACLE_HOME/demo/schema folder and follow the instructions on installing them using the Oracle online documentation. Not all of these schemas are available for Oracle Express Edition. In this chapter, we use SYSTEM to verify that the HR schema is unlocked, and then we use the HR sample schema, which is available in Oracle Express Edition.
To complete this quick walk-through, you need to know the username and password of the SYSTEM user. You also need to know the location of the database, whether this is the machine name or the IP address, and the database SID.
To begin, start SQL Developer. The very first time you start SQL Developer, you'll be asked if you want to migrate from a previous version. Select No and allow the tool to start up.
The first thing you need to do after you have started SQL Developer for the first time is to create your initial connections.
To create a connection for SYSTEM, follow these steps:
Select Connections, right-click and select New Connection. This invokes the New Database Connection dialog. You can edit and control all of the connection details using this dialog.
Complete the details, as shown in the following screenshot, relevant to your environment.
Click on Test to ensure that you have the connection details correct and click on Connect.
You are now connected as
SYSTEM. Use this connection to verify your other users, by continuing with following steps.Select the new connection you have created, expand the node, and scroll down to Other Users.
Expand Other Users and find the user HR. Right-click on it and select Edit User. Verify that the account for HR is unlocked and the Password has not expired, that is, the properties Account is Locked and Password Expired are deselected. If either of these is selected, deselect them. You can change the password for HR at this point too. It's good practice to modify the passwords of the shipped sample schemas once you have unlocked them.
Now you are really ready to begin.
Once again, select Connections, right-click and select New Connection.
Give the connection a name (for example,
HR_11g).Provide the Username (HR) and a Password. If you are working on Oracle Database 11g, be aware that passwords are now case sensitive.
Select the Save Password checkbox. This makes life easy while you are working with SQL Developer. Passwords are stored in an encrypted file. However, you should always be aware of saving passwords and possible security implications this may have.
Use the Basic connection. This requires no more detail than the location of the database and the SID, details you have.
Click on Test to test the connection.
Click on Connect.
As soon as you connect to a user, SQL Developer opens an SQL Worksheet. You may have started working with Oracle using the SQL*Plus command line, or even the GUI window. Either way, you'd start with a selection of SQL*Plus and SQL commands.
Enter the following into the SQL Worksheet:
DESC DEPARTMENTS SELECT * FROM DEPARTMENTS;
Press the F5 key (or use the Run Script button).
The output of both commands appears in the Script Output tab, which appears below the SQL Worksheet (as seen in the previous screenshot). Both commands are handled by a few simple clicks of the mouse in SQL Developer.
Select and expand the HR_11g connection in the Connections navigator. Expand the Tables node and select DEPARTMENTS.
The DEPARTMENTS tab now opens, displaying a list of the column names and details. These are the same details as given by the DESC (describe) SQL*Plus command that you entered in the SQL Worksheet. It also provides additional detail, such as the Primary Key and column comments.
Select the Data tab and notice that you now see the output from your second command. These two tabs are included with a number of other tabs, each with additional details about the DEPARTMENTS table. You would need to write a number of SQL queries in order to get the additional detail from the data dictionary if you were working in SQL*Plus.
Select the EMPLOYEES table. Notice that the new table, EMPLOYEES, immediately replaces the previous DEPARTMENTS table with its details. Select the Triggers tab, and select one of the triggers. The trigger and related trigger detail is displayed in a master-detail window, as shown in the following screenshot:
Return to the EMPLOYEES data by again selecting the Data tab. The data grid that is displayed provides a variety of options. To get started with the data grid, double-click on an item or field, such as the name of one of the employees, and change it. Tab out of the field and notice that the change is applied to the data grid and an asterisk (*) flags the record. Commit and Rollback buttons are available to send the change to the database, or to undo your action. Roll back the changes.
Once again, you get feedback, this time in the Messages Log, as shown in the following screenshot:
To run your report, perform the following steps:
Select the Reports navigator and expand the Data Dictionary Reports node. Expand the Table node and review the available reports. Expand Constraints and select the Unique Constraints report.
As you select the report, a dialog displays requesting the Connection name. Select the connection you created, HR_11g, and click on OK.
An Enter Bind Values dialog now appears, requesting the table name as an input parameter. Click on Apply to accept the default, which in this case, means all tables:
