Describe 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
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.
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:
The table below shows a quick comparison of the pros and cons of each tool:
Browse tool for retrieving and saving files
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
Yes, using the SET command
Yes, using the COLUMN command
Retrieve prior commands
Only most recent command
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.