facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: The Environment for PL/SQL Development

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

Oracle PL/SQL is designed to extend and integrate with Oracle SQL. While SQL itself is powerful and can accomplish many tasks, it is a set-oriented language designed to obtain and manipulate data in a relational database system (RDBMS). Even though Oracle SQL includes additional functionality unavailable in ANSI standard SQL, it lacks the features available in procedural languages. A procedural language provides the ability to assign values to variables, execute statements in sequence, iterate, and conditionally process commands. Hence, PL/SQL (or Procedural Language extensions to the Structured Query Language) was designed by Oracle to provide such features within the database.

A SQL user typically constructs a query to perform an operation on data in the database. Although several queries can be executed in sequence, the fundamental programming unit in SQL is a single statement. A PL/SQL user typically creates a program that includes variable declaration and assignment, conditional statements (e.g. IF/ELSE statements), and loops (e.g. FOR or WHILE statements). The program can contain many SQL statements, including statements that are dynamically generated. In addition, PL/SQL provides mechanisms to seamlessly integrate SQL and so effectively extends the capabilities present in SQL alone.

Many languages can be used with SQL embedded in them, but PL/SQL executes within the database itself. Although there are other languages that can execute within the Oracle database (Java for instance), they are simply general purposes languages that are available in this environment. PL/SQL was specifically designed for to interact closely with SQL within the Oracle database.

Lesson Goals

  • Distinguish between set-oriented and procedural languages
  • Learn about the environment and contexts in which PL/SQL operates
  • Learn about PL/SQL development tools
  • Learn about the advantages and benefits of PL/SQL within a database environment
  • Learn how to execute PL/SQL on the Oracle database
  • Learn how to return program output
  • Learn about the PL/SQL development cycle

Prerequisites

The current course assumes the following prerequisites:

  • The availability of an Oracle Client
  • A configured connection to an Oracle database with the HR Demo schema
  • Familiarity with Oracle SQL

SQL Developer and SQL*Plus are client programs provided by Oracle. Other 3rd party software (Quest Software's TOAD, All Around Solution's PL/SQL Developer) and open source programs can be used, but have specific options and might require some adjustments if used with this course. The free Oracle XE database can be used for this course.

File Naming Conventions

PL/SQL does not require any specific file naming conventions, so there are a number of different conventions in use. Some software (and programmers) differentiate between types of PL/SQL objects and use different extensions for each (pkg ,pkb, trg etc). For this course we will simply append .sql for all SQL and PL/SQL files.

Connecting to Oracle

As you know from working with Oracle SQL when you interact with an Oracle database, you do so with a given identity (Oracle user). This user can take certain actions within a subset of the database, some of which contains objects that the user owns (schema). The Oracle HR Schema (Human Resources data for a fictional company) will be used for this course.

SQL*Developer

To log into SQL Developer, double click on the menu option or SQL Developer icon.

Image of SQL Developer Icon

The splash screen will then display while the program loads.

Image of SQL Developer Icon

Configure your connection (if needed). To configure a new connection, right click on "Connections" in the leftmost tab, choose New Connection, enter the connection information, and test the connection and make any needed corrections.

Image of SQL Developer Icon

Choose your connection from the list on the left "Connections" tab of the screen and log in using the HR and relevant password.

SQL*Plus

To log into SQL*Plus, enter the following at an OS command prompt:

sqlplus <HR>/<password>@<database>

Substitute password with the correct HR password above. Note that queries need to be ended with a semi-colon or a forward-slash.

Executing PL/SQL Code

The following demonstrates how to execute an Oracle PL/SQL stored procedure.

Code Sample:

The-Environment-for-PLSQL-Development/Demos/hello_world.sql
exec dbms_output.put_line('Hello World - will not print out');
-- Now turn server output on
exec dbms_output.put_line('Hello World');

Code Explanation

A stored procedure is one type of PL/SQL programming unit. This example demonstrates one way that a stored procedure can be executed. DBMS_OUTPUT is an Oracle supplied PL/SQL package that contains a stored procedure named PUT_LINE. It puts data into a buffer on the server. However, by default both SQL*Plus and SQL Developer suppress this output. Whenever you are interested in seeing output displayed you would call this procedure from your program using dot notation: DBMS_OUTPUT.PUT_LINE('Message to display').

