O'Reilly logo

Oracle SQL*Plus: The Definitive Guide by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Working with PL/SQL

PL/SQL is a programming language developed by Oracle as an extension to SQL in order to allow procedural logic to be implemented at the database level. PL/SQL is used to write stored procedures, stored functions, and triggers, and, beginning with Oracle8, to define object types. It can also be used to simply write a block of procedural code for the database to execute. SQL*Plus was originally one of the only front-ends that could be used to send PL/SQL code to the database, and even today it is still one of the most widely used.

This section explains the mechanics of entering and executing PL/SQL code with SQL*Plus. You’ll learn what PL/SQL mode is, and you’ll learn the differences between entering a PL/SQL block and a SQL query.

If you are unfamiliar with PL/SQL, you may want to pick up a copy of Steven Feuerstein and Bill Pribyl’s book, Oracle PL/SQL Programming, second edition (O’Reilly & Associates, 1997). PL/SQL opens up a world of possibilities. You’ll want to take advantage of it if you are doing serious work with Oracle.

What Is a PL/SQL Block?

The PL/SQL block is the fundamental unit of PL/SQL programming. The term block refers to a program unit that contains some or all of the following elements:

  • Variable and subprogram declarations

  • Procedural code, which may include nested PL/SQL blocks

  • An error handler

Here is an example of a reasonably simple, but complete, PL/SQL block:

DECLARE X VARCHAR2(12) := 'Hello World!'; BEGIN DBMS_OUTPUT.PUT_LINE(X); EXCEPTION ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required