Oracle PL/SQL Self Study Questions
Oracle Tips by Burleson Consulting
1. What are some of the advantages of using PL/SQL over traditional
Answer: In traditional languages such as COBOL, Fortran, or C++, the
SQL statements must be embedded inside the program and the program
can be run through special pre-compiler to detect the SQL statements
and replace them with native calls to the database management
system. PL/SQL is superior to native programming languages in the
respect that didn’t the database access is somewhat transparent.
PL/SQL also has the advantage of being able to be called
independently from client side applications.
2. What is the concept of Row-oriented processing, and why is it
important for relational database access?
Answer: SQL is not a row-oriented language. The result set from an
SQL statement could be a single row or 10,000 rows. Hence, native
SQL is not appropriate type of language when we need to interrogate
individual rows within a set. The relational model offers us the
ability to declare and use cursors in order to extract or results
set, and interrogate data, one row at a time.
3. What is the basic difference between a stored procedure and a
function within Oracle?
Answer: Functions and stored procedures are identical in terms of
the type of code that can be placed within them, but functions have
a restriction that they must always return a value to the calling
4. What are the three types of looping constructs within PL/SQL?
Answer: Oracle’s PL/SQL provides three types of loop structures. We
have the standard for loop structure, which allows looping for
specific number of iterations, the repeat until looping construct,
and the while loop construct. These three types of looping
constructs are very similar to those in traditional procedural
languages such as Visual Basic and C++.
5. Why does Feuerstein make such a big deal about modularity in
chapter 15 of your readings?
Answer: the benefits of modular programming are very well known
within the IT community. Modularity allows code re-use, provides a
mechanism for the elimination of code redundancy, and also makes
procedural code easier to read and understand.
6. Why is it important to place stored procedures and functions
Answer: When stored procedures and functions are encapsulated into a
package, the package can be cached inside the Oracle SGA thereby
making it ineligible for paging and fully resident in the RAM. This
will make all of the procedures run far faster than stored
procedures and functions that are not pinned inside the Oracle SGA.
Note: These exercises may use the
pubsdb.sql script that can be downloaded at