PL/SQL development environments

Oracle provides and recommends the usage of its development tools for SQL writing and code execution. This chapter will cover the two main developer tools from Oracle:

• SQL Developer

• SQL*Plus

However, there are many SQL development interfaces available on the Web such as TOAD from Quest Software, Dreamcoder by Mentat Technologies, and so on.

SQL Developer

SQL Developer is a Graphical User Interface (GUI) tool from the Oracle Corporation. It is free to use and includes a wide spectrum of new features with each of its releases. It allows the users to perform database activities such as SQL writing, PL/SQL execution, DBA activities easily, interactively, and considerably within time. Many of the database utilities such as unit testing, profiling, extended search, and SQL monitoring have been implemented as GUI utilities and can be easily used with the PL/SQL programs. The latest version of SQL Developer is 3.1 (3.1.07.42) which has been released on February 7, 2012. SQL Developer can be downloaded from the Oracle Technology Network link:

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

The latest release of the database development tool offers new features such as inclusion of RMAN under DBA navigator, support for data pump technology, renovated data copy and difference features, support for migration to Teradata and Sybase, and generation of PDF reports.

The key accomplishments offered by SQL Developer are:

• Authenticating and connecting to multiple Oracle databases

• Allowing creation and maintenance of schema objects packages, procedures, functions, triggers, indexes and views

• Querying and manipulating the data

• Database utilities such as version maintenance, admin activities, migration, and database export

• Support for SQL*Plus commands

The major offerings shown in the preceding list are by virtue of the tool features. The salient features of the SQL Developer tool are:

• Connection browser and Schema browser

• SQL Worksheet and Query Builder

• Database import and export utility wizard

• Database user-defined reports

• Code repository configuration for version control

• Database copy and migration utility wizard

• Third-party databases

• Oracle APEX integration

• TimesTen integration

The following screenshot shows the Start Page of Oracle SQL Developer:

SQL Developer—the history

The following flowchart demonstrates the release history of SQL Developer:

Creating a connection

Once the SQL Developer tool is downloaded from the Oracle Technology Network (OTN) website (in ZIP format), it is ready for use and does not require any installation. The target server can be Oracle 11g database software. For educational and practice purposes, Oracle recommends the usage of the Oracle Database Express edition. It can be downloaded for free from the following URL:

http://www.oracle.com/technetwork/database/express-edition/overview/index.html

By default, the database software installation takes care of the Oracle database configuration and Oracle network configuration.

Now, we shall start working with SQL Developer to connect to the database. The first and foremost step is establishing the connection to the target database.

The steps for creating a connection in SQL Developer are as follows:

1. Double-click on \\sqldeveloper\sqldeveloper.exe.

2. Go to Menu | View | Connections. A tabbed page titled Connections will appear at the left-hand side of the page. The top node of the tree is Connections.

3. Right-click on the Connections node and select New Connection… to open the connection wizard.

4. Specify the connection name, username, password, connection type, role (DBA or default), host name, port number, and SID of the target database. Connection type must be Basic if you specify the connection parameters. If TNS, then select a connection string from the Network Alias drop-down list (which is in sync with the TNSNAMES.ORA file)

5. Check the Save Password option to allow the Connection wizard to remember the password of this user.

6. Click on the Test button to verify the connection. The status (success or error message) will appear in the wizard's console.

7. Click on the Connect button to connect to the database. By default, it opens a SQL Worksheet to write and execute queries.

SQL Worksheet

The SQL Worksheet window is the primary editor to perform database activities. It is used to write and execute SQL statements, PL/SQL code, and SQL*Plus commands.

A new worksheet can be opened in two ways:

• Hitting the shortcut key, Alt + F10

• Navigating to Tools | SQL Worksheet

When a SQL worksheet is opened by following either of the preceding options, a window pops up which prompts the user to select the database connection applicable for the current worksheet. The available database connection to open a new SQL worksheet can be selected from the drop-down option:

The worksheet contains multiple, quick utility actions as iconized menus. These menus perform a few of the basic activities associated with a script execution; for example, running a script, autotrace, and explain plan. With reference to the preceding screenshot of a sample SQL worksheet, the menu functions are described as follows:

• Run Statement: It executes the statement at the current cursor position.

• Run Script: It executes a script.

• Autotrace: It generates trace information about the statement.

• Explain Plan: It generates an execution plan for the query, starting at the current cursor position

• SQL Tuning Advisor: It advises the tuning tips for the current user. The user must have ADVISOR system privilege to use this icon.

• Commit: It commits the ongoing transaction in the current session.

• Rollback: It rollbacks the ongoing transaction in the current session.

• Unshared SQL Worksheet: It opens a new SQL worksheet.

• To Upper/Lower/InitCaps: It changes the string case of the statement to upper or lower or initial caps.

• Clear: It clears all the statements from the current SQL Worksheet.

• SQL History: It opens a dialog box with all the SQL statements executed for this user.

Executing a SQL statement

A SQL statement can be executed from the SQL Worksheet in three ways:

• Selecting the SQL statement and clicking on the Run Statement or Run Script icon from the Worksheet menu

• Selecting the SQL statement and pressing F9

• Terminating the SQL statement with a semicolon and pressing Ctrl + Enter

The result of the SQL statement execution is displayed in the Query Result tab. The following screenshot shows the execution of the SELECT statement using Ctrl + Enter:

Note

The SQL Worksheet doesn't supports some SQL*Plus commands such as append, archive, attribute, and break.

Calling a SQL script from SQL Developer

A SQL script saved on a specific OS location can be invoked from SQL Developer Worksheet. We will cover an overview of the two methods to execute a saved SQL script:

• A saved SQL script from an OS location can be invoked in The SQL Worksheet. It can be executed either by clicking on the Run Script (or F5) icon, or Ctrl + Enter or F9. The output of the script is displayed in the Script Output tab.

• Another option to invoke a saved script is to open it from the menu path, File | Open. Navigate to the script location and open the script. The script code would be opened in a new SQL Worksheet. Note that the worksheet's name is renamed as the actual script name. Now, the code can be executed using the Run Script icon.

A SQL script, Test_Script.sql at the C:\Labs\ location contains the following SQL statement:

SELECT * FROM EMPLOYEES
/

As shown in the following screenshot, the script has been invoked in the SQL Worksheet using SQL*Plus execute command, @:

Creating and executing an anonymous PL/SQL block

An anonymous PL/SQL block can be written and executed, as shown in the following screenshot. Note that the PL/SQL block must be terminated with a semicolon. The Script Output tab displays the confirmed status of the block execution as anonymous block completed.

The block output can be viewed in the Dbms Output Tab. This tab remains hidden until it can be enabled and activated by navigating to Menu | View | Dbms Output:

Debugging the PL/SQL code

The PL/SQL code can be debugged to observe the execution flow. The PL/SQL blocks and stored subprograms (procedures, functions, triggers, and packages) can be compiled for debugging.

An anonymous PL/SQL block can be debugged by selecting the block and choosing the Debug option from the right-click option list, as shown in the following screenshot:

Once the Debug option is clicked, the debugging starts and the following output appears in the Messages tab:

  Connecting to the database ORADEV.
  Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
  Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '127.0.0.1', '3953' )
  Debugger accepted connection from database on port 3953.
  Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
  Sum of two given Numbers:35
  Process exited.
  Disconnecting from the database ORADEV.
  Debugger disconnected from database.

Note

The database user must have DEBUG CREATE SESSION and DEBUG ANY PROCEDURE privileges to debug the PL/SQL code.

Likewise, the stored subprograms can be compiled using Compile for Debug to mark them for the debugging process. Henceforth, the execution of the subprograms can be traced line by line using the Oracle supplied package, DBMS_TRACE.

Editing and saving the scripts

SQL Developer provides enhanced editing features while writing the code in the SQL Worksheet. The automated code completion suggestion in the drop-down menu effectively eases the code writing. Besides, the PL/SQL syntax highlights, member method drop-down menu, code folding, and bookmarks are the other code editing features available in the SQL Worksheet.

A stored subprogram in the schema object tree can be opened in the SQL Worksheet for editing.

The following screenshot shows the auto-code completion feature of SQL Developer. You can select any of the available options as suited for the script:

The SQL statements or PL/SQL code in the current SQL Worksheet can be saved as a text or SQL file at any specified location on the OS. Either follow the full navigation path (File | Save) or use the quick utility Save icon. Once the Windows Save dialog box appears, navigate to the target location, specify the filename, and click on the Save button.

SQL*Plus

SQL*Plus is a command-line utility interface and has been one of the primitive interfaces used by database professionals for database activities. The SQL*Plus session is similar to the SQL Worksheet of SQL Developer, where you can write and execute SQL statements and PL/SQL code.

Starting from Oracle 5.0, the SQL*Plus interface has been a part of the Oracle development kit. With regular revisions and enhancements in subsequent Oracle releases, it has been deprecated in the Oracle 11g release to recommend the use of SQL Developer. However, the SQL*Plus environment can still be established from command prompt.

The evolution cycle of SQL*Plus is shown in the following diagram:

The Oracle SQL*Plus session can be invoked from sqlplus.exe. This executable file is located in the $Oracle_home\bin folder. Alternatively, it can also be invoked by performing the following steps:

1. Open command prompt.

2. Enter SQLPLUS, press Enter. Note the SQL*Plus welcome message. The editor will prompt for a username and password.

3. Enter the username, password, and database connection string. Press Enter.

4. Connect to the database:

  C:\>SQLPLUS

  SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 23 14:20:36 2011
  Copyright (c) 1982, 2010, Oracle.  All rights reserved.

  Enter user-name: ORADEV/ORADEV

  Connected to:
  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options

  SQL>

SQL*Plus has its own set of shell commands which can be used for the execution of scripts, editing the code, and formatting the query output.

The complete set of SQL*Plus commands can be obtained by entering HELP [INDEX |?] after the SQL prompt. The complete list of SQL*Plus commands are as follows:

  SQL> HELP INDEX

  Enter Help [topic] for help.

   @             COPY         PAUSE                    SHUTDOWN
   @@            DEFINE       PRINT                    SPOOL
   /             DEL          PROMPT                   SQLPLUS
   ACCEPT        DESCRIBE     QUIT                     START
   APPEND        DISCONNECT   RECOVER                  STARTUP
   ARCHIVE LOG   EDIT         REMARK                   STORE
   ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
   BREAK         EXIT         REPHEADER                TTITLE
   BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
   CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
   CLEAR         HOST         RUN                      WHENEVER OSERROR
   COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
   COMPUTE       LIST         SET                      XQUERY
   CONNECT       PASSWORD     SHOW

Executing a SQL statement in SQL*Plus

A SQL statement can be executed in the SQL*Plus editor, terminated by a semicolon or a forward slash (/). The following screenshot demonstrates the execution of a SELECT statement. The query selects the names of all employees from the EMPLOYEES table:

Executing an anonymous PL/SQL block

Similar to the execution in SQL Developer, a PL/SQL block can be executed in SQL*Plus. The SERVEROUTPUT environment variable has to be set to ON to display the results in the editor.

The following screenshot demonstrates the execution of a PL/SQL block in SQL*Plus: