Oracle PL/SQL Built-ins Pocket Reference

By Steven Feuerstein, John Beresniewicz and Chip Dawes

The Oracle PL/SQL Built-ins Pocket Reference is a quick reference guide to the many built-in packages and functions provided by Oracle Corporation. It contains a concise description of the syntax for the following:

• Built-in packages
• Built-in functions
• RESTRICT REFERENCES pragmas for the built-in packages
• Nonprogram elements (e.g., constants, exceptions, etc.) defined in the built-in packages

Although we don't include every single package and function in this pocket reference, we've included all the built-ins that most PL/SQL developers will ever need to use.

The purpose of this pocket reference is to help PL/SQL users find the syntax of specific built-in headers. It is not a self-contained user guide; basic knowledge of PL/SQL and its built-ins is required. For more information, see the following books:

• Oracle PL/SQL Programming, by Steven Feuerstein and Bill Pribyl (O'Reilly & Associates, Second Edition, 1997).
• Oracle Built-in Packages, by Steven Feuerstein, Charles Dye, and John Beresniewicz (O'Reilly & Associates, 1998).

Click to Read More

Oracle PL/SQL Language Pocket Reference

By Steven Feuerstein, Bill Pribyl and Chip Dawes

The Oracle PL/SQL Language Pocket Reference is a quick reference guide to the PL/SQL programming language, which provides procedural extensions to the SQL relational database language and a range of Oracle development tools.

Where a package, program, or function is supported only for a particular version of Oracle (e.g., Oracle8i), we indicate this in the text.

The purpose of this pocket reference is to help PL/SQL users find the syntax of specific language elements. It is not a self-contained user guide; basic knowledge of the PL/SQL programming language is required.

For more information, see the following books:

• Oracle PL/SQL Programming, 2nd Edition, by Steven Feuerstein with Bill Pribyl (O'Reilly & Associates, 1997).
• Oracle Built-in Packages, by Steven Feuerstein, Charles Dye, and John Beresniewicz (O'Reilly & Associates, 1998).
• Oracle PL/SQL Built-ins Pocket Reference, by Steven Feuerstein, John Beresniewicz, and Chip Dawes (O'Reilly & Associates, 1998).

Click to Read More

Oracle Web Applications PL/SQL Developer's Introduction

By Andrew Odewahn

The goal of Oracle Web Applications is to help Oracle SQL and PL/SQL developers who have little or no web programming experience to learn to develop useful web applications, using technologies most IS developers can grasp fairly quickly: WebDB, Oracle Application Server (OAS), PL/SQL, HTML, and XML.

In addition, the book introduces several other Oracle8i technologies -- Advanced Queuing (AQ), the Internet File System (iFS), interMedia, InternetLite, and Java(TM) -- and shows how they form a cohesive development framework that addresses the pressing issues of web content management, application development, and application integration. While there have been many changes in Oracle8i, it's still just a database, and there's no need to panic: data is data, whether it comes from the accounts payable system or from the Internet.

This book acknowledges that you're a busy person. Since most of us simply don't have time to read and digest an 800-page book on each individual technology, I've tried to present fundamental elements of the topics you'll use most often in your daily development efforts. This book will get you started and solidly on your way, but it's not, obviously, the ultimate reference. Rather, it is a "Cliff Notes" of Oracle web development -- enough to help you pass the test, but not enough to help you appreciate the finer points. Once you've read the book, however, you'll be ready to delve into the various areas (WebDB, PL/SQL, Java, etc.) more deeply. Your first step on that journey should be to consult the appendix for information on further resources.

Click to Read More

Advanced Oracle PL/SQL Programming with Packages

By Steven Feuerstein

Who would have thought that just one year after the publication of Oracle PL/SQL Programming, a 916-page tome on "everything PL/SQL", I'd end up writing a second book about the PL/SQL language? Although back in September 1995 I wasn't arrogant enough to think that I knew all there was to know about PL/SQL, I also underestimated how much more I had still to learn!

I am firmly of the belief that one never stops learning -- as long as one is open to learning. The area of PL/SQL in which I needed lots more education turned out to be packages. In my first book I explained how to build and use packages. I even provided lots of examples of package construction. But I started to realize that this wasn't enough. Over the past year, I have been designing and developing a set of packages to help me build PL/SQL-based applications. This was a thoroughly selfish effort: I wanted to be as productive as possible, and I wanted to overcome a number of weaknesses -- however transient -- in the PL/SQL language. In the process of writing this software, I learned a good deal about the best ways to build PL/SQL code, especially regarding packages. I also discovered some very interesting techniques that can make packaged software more maintainable, accessible, and easy to use.

As my thinking on the construction of packages crystallized, I began to view all of my packages as a library of code that could be used by any PL/SQL developer. I also realized that I wanted to share the new techniques and lessons I had uncovered. The result? This book and the PL/Vision product.

How often do you find yourself writing a program and simultaneously thinking: somebody must have done this before! You feel certain that you are reinventing the wheel. Worse, if you are sufficiently honest with yourself, you will also admit that someone else has probably spent more time on the problem and has already come up with a better solution than you are likely to develop for your specific application.

Often, you know you should take the time to "genericize" a program so that you can use it again and again in different circumstances. Somehow, however, you never find the time -- and the mental space -- to take your code to that higher level of abstraction.

So you limp along, accepting a relatively low level of productivity and reusing a truly minimal amount of code. You write the same things over and over and simply push aside the feeling that you are wasting your time.

Oracle developers are fortunate to be able to use an advanced, robust language like PL/SQL. PL/SQL developers are, on the other hand, less than fortunate (at least as of September 1996) to find that the supporting environment for PL/SQL is still very immature. Where are the debuggers, the code formatters and generators, the toolboxes of reusable programs and objects? When will we have a powerful editor that knows about PL/SQL syntax and -- more importantly -- the stored code available for execution?

When, you might also ask, will this guy stop complaining? It is acceptable to identify weaknesses. It is constructive to analyze areas for improvement. At some point, however, you have to stop whining and start improving things for yourself. Best yet, keep on whining but engage in self-improvement at the same time!

This book will help you write better packages. It will also show you how to use the "prebuilt" packages of the PL/Vision software product -- my attempt to change the "situation on the ground" for PL/SQL programmers. Finally, I hope that it will, via examination of my source code and the way I separated functional areas in PL/Vision, offer a blueprint for PL/SQL developers to discover how to take full advantage of PL/SQL packages in their day-to-day programming.

Click to Read More

Oracle Built-in Packages

By Steven Feuerstein, Charles Dye and John Beresniewicz

After publishing more than 1,600 pages on Oracle PL /SQL in two previous books, I marvel at the existence now of this third book covering yet other aspects of the PL /SQL language. I can still remember quite distinctly a moment in September, 1994, when I embarked on writing the first draft of Oracle PL /SQL Programming and wondered: are there really 400 pages worth of material on that much-used and often-maligned procedural language from a nonprocedural (SQL) company? If the answer to that question was a resounding "yes" in 1994, then the answer is a deafening roar today!

Maybe PL /SQL isn't the answer to every object-oriented programmer's deepest desires. Maybe developers are badly in need of -- and unreservedly deserve -- better tools with which to write, debug, and reuse PL /SQL programs. Maybe PL /SQL isn't perfect, but the reality is that hundreds of thousands of people around the world work (and struggle) with PL /SQL on a daily basis. We all need as much information as possible about how we can make the best possible use of Oracle PL /SQL.

And that is the objective of Oracle Built-in Packages. If you are going to build complex applications using PL /SQL, you will not succeed unless you learn about and figure out how to utilize many of the packages described in this book. Packages are the method of choice for Oracle and third parties like RevealNet, Inc., to extend the base PL /SQL language, to improve ease of use, and to provide brand-new functionality in the language. Writing PL /SQL code without knowing about or using built-in packages is akin to building an automobile and ignoring the last 20 years of technological advances. The resulting machine will run more slowly, use more gas, and be harder to repair.

Oracle Built-in Packages grew out of Chapter 15 of the first edition of Oracle PL /SQL Programming. When Oracle released Oracle8, it was time to update that book to include the wide-ranging new PL /SQL8 functionality. It was clear from the start that this second edition, if organized like the first, would have been well over 1,500 pages in length -- a totally impractical size for a developer's handbook.

What to do? Based on feedback from developers about Oracle PL /SQL Programming, there was an enormous amount of interest in, and often confusion surrounding, the built-in packages. These Oracle-provided "add-ons" to PL /SQL clearly needed more detailed coverage, more examples, more tips, more of just about everything. My single chapter of 100 pages was woefully inadequate. We made the decision to move that single chapter out of Oracle PL /SQL Programming and expand it into a book all its own. You are holding the result.

I recognized early in the process that I couldn't personally cover all of the Oracle built-in packages discussed in this book. I didn't have the necessary expertise, nor the time to learn, nor the time to write it all. So I sought and received the help of two excellent Oracle technologists: John Beresniewicz and Charles Dye.

Over the past six months, John, Charles, and I have researched the packages provided by Oracle in the database, verified the documentation, uncovered aberrant behavior, and discovered neat tricks. We also made it a priority to construct package-based utilities that you will be able to put to immediate use.

While Oracle Built-in Packages is a collaborative effort, it is also a combination of very individual efforts. As such, you will find differences in coding styles and philosophies. Rather than try to enforce a single standard throughout, I welcomed the variations (as long as all contributed in their own way to a deeper, clearer understanding of the PL /SQL technology). There is rarely a single right way to do anything, and there is an enormous amount we can learn from the different journeys each of us takes to a solution.

Click to Read More

Oracle PL/SQL Programming - Guide to Oracle8i Features

By Steven Feuerstein

2700 pages and still writing! Sometimes I feel like the Energizer Bunny of PL/SQL. But Oracle keeps the features coming, and after all these years, I'm still enthusiastic about what PL/SQL can do to improve the quality of life for developers. Even with the coming of Java(TM) in Oracle8i, I believe strongly that the future is bright for PL/SQL developers.

This short book is something of a departure for me -- those of you who have read my larger tomes may wonder if I've found a ghostwriter! Now that Oracle8i is here, it's my intention to update Oracle PL/SQL Programming (now in its second edition) to cover the new version of the Oracle database. Along with developing a third edition of that book (with my coauthor Bill Pribyl), I'm taking a critical look at all of my books to make sure that the O'Reilly & Associates PL/SQL series offers a comprehensive resource for PL/SQL developers.

For now, though, PL/SQL developers need current and useful information about the latest PL/SQL features; there are a lot of them, and some represent major changes in the language. This small book is intended to get you started on understanding these features and using them to best advantage.

For many people, the big news about Oracle8i is Java, and the big question for many PL/SQL developers is how (and whether) to use Java in conjunction with PL/SQL. Chapter 9, Calling Java from PL/SQL, is a roadmap showing PL/SQL developers how to employ Java right now. It doesn't attempt to teach you the basics of Java -- there are many other books that serve that purpose -- but it does teach you how to access Java from within PL/SQL.

Click to Read More

Oracle PL/SQL Programming Second Edition

By Steven Feuerstein and Bill Pribyl

What, specifically, will this book help you do?
Take full advantage of PL/SQL. The reference manuals may describe all the features of the PL/SQL language, but they don't tell you how to apply the technology. In fact, in some cases, you'll be lucky to even understand how to use a given feature after you've made your way through the railroad diagrams. Books and training courses tend to cover the same standard topics in the same limited way. In this book, we'll venture beyond to the edges of the language, to the nonstandard ways in which a particular feature can be tweaked to achieve a desired result.

Use PL/SQL to solve your problems. You don't spend your days and nights writing PL/SQL modules so that you can rise to a higher plane of existence. You use PL/SQL to solve problems for your company or your customers. In this book, I try hard to help you tackle real-world problems, the kinds of issues developers face on a daily basis (at least those problems that can be solved with mere software). To do this, I've packed the book with examples -- not just small code fragments, but complete application components you can apply immediately to your own situations. There is a good deal of code in the book itself, and much more on the disk that accompanies the book. In this book I guide you through the analytical process used to come up with a solution. In this way I hope you'll see, in the most concrete terms, how to apply PL/SQL features and undocumented applications of those features to a particular situation.

Write efficient, maintainable code. PL/SQL and the rest of the Oracle products offer the potential for incredible development productivity. If you aren't careful, however, this rapid development capability will simply let you dig yourself into a deeper, darker hole than you've ever found yourself in before. I would consider this book a failure if it only ended up helping programmers write more code in less time than ever before. I want to help you develop the skills and techniques that give you the time to build modules which readily adapt to change and are easily understood and maintained. I want to teach you to use comprehensive strategies and code architectures which allow you to apply PL/SQL in powerful, general ways to many of the problems you will face.

Click to Read More

Oracle Data Warehousing Unleashed

By Bonnie O'neill et al

You have decided to embark on a data warehouse journey. You are probably a little scared and overwhelmed by the immensity of the project in front of you. You are probably also wondering where to start.

This book is designed to be a definitive reference guide for all project staff having anything to do with the data warehouse. Here is an overview of the types of job classes that would benefit from this book:

Project managers: The book provides a chapter on project management, and one of the appendixes has a task checklist to assist you. The methodology and architecture chapters help you in understanding what you are going to build and some best-practice guidelines in how to do it. In addition, the rest of the book, while geared for a more technical audience, can give you a feel for what to expect and what kinds of obstacles you will likely encounter throughout the expedition.

Data administrators: A few chapters are designed especially for you: "Data Integration: The Challenges," "Defining Your Data," "Metadata," and the database design chapters. You might also find the architecture and methodology chapters interesting.
Quality analysts: the data integration, metadata, and data scrubbing chapters are right up your alley.

DBAs: Many chapters are of interest to DBAs, including (but not limited to) storage concerns, physical database design, exploiting parallel technology, indexes, kernel performance tuning, and security.

Application architects and developers: There are lots of chapters that help you get a feel for how to exploit the warehouse so the end user can get the maximum benefit possible. Some of these chapters are "Using the Intranet," "Front-End Tools," "Tuning Queries," and "Data Mining."

Data warehouse architects: The book contains a wealth of architecture and methodology information, and also discusses related constructs tangential to the data warehouse, including the operational data store and data marts.

Click to Read More