Oracle PL/SQL Programming

Oracle PL/SQL Programming

by Steven Feuerstein, Bill Pribyl

Paperback(Fifth Edition)

View All Available Formats & Editions

Product Details

ISBN-13: 9780596514464
Publisher: O'Reilly Media, Incorporated
Publication date: 10/28/2009
Series: Animal Guide Series
Edition description: Fifth Edition
Pages: 1186
Product dimensions: 7.00(w) x 9.20(h) x 2.40(d)

About the Author

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language. He is also the author of the sequel to Oracle PL/SQLProgramming, Advanced Oracle PL/SQL Programming with Packages (O'Reilly & Associates, 1996), and the upcoming Oracle Built-in Packages (O'Reilly & Associates, 1998).

Steven has been developing software since 1980 and worked for OracleCorporation from 1987 to 1992. He is a partner with RevealNet, Inc.( and is the chief architect of the RevealNet products,PL/SQL Knowledge Base and PL/Vision (a code library of prebuilt PL/SQL packagesthat accelerates PL/SQL-based application development). Steven also sysopsRevealNet's "PL/SQL Pipeline," an online community for PL/SQL developers( He can be reached through email Steven is also president of PL/Solutions, whichoffers training and consulting on both PL/Vision and PL/SQL( Finally (on the Oracle side of Steven's life), he servesas codirector of the Oracle Practice at SSC, a systems management consultingfirm based in Chicago (

Steven shares his Rogers Park, Chicago, Georgian with his wife Veva, hisyoungest son Eli, two cats (Sister Itsacat and Moshe Jacobawitz), and Mercury(the Congo Red African Gray parrot). His older son, Chris, is busy making musicand creating art nearby. Steven is a member of the Board of Directors of theCrossroads Fund, which provides grants to organizations in Chicago working forsocial change.

Bill Pribyl, founder and principal of DataCraft, Inc. (, has been learning andteaching about Oracle databases and applications for morethan ten years. His work with object-oriented technology dates to themid-1980s, when he coauthored a paper on the application of entity-relationshipmodeling to object-oriented analysis.

A self-avowed Oracle "generalist," Bill has served as an Oracle databaseadministrator for several billion-dollar companies; led the development ofweb-based database applications for online commerce; configured an ultra-highavailability database using Oracle replication; helped NASA apply databasetechnology to space shuttle simulation software; and developed and taughtclasses in PL/SQL, Developer/2000, and the Oracle database. An ardent supporterof the Oracle user community, Bill is past chair of the South Central (USA)Oracle Users Group. He also served as editor-in-chief of Select, the quarterly publication of the International Oracle Users Group-Americas. His work has been published in Oracle user group publications worldwide. He also authored JavaScript programming examples for The Official Netscape LiveWire Pro Book.

Bill lives near his alma mater, Rice University, in Houston, Texas, with hiswife Norma, son Johnny, stepson Geoffrey, four cats, and a deaf Dalmatian. Hevolunteer teaches a class on the Internet and beginning HTML to children inpublic middle school.

Read an Excerpt

From Chapter: Variables and Program Data

...The LONG datatype

A variable declared LONG can store variable-length strings of up to 32760 bytes--this is actually seven fewer bytes than allowed in VARCHAR2 type variables! The LONG datatype for PL/SQL variables is quite different from the LONG datatype for columns in the Oracle Server. The LONG datatype in Oracle7 can store character strings of up to two gigabytes or 231-1 bytes; this large size makes the LONG column a possible repository of mulitmedia information, such as graphics images.

As a result of these maximum length differences, you can always insert a PL/SQL LONG variable value into a LONG database column, but you cannot select a LONG database value larger than 32760 bytes into a PL/SQL LONG variable.

In the Oracle database, there are many restrictions on how the LONG column can be used in a SQL statement; for example:

  • A tale may not contain more than one single LONG column.

  • Yoiu may not use the LONG column in a GROUP BY, ORDER BY, WHERE, or CONNECT BY clause.

  • You may not apply character functions (such as SUBSTR, INSTR, or LENGTH), to the LONG column.

PL/SQL LONG variables are free of these restrictions. In your PL/SQL code you can use a variable declared LONG just as you would a variable declared VARCHAR2. You can apply character functions to the variable. You can use it in the WHERE clause of a SELECT or UPDATE statement. This all makes sense given that, at least from the standpoint of the maximum size of the variables, there is really little difference between VARCHAR2 and LONG in PL/SQL.

Given the fact that aVARCHAR2 variable actually has a higher maximum length than the LONG and has no restrictions attached to it, I recommend that you always use the VARCHAR2 datatype in PL/SQL programs. LONGs have a place in the RDBMS, but that role is not duplicated in PL/SQL. This makes some sense since you will very rarely want to manipulate truly enormous strings within your program using such functions as SUBSTR or LENGTH or INSTR.

The RAW datatype

The RAW datatype is used to store binary data or other kinds of raw data, such as a digitized picture or image. A RAW variable has the same maximum length as VARCHAR2 (32767 bytes), which must also be specified when the variable is declared. The difference between RAW and VARCHAR2 is that PL/SQL will not try to interpret raw data. Within the Oracle RDBMS this means that Oracle will not perform character set conversions on RAW data when it is moved from one system (based, for example, on 7-bit ASCII) to another system.

Once again, there is an inconsistency between the PL/SQL maximum length for a RAW variable (32767) and the RDBMS maximum length (255). As a result, you cannot insert more than 255 bytes of your PL/SQL RAW variable's value into a database column. You can, on the other hand, insert the full value of a PL/SQL RAW variable into a column with type LONG RAW, which is a two-gigabyte container for raw data in the database.

The LONG RAW datatype

The LONG RAW datatype stores raw data of up to 32760 bytes and is just like the LONG datatype excerpt that the data in a LONG RAW variable is not interpreted by PL/SQL.

Given the fact that a RAW variable actually has a higher maximum length than the LONG RAW and has no restrictions attached to it, I recommended that you always use the RAW datatype in PL/SQL programs. LONG RAWs have a place in the RDBMS, but that role is not duplicated in PL/SQL.

The ROWID datatype

In the Oracle RDBMS, ROWID is a pseudocolumn that is part of every table you create. the rowid is an internally generated and maintained binary value which identifies a row of data in your table. It is called a pseudocolumn because a SQL statement includes it in places where you would normally use a column. However, it is not a column that you create for the table. Instead, the RDBMS generates the rowid for each row as it is inserted into the database. The information in the rowid provides the exact physical location of the row in the database. You cannot change the value of a rowid.

You can use the ROWID datatype to store rowids from the database in your PL/SQL program. You can SELECT or FETCH the rowid for a row into a ROWID variable. To manipulate rowids in Oracle8, you will want to use the built-in package, DBMS_ROWID (see Appendix C, Built-In Packages). In Oracle7, you will use the ROWIDTOCHAR function to convert the rowid to a fixed-length string and then perform operations against that string.

In Oracle7, for format of the fixed-length rowid is as follows:


Components of this format have the following meanings:


The block in the database file


The row in the clock (where the first row is zero, not one)


The database file

All these numbers are hexadecimal; the database file is a number which you would then use to look up the actual name of the database file through the data dictionary.

In Oracle8, rowid have been "extended" to support partitioned tables and indexes. The new, extended rowids include a data object number, identifying the database segment. Any schema object found in the same segment, such as a cluster of tables, will have the same object number. In Oracle8, then, a rowid contains the following information:

  • The data object number

  • The data file (where the first file is 1)

  • The data block within the data file

  • The row in the data block (where the first row is 0)

Oracle8 provides functions in the DBMS_ROWID package to convert between the new formats of rowids...

Table of Contents



Programming in PL/SQL

Chapter 1: Introduction to PL/SQL

Chapter 2: Creating and Running PL/SQL Code

Chapter 3: Language Fundamentals

PL/SQL Program Structure

Chapter 4: Conditional and Sequential Control

Chapter 5: Iterative Processing with Loops

Chapter 6: Exception Handlers

PL/SQL Program Data

Chapter 7: Working with Program Data

Chapter 8: Strings

Chapter 9: Numbers

Chapter 10: Dates and Timestamps

Chapter 11: Records

Chapter 12: Collections

Chapter 13: Miscellaneous Datatypes


Chapter 14: DML and Transaction Management

Chapter 15: Data Retrieval

Chapter 16: Dynamic SQL and Dynamic PL/SQL

PL/SQL Application Construction

Chapter 17: Procedures, Functions, and Parameters

Chapter 18: Packages

Chapter 19: Triggers

Chapter 20: Managing PL/SQL Code

Chapter 21: Optimizing PL/SQL Performance

Chapter 22: I/O and PL/SQL

Advanced PL/SQL Topics

Chapter 23: Application Security and PL/SQL

Chapter 24: PL/SQL Architecture

Chapter 25: Globalization and Localization in PL/SQL

Chapter 26: Object-Oriented Aspects of PL/SQL

Chapter 27: Calling Java from PL/SQL

Chapter 28: External Procedures

Regular Expression Metacharacters and Function Parameters

Number Format Models

Date Format Models


Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews

Oracle PL/SQL Programming 4.8 out of 5 based on 0 ratings. 10 reviews.
rumtum More than 1 year ago
This is an essential book for PL programming.
Anonymous More than 1 year ago
*strolls in and sits on the couch*
Anonymous More than 1 year ago
Fine move to dg res one.
Anonymous More than 1 year ago
Crushes? Fasion? Makeup? Friends? Ask about all these and more at fasion result 2!!!!!!!
Anonymous More than 1 year ago
My ex is a total jerk!! He's always talking about how gross I am and always making wierd faces when I look at him. Once he even said it was the biggest mistake of his life to date me!!!! My heart shattered!!! What do I do?
Anonymous More than 1 year ago
Girl problem at res 6
Anonymous More than 1 year ago
This is not realated to boys or anything like that but i didnt know where else to put this so here it goes. I dont have a religon. Now, you must be thinking," Yes you do. Everyone does!" Well, I dont. I dont go to church, I dont say prayers, or anything of that sort. And im not Jewish( no offenese to those who are). My best friend is always talking about her church and how I should go there with her every Sunday. She tries to teach me about god and jesus and things like that but it doesnt make sense to me at all. When i say," Can we do something else?"she says,"No you need to learn this." I dont want to learn it. What should i tell her, and how do i say it without offending her?
Anonymous More than 1 year ago
Don't date someone you don't like just because you think your friend will get mad at you. Talk to her and say, "l think your brother is a sweet and awesome person. I just don't have a romantic attraction to him. That could change, but not anytime soon." If she is a good friend she will understand.<p> About Mikey: it's obvious from your description that Mikey possibly might have a mental or emotional disorder, or very light autism. I think you should keep your distance from him and stop thinking about him. If he kicked a girl there is a BIG problem. Just forget about him. That sounds harsh, but it's true. Remember the good times you had together, acknowledge them, and move on. You'll be happier for it.
Anonymous More than 1 year ago
No problem. :D If you need anything ir just want to talk, find me through FlareStrike.
Anonymous More than 1 year ago