PL/SQL Programming   «Prev  Next»

Lesson 2Introducing PL/SQL
ObjectiveDescribe some of the Common Tools used to develop PL/SQL.

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.

Common tools used to develop PL/SQL Programs

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

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:
SQL Worksheet consisting of 1) Enter SQL Statement 2) Results 3) Script Output 4) Explain 5) Autotrace
SQL Worksheet consisting of 1) Enter SQL Statement 2) Results 3) Script Output 4) Explain 5) Autotrace

Oracle PL/SQL Programming
The table below shows a quick comparison of the pros and cons of each tool:
FeatureSQL*PlusSQL Worksheet
Windows-like interfacePoorGood
Browse tool for retrieving and saving filesNoYes
EditorUse any editor of your choiceUse any editor of your choice
Editing in toolLine mode only, limited mouse as well as cut-and-paste capabilityRich cut-and-paste and mouse capabilities
Format numbersYes, using the SET commandNo
Format headingYes, using the COLUMN commandNo
Retrieve prior commandsOnly most recent commandYes
Behavior on exitCommits transaction by default on exitRolls 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.

SEMrush Software