Oracle Consulting and Training
For Better, Faster, Smarter,
Oracle PL/SQLTutorial Part 7 - Using Stored Procedures
This is the 7th in our series of introductory Oracle PL/SQL tutorials. We've already looked at data types,
language elements, pros and cons of using PL/SQL. cursors and cursor FOR LOOPs. (See here for
previous articles). This tutorial explores the use of PL/SQL stored procedures in Oracle.
Oracle PL/SQL Stored Procedures
Let's consider the advantages and disadvantages of using stored procedures (and functions and packages)
and which, by the way, apply to Java stored procedures as well as Oracle PL/SQL stored procedures.
Advantages of stored procedures in Oracle:
the procedures are stored in the Oracle database and
are, therefore, executed on the database server which
is likely to me more powerful than the clients which
in turn means that stored procedures should run
the code is stored in a compiled form which means
that it is syntactically valid and does not need to be
compiled at run-time, thereby saving resources;
each user of the stored procedure/function will use
exactly the same form of queries which means the
queries are reused thereby reducing the parsing
overhead and improving the scalability of
as the procedures/functions are stored in the Oracle
database there is no need to transfer the code from the
clients to the database server or to transfer intermediate results from the server to the clients. This
results in much less network traffic and again improves scalability;
when using Oracle PL/SQL packages, as soon as one object in the package is accessed, the whole
package is loaded into memory which makes subsequent access to objects in the package much faster
stored procedures/functions can be compiled into "native" machine code making them even faster
(available with Oracle 9i and above).
there is an overhead involved in embedding or calling PL/SQL procedures from SQL in Oracle due to
the context switching that the database has to perform which may have a significant effect on
more memory may be required on the Oracle database server when using Oracle PL/SQL packages as
the whole package is loaded into memory as soon as any object in the package is accessed
native compilation can take twice as long as normal compilation
In conclusion, stored procedures is one feature of Oracle that you should be using if you want to improve
the performance and scalability of your applications whether they’re written in .NET, Java or PL/SQL. The
disadvantages are easily outweighed by the advantages and the use of packages to combine related objects
(procedures, functions and variables) into one physical unit enhances these advantages.
For reviews of book on Oracle PL/SQL and other aspects of Oracle as well as links to other resources see
our Oracle resources page. You might also be interested in our beginner's SQL tutorial or our other Oracle
The next part of this tutorial continues exploring the use of PL/SQL stored procedures in Oracle.
PL/SQL tutorial Part 6
PL/SQL tutorial Part 8
Looking to sky-rocket productivity, save time and reduce costs?
Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor-
led training in Oracle and related technologies on or off site in cities across the UK as well as self-study
online training. See our scheduled Oracle training courses, or let us know your requirements.
Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information,
take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you
make your Oracle systems faster and more reliable and save you hours searching for information.
Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd, Bristol, England
Tel: 0845 003 1320
Need help with Oracle? Contact Us
This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry
trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd 2014. All rights reserved.