Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Introduction to PL/SQL

Databases have been in use long before the personal computer arrived on the scene.  IBM developed the Structured Query Language standard (dubbed SQL, and pronounced “See-Quel”) over 30 years ago as a way to retrieve data from their new “relational” database.  A decade later, Oracle released the first commercial relational database that used SQL, and SQL has become the de-facto query language for the vast majority of popular database products. 

Even though SQL  is the standard language for interacting with any modern database, it does not mean that SQL is without limitations.  If we want to retrieve a set of records from the database and modify them according to a set of rules, (updating some and returning to the calling program others), we can’t do this with a single SQL call to the database.  Complex processing requires the ability to compare values (often called Boolean logic) and implement programmatic flow control.   In other words, some type of programming language was required to process the returned rows and implement the program rules.  To achieve this capability, Oracle introduced the Procedural Language extensions to the Structured Query Language or PL/SQL. 

Oracle PL/SQL  was based on the ADA programming language which was developed by the Department of Defense to be used on mission critical systems.  Although not a “sexy” language like Java or C, ADA is still being develop and used for applications such as aircraft control systems.  ADA is a highly structured, strongly typed programming language that uses natural language constructs to make it easy to understand.  The PL/SQL language inherited these attributes making PL/SQL easier to read and maintain than more cryptic languages such as C.  For example, below are two loops, one in PL/SQL and the other in a programming language called C.

for x in v_start..v_finish --PL/SQL

   v_int := v_int +1;

end loop;

As opposed to

for (x = str; x< fin; x++) {i++}  --C

As we see, the PL/SQL statement is more verbose but also easier to understand.

PL/SQL  is also portable within the Oracle database family and runs on all supported Oracle platforms including Oracle10g grid database. Even more important is platform independence, where programs developed in PL/SQL on a Windows Oracle database will load and run in a UNIX Oracle database.  With each release of the Oracle database, Oracle Corporation  enhances the capabilities and performance of PL/SQL.  Remember, PL/SQL is an Oracle only product, and no other database management system will run PL/SQL.

Unlike other languages that execute externally, PL/SQL executes inside the database.  This means that you can take advantage of PL/SQL’s exceptional ability to manipulate data in the database without paying the network penalty of retrieving the data out of the database and them updating it back to the database.  Because PL/SQL runs inside the database it takes advantage of the capabilities and capacity of the database server.

Traditionally, PL/SQL has been a compiled/interpreted language similar to Java.   When PL/SQL code is loaded into the database it is compiled into an intermediate form, similar to the way Java is compiled into byte-code.  This intermediate code is portable across Oracle databases.  Later versions of Oracle (Oracle9i and 10g) will compile PL/SQL into native code for over 60 hardware platforms.  This natively compiled code runs more efficiently, but it loses the ability to move to other Oracle databases without recompiling.

Lastly, placing the code that interacts with the database in PL/SQL makes better use of the database resources.  PL/SQL packages are loaded as a package so as your program calls for data, the procedures and functions are already in cached memory.  Using PL/SQL will also result in your application using bind variables.  The only way not to use bind variables in PL/SQL is to implement dynamic SQL  (discussed in Chapter 5).  The Database Administrator (DBA) also benefits when developers place their SQL inside PL/SQL because they have access to the statements for tuning.  For example the DBA can tune the SQL (adding hints, reordering the WHERE clause ) without impacting the existing application.  Placing SQL inside PL/SQL also allows the code to be protected by the recovery capabilities of the Oracle database. 

PL/SQL  is the most common language for Oracle the world, and developers are realizing the benefits in both application performance and database performance by implementing the database interaction in PL/SQL.  There are even websites built entirely using PL/SQL.  For example, Oracle’s HTML-DB product is installed in the database and consists primarily of PL/SQL packages  and Javascripts. 

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.