Database Connections and SQL Worksheet

In this article written by Ajith Narayanan, author of the book Oracle SQL Developer, we learn one of the most important navigation windows is the Connections window pane. This is the window where we have all our predefined database connections. Once we have added the required connection definitions to the databases, we can quickly launch the connections to the databases and start working. Another important fixed window after making database connections is the SQL Worksheet window. The SQL Worksheet provides a scratch pad for all SQL, PL/SQL, and SQL*Plus statements and commands. You can run individual statements or a collection of commands. You can also run scripts and execute PL/SQL. In essence, you can do all you want in a command-line interface such as SQL*Plus. The SQL Worksheet provides more; it offers code templates, code snippets, assists with code insight and completion, and maintains a history of commands. We will now look at the Connections window and other features offered in the SQL Worksheet and how to use them. We will use SQL and SQL*Plus commands to illustrate features, using PL/SQL only where needed specifically for a feature.

(For more resources related to this topic, see here.)

Working with the Connections navigator

The Connections navigator lists all our database connections created by us. A double click on each of those database connections will open the database connection for us using the saved credentials for that particular database. Objects are grouped into nodes in the Connections navigator to reflect their types. They are sorted by Most commonly used with Tables, Views, and Indexes, at the top of the list. You can refer to the following screenshot to see the grouping, order, and some of the currently available types displayed in the Connections navigator. The selection of browsable object types available increases with each release as the SQL Developer team adds support for more features. SQL Developer allows you to explore the contents of your database using the connection tree. A complete list of supported database object types follows:

Opening connections

To open a connection in the navigator, follow these steps:

  1. Under the Connections navigator, expand any of the existing database connection nodes to establish the database connection.
  2. The second option is to right-click on any of the database connection nodes under the Connections navigator and select Connect from the context menu.

The first time you connect to a database schema, whether you open an existing connection or click on Connect in the New Database Connections dialog, SQL Developer automatically expands the connection in the Connections navigator and opens a SQL Worksheet. This automatic opening of the SQL Worksheet is controlled by a preference: Open a Worksheet on connect. Navigate to Tools | Preferences, expand the Database node and select Worksheet.

Reconnecting users

When doing administrative work with users, it can help to disconnect the user you are working with before making the changes, and reconnect them afterwards. Some actions, such as dropping users or renaming connections, will not be possible without first disconnecting the connection.

Database schema or user?

The Oracle Concepts Guide states,

"A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user."

Throughout the text, we use schema and user interchangeably. For most part, we refer to the SYSTEM and HR schemas, meaning the collection of database objects. When closely or directly related to an activity, we use "user", as the code does this. For example, DROP user HR cascade; a piece of code that drops all of the objects in the schema and the user itself.

Working with database objects

To work with any object, select and expand the respective object node. The most common node you'll work with is the Tables node. This displays all of the tables the user owns (all of the tables in the schema). Each object node is identified by an icon, and the Tables node highlights some of the main table types using these icons. Not all are singled out, but the more commonly used ones are. If you expand the HR Tables node, the COUNTRIES table (which in the sample is an index-organized table) is identified by the slightly different table icon used. Partitioned tables are also distinguished from regular, simple tables using icons. The following screenshot displays the index organized as regular, external, partitioned, and temporary icons:

Display editors

Once you have expanded an object type node in the Connections navigator, selecting any object in that node opens a window of tabs, called display editors, which define the object. Each editor displays a data grid of details describing the selected object. These are based on queries against the data dictionary and you can get the same results by writing the SQL yourself.

The number and types of editors displayed will vary depending on the object or database that you are connected to. If you are connected to Oracle Database 11 g or above, then an additional Flashback editor is displayed with the other table display editors.

The Partitions tab is permanently displayed from SQL Developer 2.1.

General display editors

Instead of itemizing each of the object types and the different collections of display editors, we'll use the Tables node to review some of the display editor details. Using the HR connection, select EMPLOYEES in the Tables node to see the general display editors:

The Columns editor displays all of the column detail, including column comments. To get a feel for queries that run behind the editors, run the following query in the SQL Worksheet:

SELECT COLUMN_NAME,DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME ='EMPLOYEES'
AND OWNER = 'HR';

The output from the query matches the first two columns of the Columns display editor (this is a simplified example). If you need to find out more information about any object in the database, without a GUI tool, you need to start by querying the data dictionary to determine which tables hold the metadata about your objects. From there, you must decide what detail you need from these tables, in our example, it was the single ALL_TAB_COLUMNS table. After that, we write the join clause to query all of the selected tables.

There is a set of editors for each of the object types. For tables, these include Constraints, Grants, Statistics, Triggers, and Partitions. The data in each data grid is a result of a query joining a number of data dictionary tables. SQL Developer provides a framework for you to create and add your own display editors. You can do this with user extensions.

Some of the editors display master-detail output. To see the detail result set you need to select an individual record in the master result set. The following screenshot shows the EMP_NAME_IX for the EMPLOYEES table. By selecting the index, you can quickly see that this index is made up of two columns:

To create the index using SQL, use the following query:

CREATE INDEX "EMP_NAME_IX" ON "EMPLOYEES" ("LAST_NAME", "FIRST_NAME");

Working with the display editors

Each new object selected in the navigator replaces the last, regardless of object type or connection, unless you click on the Freeze View pin () on the object display. This locks the window and a new window opens with a new set of editors.

To see this feature in action, expand the HR Tables node. Select the EMPLOYEES table and note the set of display editors. Now, select the DEPARTMENTS table, and note that it replaces the details of the EMPLOYEES table. Expand the Sequences node and click through each of the sequences available. These now replace the tables which were previously displayed. This replacing feature is very useful as it saves screen real estate, and keeps the screen and work area uncluttered. However, it is also very useful to be able to compare two sets of data, side by side. Therefore, by selecting the Freeze View pin, each new object selected opens in a new window and you can then tile the windows.

Freezing the Object Viewer

You can control whether each new object selected opens a new set of editors. Navigate to Tools | Preferences in the tree display and expand Database. Next, click on ObjectViewer and select Automatically Freeze Object Viewer Windows.

SQL Developer automatically opens the display editors as you click on the object, or if you navigate through the objects using the keyboard. You can control this by changing the default Open Object on Single Click behavior. Navigate to Tools | Preferences, in the tree displayed, expand Database, select ObjectViewer, and deselect Open Object on Single Click.

Using the SQL display editor

The SQL editor is displayed at the end of the set of shipped display editors and is available for most object types (any editors you create are added after the SQL editor). The SQL editor displays the query required to recreate the object selected. When you select the SQL editor, SQL Developer uses the package DBMS_METADATA to query the database and return the SQL required for recreating the object selected. So, clicking on the editor with a table selected displays the SQL (DDL) for that table.

Select the COUNTRIES table in the Connections navigator. The default display provides the full CREATE TABLE SQL in a single statement as shown in the following screenshot:

Working with the data grids

The contents of each display editor are displayed in data grids, which typically have three or more columns of data within the grid. A few are two column name-value pair data grids, such as the Details editor. The data in these grids is not editable and merely reflects the details of the object or structure selected. There are two exceptions. The first exception is the Data editor included with the set of display editors for certain objects, such as tables and views. The Data editor displays the instance data for a table and, depending on the object, this data can be edited and the changes can be committed to the database. The second exception is the Code editor for PL/SQL objects, where you are placed into a PL/SQL editor when you select the object.

Data grids throughout SQL Developer have context menus on the column's headings and the data grid itself. You can control the layout and what data is displayed by using these two context menus. For the remaining portion of this section, we'll review the various options on these context menus.

The following screenshot shows the customizable columns' capability of the data grids:

The following screenshot shows the sorting capability of the data grids:

The following screenshot shows the data filtering capability of the data grids:

The following screenshot shows the data finding/highlighting capability of the data grids:

Reviewing other database object nodes

As you select each of the other database objects, you'll notice that the set of display editors varies considerably.

It would be tedious to single out each of the object nodes and describe them here. The display editors and data grids behave the same for each of them.

Working with PL/SQL objects

Triggers, functions, procedures, and packages all have their own separate nodes in the Connections navigator. Clicking on any object in these PL/SQL nodes opens editable PL/SQL code:

In the screenshot just displayed, the initial Code editor is the editable PL/SQL code editor. This Code editor is included in the set of display editors for the selected procedure.

Unlike with other database objects, each new selected PL/SQL object opens a new window. In other words, the windows are automatically pinned for the PL/SQL windows.

Accessing objects you don't own

Very often, you might have the connection details for one schema that has access to objects in another schema. One of the most frequently asked questions on the SQL Developer forum is about viewing objects that the schema does not own.

Other users

As discussed, the objects under your connection are objects created or owned by the schema defined in the connection. To view any other objects that your connection (schema) has access to, use the Other Users node. When you expand Other Users for your current connection, you are executing the equivalent SQL querySELECT * FROM ALL_USERS;. This query returns all of the users to the current database. However, it does not mean that you have access to the objects in each of those schemas.

To review the objects the HR schema has access to, expand the Other Users node for the HR connection. Select and expand the user OE, and then expand the Tables node. You should see the selection of OE tables displayed. Not only does HR have access to these objects, the HR schema can query the data. You can select each table and display the set of editors available. HR is not a privileged user, but is granted the SELECT ANY TABLE system privilege.

The user SCOTT/TIGER is another of the Oracle database shipped schemas. SCOTT was the original sample user and is not a privileged user. If you have access to SCOTT, create a new database connection for SCOTT and repeat the exercise. If you expand the OE or HR tables nodes under SCOTT's Other Users node, the nodes will be empty. SCOTT does not have access to these objects.

Synonyms and filters

You can expand and work with objects you have access to in the Other Users node. You can also create synonyms for these objects and then, using Filters, display the objects within each respective object type node in your connection.

Consider the following example, the HR schema has access to query the OE's CUSTOMERS table. HR can query the table, using SELECT * from OE.CUSTOMERS;. Now, create a synonym using CREATE SYNONYM CUSTOMERS FOR OE.CUSTOMERS;. This means the HR schema can write the queries without having to refer to the OE schema.

In SQL Developer, the synonym definition appears in the Synonyms node. The CUSTOMERS object will appear in the Tables node by setting the Include Synonyms option in the Filter dialog.

In the following screenshot, the tooltip shows that the CUSTOMERS table belongs to OE. The Tables node is also marked as Filtered:

Recycle Bin

The Recycle Bin was introduced to Oracle databases in Oracle Database 10 g. SQL Developer displays the contents of the recycle bin in the Recycle Bin node. For supported databases, the Recycle Bin provides a listing of all objects deleted from the schema. The information displayed about dropped objects, includes the date the object was created and dropped, and whether the object can be undropped (reinstated) or purged. SQL Developer displays the original name of the object in the Connections navigator for ease of use. However, once dropped, the object has a new name. This allows you to drop objects and create new ones without the dropped object name blocking the action. In SQL Developer, displaying the old name makes it easier to decide what object you are dealing with, unless of course you repeatedly recreate and dropped the object.

Creating new database objects

We previously looked at browsing objects and data in the database. If you are an application developer and working with a completed database design, you might spend most of your time doing just that. It is more likely though, that you'll need to make changes such as adding or modifying structures and data. SQL Developer provides the dialogs to assist you.

In the same way that you can write SQL queries to extract information about the objects you have access to, you can write the SQL Data Definition Language (DDL) to create, edit, and modify these objects. Almost all of the object nodes in the Connections navigator provide a context menu to create a new object. To invoke a dialog that creates any new object, select the object in the navigator and invoke the context menu.

