Minimize
Blogger List

Johannes Ahrends
Toad and Oracle

Ben Boise
Toad SC Discussions

Kevin Dalton
Benchmark Factory

Steven Feuerstein
PL/SQL Obsession

Devin Gallagher
Toad SC discussions

Stuart Hodgins
JProbe Discussions
  Henrik "Mauritz" Johnson
Toad Tips & Tricks on the "other" Toads
  Mark Kurtz
Toad SC discussions
  Michael Lumbard
Toad SC discussions
Daniel Norwood
Toad for Data Analysts,
Toad Extension for Visual Studio
Debbie Peabody
Toad for Data Analysts
Gary Piper
Toad Reports Manager
John Pocknell
Toad for Oracle, JProbe
Kuljit Sangha
Toad SC discussions
Bert Scalzo Indicates Oracle ACE status
Toad for Oracle, Data Modeling, Benchmarking
Jeff Smith
Toad product family
Richard To
SQL Optimization
Jim Wankowski
DB2 - LUW and z/OS
John Weathington
  Toad Data Modeler Opens in a new window
Data Modeling		  
  Real Automated Code Testing for Oracle
Quest Code Tester blog
 
Minimize
Blog Categories
 
Minimize
Blog Tags
toad for oracle (122)
oracle (62)
plsql (46)
sql optimization (37)
toad for data analysts (28)
code tester (19)
toad for ibm db2 (13)
automation (11)
batch optimizer (10)
virtualization (10)
schema browser (9)
toad for sql server (9)
data grid (8)
sql (8)
sql editor (8)
toad data modeler (8)
benchmark factory (7)
excel (7)
query builder (7)
report manager (7)
toad extension (7)
visual studio (7)
11g (6)
configuration (6)
freeware (6)
health check (6)
vmware (6)
connect (5)
dba module (5)
er diagrammer (5)
F4 (5)
linux (5)
refactoring (5)
spotlight (5)
unicode (5)
compare (4)
debugger (4)
export (4)
formatter (4)
make code (4)
rman (4)
strip code (4)
benchmark (3)
bfscript (3)
bulk collect (3)
code templates (3)
code xpert (3)
database browser (3)
db2 (3)
notebook (3)
oem (3)
RAC (3)
session browser (3)
speed (3)
sql optimizer (3)
toad for mysql (3)
tpc-c (3)
9.7 (2)
alert log (2)
app designer (2)
awr (2)
code insight (2)
code snippets (2)
collection (2)
compare and sync (2)
compliance (2)
data generator (2)
data warehouse (2)
database explorer (2)
database monitor (2)
explain (2)
forall (2)
ftp (2)
group execute (2)
handbook (2)
installation (2)
job scheduler (2)
multi-task (2)
nested table (2)
os command (2)
profiler (2)
recovery (2)
release history (2)
save as (2)
schema compare (2)
sql recall (2)
stats pack (2)
subversion (2)
team coding (2)
trace file browser (2)
while loop (2)
10g (1)
64 bit (1)
7zip (1)
action (1)
addm (1)
alter (1)
ansi join (1)
array (1)
ccleaner (1)
code coverage (1)
code road map (1)
CRON (1)
cursor for loop (1)
data browser (1)
data subset (1)
database probe (1)
dbms_flashback (1)
dbms_profiler (1)
ddl (1)
feuerstein (1)
filezilla (1)
flash drive (1)
flow control (1)
for loop (1)
group policy manager (1)
hints (1)
import (1)
index (1)
inheritance (1)
invoker rights (1)
ipad (1)
java (1)
latency (1)
log switch (1)
logical model (1)
ltrim (1)
master-detail browser (1)
monitor (1)
multi-select (1)
naming standards (1)
network (1)
object explorer (1)
OEBS (1)
package (1)
parser (1)
partitioning (1)
performance (1)
pragma (1)
project manager (1)
RAT (1)
revo (1)
REXEC (1)
schema report (1)
script manager (1)
search (1)
set operator (1)
sga (1)
slow (1)
sonarsource (1)
source control (1)
space projection (1)
sql monitor (1)
sql navigator (1)
sql script (1)
sql tracker (1)
sql*plus (1)
standards (1)
statistics (1)
stored procedure (1)
string parser (1)
sub-model (1)
sub-type (1)
synch (1)
synchback (1)
TELNET (1)
toad (1)
trace (1)
unit test (1)
unix (1)
usb (1)
utility (1)
v10 (1)
v9.5 (1)
version control (1)
waits (1)
workload replay (1)
workspace (1)
xml (1)
 
WELCOME, GUEST
 
 

Blogs
Toad and Database Commentaries

Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies.  Have some views of your own to share?  Post your comments!  Note:  Comments are restricted to registered Toad World users.

Do you have a topic that you'd like discussed?  We'd love to hear from you.  Send us your idea for a blog topic.

Must Know Features of PL/SQL

Jul 22

Written by: StevenFeuersteinTW
Wednesday, July 22, 2009 7:50 AM  RssIcon

I spend way too much of my life on airplanes zipping around to different places, doing presentations and trainings on PL/SQL. In the process, I meet many PL/SQL developers – and I continue to be surprised and disappointed at how many developers do not know about or at least do not seem to take advantage of very powerful and useful features of PL/SQL. So I have to decided to list below all those elements of PL/SQL that I consider to be "must know." In other words, if you are not familiar with the following features, and not utilizing them, then your PL/SQL programs likely leave lots to be desired from the standpoints of efficiency and maintainability.
 
In the table below, you will find on the left the PL/SQL keywords that identify the feature and on the right a brief description and link to the Oracle documentation on this feature. Of course, you can also download my trainings at PL/SQL Obsession and learn about these features – oh, and don't forget to buy my books. J
 
Collections
Associative arrays, nested tables, varrays: if these terms mean little to you, then you are missing out on some of the most important functionality in PL/SQL. The collection is PL/SQL's version of an array, and they can help you in so many different ways.
 
FORALL
Bulk processing of DML statements. This is the most important new feature in PL/SQL since Oracle8i was released. Anytime you execute an insert, update, delete or merge inside a loop of some sort (multi-row DML), you should convert this looping operation to a FORALL statement. You will see an amazing improvement in performance...and you need to use collections to leverage FORALL.
 
Don't forget to also check out these nuances of FORALL: SAVE EXCEPTIONS, INDICES OF, VALUES OF.
 
BULK COLLECT
Bulk retrieval of data. Second only to FORALL in importance, use BULK COLLECT with both implicit and explicit cursors to drastically speed up the performance of data retrieval. Don't forget to use the LIMIT clause to manage the amount of PGA memory consumed by your program. As with FORALL, you need to use collections to leverage BULK COLLECT.
 
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Answers the question "On what line number was the most recent exception raised?" Available in Oracle Database 10g Release 2 and above, this function should be called whenever you trap and log an error.
 
DBMS_UTILITY.FORMAT_CALL_STACK
Answers the question "How did I get here?" In other words, it shows you the execution call stack (A called B called C). Very useful information when logging errors or tracing application execution.
 
DBMS_UTILITY.FORMAT_ERROR_STACK
Returns the current Oracle error message (sometimes as a single string, and sometimes as a stack of messages, if the exception has been re-raised one or more times). Oracle recommends you use this instead of SQLERRM, which may truncate your error message.
 
PRAGMA AUTONOMOUS_TRANSACTION;
This pragma defines your procedure or function as an autonomous transaction, which means that any DML operations performed within this subprogram are saved or rolled back without affecting any other uncommitted changes in your session. Very handy when logging errors or tracing, also useful in a variety of other circumstances.
 
AUTHID CURRENT_USER
When you include this clause in the header of a program unit, then when the program is run, the privileges of the current user or invoker of the program are used to resolve references to table and view names. This is called invoker rights and is in contrast to definer rights, the default (or AUTHID DEFINER), in which the privileges of the owner of the program determine at compile time how references to database objects are resolved.
 
Invoker rights comes in very handy when you need the same code to work against different tables, depending on who is calling the program.
 
SUBTYPE
The SUBTYPE keyword allows you to define your own, application-specific datatypes. It is a trivial feature to learn and use, but a very powerful one from the standpoint of writing maintainable code.
 
Use packaged-based subtypes to hide the actual datatype definition, such as VARCHAR2(500). That way, if you ever need to change the datatype (for example, to make the maximum size of the string larger), you can change it one place and all the variables that are declared using the subtype will use this new definition after recompilation.
 
RESULT_CACHE
New to Oracle Database 11g, you can specify that a function is a result cache. This means that Oracle will cache the inputs and return values of the function in a new SGA cache, and share this data across all sessions in the instance. It can provide a significant boost in query performance for any tables that are queried more frequently than they are updated.
 
Sure, you are not using 11g yet, but you will. And you can get ready to take advantage of the result cache by hiding your queries inside functions now.
 
DBMS_ERRLOG
Introduced in Oracle Database 10g Release 2, this package and the associated LOG ERRORS clause, allows you to suppress the raising of exceptions in for DML statements, instead writing error information to a log table. This feature is very handy when performing large numbers of DML operations (ie, batch loads) from within PL/SQL and you need to continue past any exceptions raised. You can also use this within a FORALL statement.
 
 

Trackback Print
Tags: plsql , oracle , collection , forall , bulk collect , pragma
Categories: PL/SQL Obsession
Location: Blogs Parent Separator Steven Feuerstein's Blog

2 comment(s) so far...


Re: Must Know Features of PL/SQL

I would add pipelining, which reduces the PGA memory footprint that bulk collect and collections in general incur. Pipelining allows you to avoid instantiating a result set in memory inside a function, that's going to be returned to perhaps yet another calling function. When a function returns a collection, a full copy of the collection is made (albeit temporarily). For large collections, this can lead to poor performance. Pipelining allows you to return the collection's elements in bursts, and can be parallellized.

By dclamage on   Thursday, July 23, 2009 12:55 PM

Re: Must Know Features of PL/SQL

Thanks for the comment, Dan. It is good to hear from you. Pipelining is a very cool technique, and it is built on the more general table function capability. To demonstrate how much I agree with you that this is an important tuning technique, the fifth edition of my OPP text (coming out with the Oracle announcement of 11.2 this fall) features about twenty pages just on how to take full advantage of PTFs (written by Adrian Billington, who has tons more experience with this feature). Remember, though, that pipelined table functions can only used when the function is being called from within a SQL statement. Regards, SF

By StevenFeuersteinTW on   Thursday, July 23, 2009 1:01 PM
Minimize
Most Popular
 
Search Blog Entries
 
Blog Archives