EnterpriseDB Developer Studio Toolbar Oracle Tips by Burleson Consulting

Figure 2.22: Developer Studio Toolbar

The navigator pane is the key to Developer Studio.  The navigator is set up like Windows Explorer or any other tree based navigation tool.  From the navigator, you can click on a plus sign (+) to drill down to lower levels of details.

In the properties pane, there are four tabs:  Properties – shows general information about the object highlighted in the navigator pane; Statistics – shows statistics on an object if applicable; Dependencies – lists objects upon which the highlighted object is dependent; and Dependents – lists objects dependent upon the highlighted object.

In the SQL pane, you can see, but not edit, source code.

If you double click on an object in the navigator, it will launch the SQL Interactive window.  If you double click on an object that can be selected from (like a table or a view), the SQL Interactive window will launch with a SELECT statement for that object (which is the same as if you right-clicked and selected View Data), Figure 2.23.  If you double click on an object with source code (function, procedure, package, trigger), the SQL Interactive screen will launch with the code loaded and ready for compilation.

Figure 2.23: SQL Interactive

The SQL Interactive window is intuitive.  Along the top (1) is a toolbar.  The running man icon is to run (or perform) an action.  In the case of a query, the query is executed.  In the case of source code, the code is compiled.  The running man icon is a graphical short hand that means “Make it so, Number one!”  Pressing the F5 key has the same effect as clicking the running man icon.

Next to that are the standard dialogs: Open, Save and Save As.

The next drop-down list offers two choices:  Grid Output – loads the query results in a grid with resizable columns; and Text Output – displays the results as continuous, scrollable text.

The Autocommit checkbox applies only when Grid Output is selected, otherwise it is deactivated.

If Autocommit is checked as in Figure 2.24, then all the SQL statements that are executed in the query area are automatically committed provided that all of the SQL statements executed without error.

Figure 2.24: Autocommit

If any SQL statement results in an error, then none of the SQL statements are committed.  In other words, the SQL statements are submitted as one transaction when you click the running man icon.

If Autocommit is not checked as in Figure 2.25, then explicit transaction control is enabled and the rollback icon (with the orange arrow) and the commit icon (with the green arrow) are activated.

Figure 2.25: Explicit Transaction Control

When explicit transaction control is enabled, regardless of how many SQL statements are executed and how many times you click the running man icon, no statement is committed until you click the commit icon (unless of course, one of the SQL statements you execute is a COMMIT statement).  If you click the rollback icon, all SQL statements executed since the last time you clicked the commit icon or executed a COMMIT statement are rolled back.  By default, when you exit SQL Interactive, the last action taken is a rollback.  Thus, if you inadvertently delete your entire table, you can either click the rollback icon or simply exit from SQL Interactive and the deletion will be rolled back.

The default rollback action when you exit SQL Interactive can be changed in the Preferences dialog box, Figure 2.26, found under the Tools menu option of the Developer Studio main menu.

Figure 2.26: Preferences

If you choose, Commit, the action upon exiting SQL Interactive will be to perform a commit.  If you choose, Always Ask, a dialog box will always ask you if you want to commit, rollback or cancel upon exiting SQL Interactive.

If you selected Text Output from the Grid Output/Text Output drop-down list as in Figure 2.27, the transactional behavior is very different.  First of all, the Autocommit checkbox and the rollback and commit icons are deactivated.

Figure 2.27: Text Output

Secondly, the successfully executed SQL statements are individually auto-committed regardless of the occurrence of errors in other SQL statements.  In actuality, the statements are submitted to the EnterpriseDB PSQL program as a batch which by default, is configured to auto-commit all transactions.  In a later section, I will explain how to turn off auto-commit.

One more line further down in Figure 2.23 we have the Limit checkbox and a numeric entry box (2).  If you want to force your query to return a maximum number of rows, make sure the checkbox is checked and a valid value is in the numeric entry box.

Below that is the query area (3).  You can enter any DML or DDL in this box.  You will run it by clicking on the running man icon.

You can have multiple queries in the query area at one time.  If you press the run icon with nothing selected, all queries will execute one after the other and display the results in the message window.

You can select a single statement to execute by highlighting it before pressing the run icon.

If you have Grid Output selected when you run one or more statements in the query area, it loads the results of each query into its own individual tab formatted in a data grid (5).  You can right-click on a tab (4) to get a menu (where you can close the tab if you choose to).

If you have Text Output selected, it displays all the results in a single, continuous message area that is more like the output of a SQL*Plus session.

This should be enough to get you into Developer Studio and playing with its features.  I will cover Developer Studio, the SQL Interactive facility, the SQL Terminal, Visual Explain, Backup and Restore in Chapter 5, Developer Studio.  I will also cover the debugger in detail in Chapter 5 but I will give you an introduction below.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.