Using SQL Developer

If your primary job role is a DBA, you will most likely spend most of your time using SQL Developer to access Oracle Database XE, or any version of Oracle version 9.2 or later. SQL Developer provides these key features:

Creating and editing database objects: You can connect to any database and create, edit, view, or delete objects to which you have permission. You can extract the DDL for any object, including tables, views, sequences, functions, procedures, and so on.

Running SQL commands: You can use SQL Worksheet to create and execute SQL, PL/SQL, and SQL*Plus commands.

PL/SQL editing and debugging: You can create and edit PL/SQL anonymous procedures as well as functions, procedures, and packages. Code formatting makes your PL/SQL code more readable, and breakpoints make it easy to step through your code line by line when you are debugging a PL/SQL procedure.

Reporting: SQL Developer includes many predefined database and object reports that can easily be customized for your environment.

To get SQL Developer, navigate to sql/index.html. You can run SQL Developer on Windows, Linux, or even Mac OS X. Each platform has two links; you can download the smaller file if you already have JDK 1.5 installed. Extract the files to a directory with at least 125MB of free disk space. You can put the extracted files anywhere, as long as you preserve the directory structure—no installer required. This applies to both Windows and Linux.

In a Windows environment, locate the file sqldeveloper.exe in the directory sqldeveloper and double-click it; that's it! You might find it useful to create a desktop shortcut for sqldeveloper.exe to make it even easier to start the next time by right-clicking sqldeveloper.exe then selecting Send To > Desktop (Create Shortcut).

For Linux, the process is very similar. Locate the file sqldeveloper in the directory sqldeveloper and start it this way:

[[email protected] bin]$ sh sqldeveloper

Most if not all Linux GUIs support desktop shortcuts to this command just as in a Windows environment.

In the following example, you start SQL Developer (on either Windows or Linux) and create a connection to an Oracle Database XE instance on the same workstation where you started SQL Developer. On the left-hand side of the window, right-click Connections and select New Database Connection. Enter the following values in the dialog box that appears:

Connection name: orcl_hr (or any name that makes it easy to remember the local database connection for this user)

Username: HR (or any username that exists for this database) Password: the password you assigned to the username

Hostname: localhost (i.e., the same server on which you are running SQL Developer) SID: xe (the default Oracle Database XE instance name)

Select the Save Password checkbox if you want SQL Developer to remember the user's password the next time you start SQL Developer. Click Test if you want to make sure that the connection works; otherwise, go ahead and click Connect to establish a permanent connection to the database and begin browsing database objects. You only need to set up this connection once; SQL Developer saves your connection definitions and makes them available on the left-hand navigation pane every time you start SQL Developer. In Figure 29-3, you connect as the user HR and browse the structure of the EMPLOYEES table.

F Oracle SQL Developer

F Oracle SQL Developer

Figure 29-3. Browsing database objects in the HR schema

Was this article helpful?

0 0

Post a comment