Howard Rogers

SQL*Plus and Basic Querying

Throughout this book you will find SQL*Plus is the predominant tool used for sample code, learning concepts, and putting Oracle through its paces. In this chapter, we are going to get you acquainted with using SQL*Plus to interface with your Oracle databases. You’ll learn the different ways to start it up. You’ll learn how to use it to query your database for application data or to get descriptions of your database. You’ll learn how to configure SQL*Plus to your needs.

Case Study 1 — Debugging PL/SQL

Throughout this book you have been introduced to a wide range of features of the Oracle database. Now we are going to use many of those features to build a debug utility (called DEBUG), which I hope you will find useful when developing your own PL/SQL applications. We will bring together tables, triggers, built-in packages, SQL, PL/SQL, security, and user-defined types to build a flexible and extremely useful tool.

The New 9i Sample Schemas

Throughout the first three chapters of this book, we have seen the use of database schemas such as SCOTT and HR. If you don’t already know, these database schemas are prepackaged by Oracle with the database. For many years now, Oracle instructors, administrators, programmers, and users have been using the trusty SCOTT schema for simple queries, updates, and deletes in order to learn, test, and/or tune their databases. These schemas are what we call sample schemas. Sample schemas are collections of database objects such as tables, views, and indexes, which come prepopulated with data representative of a small or medium-sized company.

Processing Statements in Oracle

In this chapter, we will explore what happens when Oracle executes the statements that you submit to it. We’ll investigate the complex actions that take place in order to execute both a simple statement such as SELECT * FROM EMP, and a more complex UPDATE statement. We’ll see what database processes (as described in Chapter 5) are involved, what memory structures are used and the general procedure Oracle goes through to execute each and every statement you submit.

Transactions and Concurrency

In this chapter we will explore two concepts that truly set a database management system apart from a file system. If you compare a file system to a database, you’ll discover many similarities between the two. We can create files in a file system, in the same way as we’ll create tables in a database. These files can be located in different directories, just as we have schemas or owners in the database. We can write data to these files, just as we insert rows into tables. However, two features that set the database apart from the file system in a dramatic fashion are transaction management and concurrency control.

Case Study 2 — Finding People

In this Case Study, we will review an application built to solve a problem we experienced at Oracle, involving employee contact information. While this may seem somewhat simple and rudimentary, there is more to it than mere code and SQL. You will find the application code itself is very easy to consume, reproduce and modify to fit your own data. What’s more, you can change the source data for this application and apply the principles you learn in this case study to any number of other problems you might encounter in your own day-to-day operations.

Procedures, Functions, and Packages

In the chapter on PL/SQL, you saw how powerful and useful this programming language could be. I’m sure any developers reading it also noticed that it was a little limiting though. All there seemed to be were anonymous (unnamed), self-contained blocks of code that can be run from the SQL*Plus prompt. Well that is not the case at all. Developers can name their PL/SQL blocks, parameterize them, store them in the database and reference or run them from any database client or facility such as SQL*Plus, Pro*C, or even JDBC.

Creating and Managing Users and Tables

In order to store and manage data in Oracle efficiently you need to understand the tools at your disposal. You are going to use the database to store your data. This data will be held in tables, and these tables are owned by user accounts. In other words, you need to learn both how to create (and manage) user accounts, and how to create tables to store your data.

A SQL Toolkit

If you haven’t realized it yet, you will soon come to recognize the value of having a repository of scripts to perform common tasks and execute useful reports in the database. Without a set of scripts at your disposal, you are constantly rewriting common SQL statements and wasting valuable time that could be spent doing other things, such as tuning your database or writing that application enhancement your users have been asking for.

Getting To Know Oracle

Before we start getting into the nitty-gritty of the chapters to come, there are some core concepts you should understand. In the next few sections, we will discuss concepts that aren’t to do with acquiring technical skills, but will be helpful in your pursuit of knowledge of and understanding about Oracle. In this chapter, I will explain: Fundamentals. What is a database? An instance? A schema? What kind of terminology will you encounter in Oracle that you most likely haven’t seen before? What are SYS and SYSTEM? I’ll explain some of the basic terminology you should be familiar with before you begin. This will arm you with the ‘lingo’ you’ll need to understand the information you’ll encounter throughout the book. The many flavors of the Oracle database. Oracle has one core database product, but it comes in a couple of different ‘classes’. We’ll discuss the intent of each class of database, and look at some of the factors that differentiate them. What you will find throughout this book and some things you should bear in mind as you move through it.