Oracle Desk Reference

Read an Excerpt

PREFACE:

Preface

Oracle Desktop Reference Template

How do I use substr to get trailing characters from a string? How do I create my own exceptions in PL/SQL? How do I create a read-only transaction? How do I add a new log file to my database? What's the syntax for publishing a Java-stored program in PL/SQL?

Oracle administrators and developers face questions like these every day. With the ever-expanding scope of the Oracle product line, finding the answers involves an increasingly difficult struggle through the massive and often inconvenient Oracle on-line documentation set. The aim of this book is to improve your productivity by placing essential Oracle information at your fingertips.

When I started administering and developing applications with Oracle databases in the 1980s, every developer and DBA on my team had a set of manuals, which could comfortably fit on a desk. Now — in the last year of the twentieth century — the full documentation set requires several shelves in a large bookcase. Luckily, the dilemma of finding space for printed documentation almost never arises because printed manuals have virtually disappeared from the workplace. Oracle Corporation literally gives away electronic documentation, while charging extravagant prices for the printed versions. The result has been the almost total elimination of printed Oracle documentation in the average office.

I'm a real fan of on-line documentation, but the process of locating snippets of information in the Oracle on-line documentation set just takes too long. Suppose you had the Oracle documentation set on CD-ROM and you wanted to find the exactsyntax for the select statement. Here's what you would do:

• If you didn't have the Oracle on-line documentation installed on your hard disk, you would have to put the CD in your CD drive and wait a few seconds for your system to recognize it.
• Next, you must fire up your Web browser and load the Oracle documentation set home page.
• Then you have to locate the correct document, in this case, the Oracle SQL reference.
• When the document is found, you must open the Index page for the document and locate the select entry.
• And finally, you have to navigate to the page containing the Select documentation, which is large and takes a few moments to load.

I found that this involved process was consuming too much of my time. What I needed was a quick reference to all the frequently needed information — SQL commands, PL/SQL syntax, built-in packages, API calls, configuration parameters, and command line arguments — in a package small enough to fit on my desk. Because no such reference existed, I decided to write one, and this book is the result.

How to Use this Book, and How Not to Use It!

This book was designed to be kept close at hand and used to determine the syntax or usage of some aspect of Oracle for which you are already broadly familiar. For instance, you may recall that the mod function calculates a remainder, but you may have forgotten the order of the arguments. You may be creating a table, but need to refresh your memory on storage parameters. Or, you may be a DBA who needs to check the options of an init.ora parameter.

However, I don't recommend that you use this book as your only reference to the Oracle database. There is only limited introductory and tutorial material in this book. If you need to learn about some Oracle facility from scratch, or if you are looking for in-depth coverage of a topic, you should consult the Oracle documentation set, other third-party books, or Internet resources. For instance, if you are new to PL/SQL, you should not use this book to teach yourself the PL/SQL language. However, once you have learned PL/SQL — and even while you are learning it — you will find the PL/SQL chapters of the book a very useful reference.

Structure

This book is arranged into a couple of major sections:

The first four chapters cover the SQL language, which is the basis for all data manipulation and retrieval in Oracle. These chapters cover:

• The select statement, which is the basis for all data retrieval.
• SQL functions, expressions, hints, and operators.
• The Data Manipulation Language (DML) statements: update, delete, and insert, and statements related to transaction control and locking.
• Data Definition Language (DDL) statements that create, modify, and delete database objects such as tables, indexes, and views.

The next three chapters cover PL/SQL and Java. pl/sql implements Oracle's proprietary procedural extensions to the SQL language. It also implements many of Oracle's advanced features such as advanced queuing and large object support. In Oracle8i, a Java Virtual Machine (JVM) has been introduced and developers can now choose to develop stored procedures and triggers in Java. These three chapters cover:

• The PL/SQL language syntax and usage.
• Oracle-supplied PL/SQL packages, which extend the PL/SQL language, or which provide new Oracle functionality.
• Oracle Java, including JDBC, SQLJ, and the Oracle8i JServer environment.

The final chapters cover utilities, administration options, terms and jargon, and a brief list of Internet resources. These chapters are:

• Command line utilities provided with the Oracle server.
• The SQL*PLUS program.
• Oracle server configuration parameters.
• Jargons, terms, and acronyms.

Operating System and Oracle Version-Specific Information

This reference attempts to cover syntax for Oracle versions 7.3, 8.0, and 8.1 (also referred to as Oracle8i). It also covers command line utilities available in the UNIX and NT operating systems. To minimize confusion, the following symbols may appear in the margin to indicate that a feature is specific to an operating system or Oracle version.

Feedback

While every effort has been made to ensure that this book is accurate and timely, there is no doubt that some errors will be found or that information in this book will be overtaken by subsequent releases of Oracle. If you find anything in need of correction, please let me know at gharriso@mira.net. IÊwill ensure that the necessary corrections are made in future releases or in errata. You might also wish to visit my home page at ...

Read More