The following list is an exception for creating new objects using context menus:

  • Editioning Views
  • Queues
  • Queue Tables
  • Cross Edition Triggers
  • Directories
  • Editions
  • Application Express
  • XML DB Repository
  • Recycle Bin

Right-clicking on these listed object nodes under Connection navigator will not provide us with the Create New option.

Editing database objects: putting context menus to work

Each object in the Connections navigator has a context menu associated with it. Each context menu invokes a dialog that supports SQL commands that range from a single, simple command (such as DROP TABLE HR.COUNTRIES) to a collection of basic and complex commands. Throughout we have stated that any of the activities we're performing uses a UI as an interface for the underlying SQL command. Certainly, typing the drop table command is faster than selecting the items and invoking the dialog, assuming, of course, that you know the syntax. In this section, we'll briefly single out a few of these context menus.

Editing objects

Each of the Create dialogs has a matching Edit dialog. Most of the Edit dialogs directly match the object's Create dialog. If you invoke any Edit dialog, the DDL tab or node is initially empty, as it was when you first invoked the Create dialog. As soon as you change any of the properties, the DDL tab or node is populated with the SQL to reflect the changes. For many objects, such as triggers or views, editing the object results in a Create or Replace… command. For other objects, such as Sequences or Tables, editing the objects results in an Alter… command.

Consider the Edit Table dialog as shown in the following screenshot. The dialog is in the form of the advanced Create table dialog. Notice that you can no longer switch the table type, nor create partitions. While you can certainly add or delete columns, you cannot reorder them (unless you drop and recreate them). You'll find that other database rules, such as reducing the column width, are also enforced if the column already contains data.

Script Runner/ Running scripts

Possibly, one of the most commonly used group of SQL*Plus commands are those that run the scripts: @, @@, and start. The script runner emulates a limited set of SQL*Plus features. You can often enter SQL and SQL*Plus statements and execute them by clicking the Run Script icon. The Script Output pane displays the output.

You need to use @@ when running one script that calls a second or third script. In this case, it is necessary to set the path for the top-level file.

For either, set the path using the Database | Worksheet preference. Press F5 to run the script.

Set the default path for executing SQL scripts

To set the default path for scripts, invoke the Preferences dialog and set the path to Database | Worksheet. Click on Browse to locate or enter the path.

As with SQL*Plus, if you use @ or start to run a single SQL script file, you can either enter the full path (for example @C:\Performance_Issues\MyScript.sql), or you can set the path in the Preferences dialog:

Few exceptions apply in SQL Developer script runner. For example, use of bind variables is not supported. (Bind variables of type VARCHAR2, NUMBER, and DATE.)

For substitution variables, the syntax &&variable assigns a permanent variable value, and the syntax &variable assigns a temporary (not stored) variable value.

For EXIT and QUIT, commit is the default behavior, but you can specify rollback. In either case, the context is reset, for example, the WHENEVER command information and substitution variable values are cleared.

DESCRIBE works for most, but not all object types for which it is supported in SQL*Plus.

Execution plan

The Execute Explain Plan icon generates the execution plan for a given query in a single click. The execution plan shows us the sequence of operations that will be performed to execute the SQL statement. An execution plan shows a row source tree with the hierarchy of operations that make up the statement. For each operation, the ordering of the tables referenced by the statement; access method for each table in the statement; join method for tables; and data operations such as filter, sort, or aggregation. The following is a screenshot of the Explain plan feature:

The plan table also displays information about optimization (such as the cost and cardinality of each operation), partitioning (such as the set of accessed partitions), and parallel execution (such as the distribution method of join inputs). Later we will be discussing more on this feature.

Summary

We reviewed the Connection navigator pane and then proceeded with SQL Worksheet and all its features that can assist in your daily tasks.

Resources for Article:


Further resources on this subject:


You've been reading an excerpt of:

Oracle SQL Developer

Explore Title