Common Tools used to develop PL/SQL

The standard (PL) Procedural Language for Structured Query Language (SQL, pronounced sequel) is referred to as PL/SQL. The language bridges the gap between database technology and procedural programming language. Basically an application development tool, PL/SQL uses the facilities of the sophisticated Oracle (RDBMS) relational database management system and extends the standard SQL database language. PL/SQL is an extension to SQL. Thus, PL/SQL permits the use of all SQL data manipulation statements, including

1. INSERT,
2. UPDATE,
3. DELETE, and
4. SELECT,

as well as cursor operations and the transaction processing statements COMMIT, ROLLBACK, and SAVEPOINT. You have two choices for native tools in Oracle to develop your PL/SQL: SQL*Plus and SQL Worksheet.

Here are some common tools used to develop PL/SQL programs:

1. SQL Developer: A free, integrated development environment (IDE) provided by Oracle, which offers features like code completion, debugging, and version control.
2. PL/SQL Developer: A commercial IDE that provides advanced features like code optimization, debugging, and testing.
3. Toad: A commercial tool that offers a comprehensive set of features for PL/SQL development, including code completion, debugging, and database administration.
4. Oracle Enterprise Manager: A web-based tool that provides a graphical interface for managing and developing PL/SQL programs, as well as monitoring and administering Oracle databases.
5. SQLPlus*: A command-line tool that allows developers to write, execute, and debug PL/SQL programs, as well as perform database administration tasks.
6. JDeveloper: A free, integrated development environment (IDE) provided by Oracle, which supports PL/SQL development, as well as Java, XML, and web development.
7. Sublime Text: A popular, general-purpose text editor that can be used for PL/SQL development, with features like code completion and syntax highlighting available through plugins.
8. Visual Studio Code: A free, open-source code editor that supports PL/SQL development through extensions like Oracle Developer Tools.
9. DBMS_OUTPUT: A built-in tool that allows developers to print output and debug information to the console.
10. DBMS_DEBUG: A built-in tool that provides a debugging environment for PL/SQL programs.

These tools offer various features to support the development, testing, and debugging of PL/SQL programs, and can be used depending on the specific needs and preferences of the developer.

SQL*Plus has been part of Oracle's software system from the very beginning. SQL*Plus can be run in a windows mode on the client or server side or in line-command mode on the server side. This makes it a versatile tool for the database administrator. If you have taken the first course in this series, you are already familiar with SQL*Plus.

• Using the SQL Worksheet: SQL Worksheet is one of the tools in Oracle Enterprise Manager. You can use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements. You can specify any actions that can be processed by the database connection associated with the worksheet, such as creating a table, inserting data, creating and editing a trigger, selecting data from a table, and saving that data to a file. You can display a SQL Worksheet by right-clicking a connection in the Connections navigator and selecting Open SQL Worksheet, by selecting Tools and then SQL Worksheet, or by clicking the Use SQL Worksheet icon under the menu bar. In the Select Connection dialog box, select the database connection to use for your work with the worksheet. You can also use that dialog box to create and edit database connections. (You can have a SQL Worksheet window open automatically when you open a database connection by enabling the appropriate SQL Developer user preference under Database Connections.) To create a separate unshared worksheet for a connection, use Ctrl+Shift+N.

The SQL Worksheet has the user interface shown in the following figure:

Oracle PL/SQL Programming
The table below shows a quick comparison of the pros and cons of each tool:

Feature	SQL*Plus	SQL Worksheet
Windows-like interface	Poor	Good
Browse tool for retrieving and saving files	No	Yes
Editor	Use any editor of your choice	Use any editor of your choice
Editing in tool	Line mode only, limited mouse as well as cut-and-paste capability	Rich cut-and-paste and mouse capabilities
Format numbers	Yes, using the SET command	No
Format heading	Yes, using the COLUMN command	No
Retrieve prior commands	Only most recent command	Yes
Behavior on exit	Commits transaction by default on exit	Rolls back transaction by default on exit

If you like the standard Microsoft Windows features, you will want to use SQL Worksheet. However, SQL Worksheet may get a little frustrating because the tool has a lot of bugs. SQL*Plus is very robust and has been around for a long time. In the next lesson, the different types of PL/SQL variables will be identified.