×

Uh-oh, it looks like your Internet Explorer is out of date.

For a better shopping experience, please upgrade now.

Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2
     

Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2

by Steven Feuerstein, Bill Pribyl
 

See All Formats & Editions

The first edition of Oracle PL/SQL Programming quickly became an indispensable reference for both novice and experienced PL/SQL developers. Packed with examples and recommendations, it helped everyone using PL/SQL (from the Oracle Forms developer to the database administrator) make the most of this powerful language.Oracle8 presents PL/SQL programmers with

Overview

The first edition of Oracle PL/SQL Programming quickly became an indispensable reference for both novice and experienced PL/SQL developers. Packed with examples and recommendations, it helped everyone using PL/SQL (from the Oracle Forms developer to the database administrator) make the most of this powerful language.Oracle8 presents PL/SQL programmers with new challenges by increasing both the possibilities and complexities of the language. This new edition updates the original book for Oracle8, adding chapters describing the new PL/SQL object features (object types, collections, object views, and external procedures). The second edition also contains a much-requested chapter on tuning PL/SQL, as well as expanded discussions of debugging and tracing PL/SQL execution.The book comes with a companion diskette containing the Companion Utilities Guide for Oracle PL/SQL Programming, an online tool developed by RevealNet, Inc. The online guide contains more than 100 file of source code and documentation prepared by the authors.Even if you've already read the first edition of Oracle PL/SQL Programming, you'll find an enormous amount of new and revised information in this second edition and on its companion diskette. If you're new to PL/SQL, you'll soon find yourself on the road to mastery.NOTE: To make room for the new material, some summaries and code from the first edition have been moved to the companion disk.Oracle PL/SQL Programming is divided into seven parts:Foreword PrefacePart I: Programming in PL/SQL

  • 1. Introduction to PL/SQL
  • 2. PL/SQL Language Fundamentals
  • 3. Effective Coding Style
Part II: PL/SQL Language Elements
  • 4. Variables and Program Data
  • 5. Conditional and Sequential Control
  • 6. Database Interaction and Cursors
  • 7. Loops
  • 8. Exception Handlers
  • 9. Records in PL/SQL
  • 10.PL/SQL Tables
  • Part III: Built-in Functions and Packages
    • 11. Character Functions
    • 12. Date Functions
    • 13. Numeric, LOB, and Miscellaneous Functions
    • 14. Conversion Functions
    Part IV: Modular Code
    • 15. Procedures and Functions
    • 16. Packages
    • 17. Calling Stored PL/SQL Functions from SQL
    Part V: PL/SQL Oracle8 Features
    • 18. Object Types
    • 19. Nested Tables and VARRAYs
    • 20. Object Views
    • 21. External Procedures
    Part VI: Making PL/SQL Programs Work
    • 22. Code Design Tips
    • 23. Managing Code in the Database
    • 24. Debugging PL/SQL
    • 25. Tracing PL/SQL
    • 26. Tuning PL/SQL
    Part VII: Appendixes
    • A. What's on the Companion Disk?
    • B. Calling Stored Procedures from PL/SQL Version 1.1
    • C. Built-in Packages

    Editorial Reviews


    Fatbrain Review

    Devoted entirely to the PL/SQL (Procedural Language extension to SQL), this second edition of Oracle PL/SQL Programming updates the first edition for Oracle8, and includes chapters on new PL/SQL object features (object types, collections, object views, and external procedures). The first three chapters of the book explain what it means to program in PL/SQL, and then walk you through the main features of the language, programming habits, and effective coding style. It then moves on to basic PL/SQL programming components such as variables, cursors, conditional and sequential control statements, loops, exception handlers, PL/SQL records, and PL/SQL tables. Part three of the book covers built-in functions such as character, date, numeric, LOB, and conversion functions that can be put to use immediately in your applications. Moving forward, you will learn how to build procedures, functions, and packages correctly. Chapters 18 through 21 give an in-depth coverage of the new features of Oracle8. Learn about object types, nested tables and VARRAYs, object views, and external procedures. Armed with a summary of helpful tips for effective PL/SQL programming in the real world, part six of the book shows you how to manage your PL/SQL code and debug, tune, and trace the execution of your programs. The text contains numerous examples to guide you as to how, when, and where to apply programming constructs most effectively. Appendices A through C contain a summary of contents on the companion disk, how to call stored procedures from PL/SQL Version 1.1, and how to call Oracle's built-in packages.
    Booknews
    New edition which adds chapters describing PL/SQL in terms of object features and tuning, and includes expanded discussions of debugging and tracing execution. Twenty-six chapters discuss topics including programming, language elements, built-in functions, modular code, and new PL/SQL8 features. The included disk contains the Oracle PL/SQL programming utilities guide which offers approximately 100 files of source code and documentation. Annotation c. by Book News, Inc., Portland, Or.

    Product Details

    ISBN-13:
    9781565923355
    Publisher:
    O'Reilly Media, Incorporated
    Publication date:
    09/01/1997
    Edition description:
    Second Edition
    Pages:
    1024
    Product dimensions:
    7.01(w) x 9.19(h) x 1.99(d)

    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:

    BBBBBBB.RRRR.FFFFF

    Components of this format have the following meanings:

    BBBBBBB

    The block in the database file

    RRRR

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

    FFFFF

    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...

    Meet the Author

    Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language. He is the author or coauthor of Oracle PL/SQL Programming, Oracle PL/SQL Best Practices, Oracle PL/SQL Programming: Guide to Oracle8i Features, Oracle PL/SQL Developer's Workbook, Oracle Built-in Packages, Advanced Oracle PL/SQL Programming with Packages, and several pocket reference books (all from O'Reilly & Associates). Steven is a Senior Technology Advisor with Quest Software, has been developing software since 1980, and worked for Oracle Corporation from 1987 to 1992.

    Bill Pribyl, author, teacher, and software consultant,is the primary author of Learning Oracle PL/SQL and the coauthor of Oracle PL/SQL Programming and its companion pocket reference, all published by O'Reilly & Associates. An Oracle user since 1986, Bill has consulted on many aspects of using Oracle products. He recently spearheaded PLNet.org, a Web-based repository where developers can share open source PL/SQL.

    Customer Reviews

    Average Review:

    Post to your social network

         

    Most Helpful Customer Reviews

    See all customer reviews