However, in order to actually see the message displayed in SQL*Plus you must first issue a SET SERVEROUTPUT ON command. (You'll see a demonstration in the next section for displaying output in SQL Developer.) The SET SERVEROUTPUT ON command causes the client application to issue a call to another stored procedure (DBMS_OUTPUT.GET_LINE) behind the scenes that retrieves the data from the buffer and displays it. In the next section you'll see that using SET SERVEROUTPUT ON is not necessary when SQL Developer is used.

SQL Developer Configuration

You can enter code into SQL Developer by typing it in, copying and pasting from an external source file or choosing File - Open from the menu. Make sure to set the option to display line numbers to ease the interpretation of error messages and subsequent debugging. You can right click in the line gutter and choose "Toggle Line Numbers" as shown below. If you want line numbers to persist between invocations of SQL Developer, then click on the "Tools" menu and select "Preferences", "Code Editor", "Line Gutter" and click the "Show Line Numbers" checkbox.Image of SQL Developer - toggle line numbers

If you are typing code in or copying and pasting, you can open a database connection by choosing to do so from the list of configured connections from the Connections tab on the left. However, if you open an existing file, you will need to choose a database connection to use. If a connection is not selected, the ability to execute scripts is disabled. This is because PL/SQL actually executes within the database itself. Image of SQL Developer - choose a database connection

As mentioned earlier, by default the "DBMS_OUTPUT" tab is not open in SQL Developer. To show the server output, select View and Dbms_Output. Image of SQL Developer - DBMS_Output

Once a database connection has been selected, the user interface will reflect the ability to run scripts. Image of SQL Developer - database connection chosen

If the DBMS_OUTPUT package is in use, code can be executed but no results will be displayed until serveroutput is set to on. Image of SQL Developer - no serveroutput

This can be done by running the command or by selecting an appropriate menu option. If you type in the command in SQL Developer then the server output will be displayed in the "Dbms Output" tab. In addition the output and any system messages will be displayed in the "Script Output" tab. You can see these two behaviors in the screen shot below.Image of SQL Developer - set serveroutput

If you want to separate the display of system messages from program output, then do not type the SET SERVEROUTPUT ON command. Instead, as indicated in the next screen shot, click the plus sign on the "Dbms Output" tab. Then select the appropriate connection from the "Connection" drop down list and click ok. This essentially performs the same process as the SET SERVEROUTPUT ON command but limits the display to just the "Dbms Output" tab. Notice the "Dbms Output" tab includes a number of additional controls relating to the display of server output from your program. Image of SQL Developer Set SERVEROUTPUT Button

The next screen shot illustrates the separation of the script output from the dbms output when you perform the steps in the above screen shot instead of executing the "set serveroutput on" statement in SQL Developer.Separation of Script Output

The PL/SQL Development Cycle

Duration: 10 to 15 minutes.
  1. Configure a connection (if necessary) to the database that contains the HR schema that will be used for the class.
  2. Make a connection using client software (SQL Developer and/or SQL*Plus) using the HR user.
  3. Create and execute a call to a stored procedure that will print out "Hello Universe!" using the execute command.

Solution:

The-Environment-for-PLSQL-Development/Solutions/development_cycle_solution.sql
exec dbms_output.put_line('Hello Universe!');

Code Explanation

Up to this point, you might have been typing in text (or copying/pasting) into SQL Developer. The following involves opening an existing file and figuring out how to associate a connection. This is an important concept for understanding the basic development process that is required for PL/SQL Development (as well as exercises for the remainder of this course).

Challenge

  1. Open a script that contains all of the commands from the previous exercise using The File - Open menu options. Run the script, change the text being displayed to standard out to "Hello Friend".

Challenge Solution:

The-Environment-for-PLSQL-Development/Solutions/challenge_development_cycle_solution.sql
-- Is the run menu disabled?  If so, make sure that you
-- assign the new worksheet to a connection using the
-- dropdown on the right hand side of the new
-- worksheet's tool bar.


exec dbms_output.put_line('Hello Friend!');