Take a Creative, Even Radical Approach

We all tend to fall into ruts, in almost every aspect of our lives. People are creatures of habit: you learn to write code in one way; you assume certain limitations about a product; you turn aside possible solutions without serious examination because you just know it can't be done. Developers become downright prejudiced about their own programs, and often not in positive ways. They are often overheard saying things like:

"It can't run any faster than that; it's a pig."

"I can't make it work the way the user wants; that'll have to wait for the next version."

"If I were using X or Y or Z product, it would be a breeze. But with this stuff, everything is a struggle."

But the reality is that your program could almost always run a little faster. And the screen could in fact function just the way the user wants it to. And although each product has its limitations, strengths, and weaknesses, you should never have to wait for the next version. Isn't it so much more satisfying to be able to tell your therapist that you tackled the problem head-on, accepted no excuses, and crafted a solution?

How do you do this? Break out of the confines of your hardened views and take a fresh look at the world (or maybe just your cubicle). Reassess the programming habits you've developed. Be creative—step away from the traditional methods, from the often limited and mechanical approaches constantly reinforced in our places of business.

Try something new: experiment with what may seem to be a radical departure from the norm. You will be surprised at how much you will learn and grow as a programmer and problem-solver. Over the years, I have surprised myself over and over with what is really achievable when I stopped saying "You can't do that!" and instead simply nodded quietly and murmured "Now, if I do it this way . . . "

Take a Creative, Even Radical Approach - student2.ru Chapter 2. Creating and Running PL/SQL Code

Before exploring the "meat and potatoes" of writing PL/SQL programs, you might benefit from some practical information about how to compile and run those programs. This chapter surveys a range of tools, in particular SQL*Plus, from which you can invoke your PL/SQL programs, and provides the specifics you'll need to get started.

If you already have some experience with database programming, you probably realize that there are lots of different ways and places to use SQL (Structured Query Language). Well, the same is true for PL/SQL; not only can you invoke it from a variety of other languages, it can execute in two different runtime environments:

· "Inside" the Oracle database server, as stored code. If you call PL/SQL from SQL*Plus, Java, or any other language, this is typically where it's going to run.

· In one of Oracle's application development environments such as Oracle Forms or Oracle Reports, as a program that executes on a client computer. (Alternately, these tools can run on a "middle tier," operated across the network from a browser-only or "thin" client.)

Let's begin by looking at the first option, running on the Oracle server. Here you have additional options for the front end from which you launch your code. Some of the most popular programming tools include:

· Oracle's command-line tool, SQL*Plus, which connects to an Oracle server where you can run PL/SQL statements.

· A host language such as C, C++, Java, Visual Basic, COBOL, Ada, or FORTRAN, for which Oracle provides a runtime library and/or precompiler that allows you to embed SQL and PL/SQL calls into your program.

Because the number of execution tools at your disposal will probably far exceed your time to assimilate them, it makes sense to concentrate on one or two, and learn them really well. So, while this chapter shows some examples of using PL/SQL with a variety of tools, the main concentration is on SQL*Plus.

Take a Creative, Even Radical Approach - student2.ru 2.1 SQL*Plus

The granddaddy of Oracle front ends, Oracle's SQL*Plus provides a command-line interpreter for both SQL and PL/SQL. That is, it accepts database statements from the user, then sends them off to the Oracle server, and finally displays the results.

Often maligned for its primitive user interface, SQL*Plus is one of my favorite Oracle tools. I actually like the lack of fancy gizmos or complicated menus. When I started using Oracle (circa 1986), this product's predecessor was boldly named UFI—User Friendly Interface. Almost two decades later, even Oracle9i's version of SQL*Plus is still unlikely to win any user friendliness awards, but at least it doesn't crash very often.

Today there are several different styles ofexecuting SQL*Plus:

As a console program

This is a program that runs from a shell or command prompt^[1] (an environment that is sometimes called a console).

^[1] Oracle calls this the "command-line interface" version of SQL*Plus, but I find that somewhat confusing, because two of the four styles provide a command-line interface.

As a pseudo-GUI program

This form of SQL*Plus is available only on Microsoft Windows. I call it a "pseudo-GUI" because it looks pretty much like the console program but with bitmapped fonts; few other features distinguish it from the console program. In fact, Oracle is already phasing out support for this product, with even "extended" support terminating in September 2005.

Via iSQL*Plus (in Oracle9i or later)

This program executes from a web browser connected to a middle-tier machine running Oracle's HTTP server and iSQL*Plus server.

Via SQL*Plus Worksheet

This is merely a Java GUI front end on the console version of SQL*Plus. Although it does maintain some statement history, there is little else to commend this version.

Figure 2-1 is a screen shot of a SQL*Plus console-style session.

Figure 2-1. SQL*Plus in a console session

Take a Creative, Even Radical Approach - student2.ru

Usually, I prefer the console program because:

· It tends to draw the screen faster, which can be significant for long queries

· It has more complete command-line history (on Microsoft Windows platforms, at least)

· It has a much easier way of changing visual characteristics such as font, color, and scroll buffer size

· I'm just an old command-line guy anyway

That said, I am also rather fond of the way iSQL*Plus automatically formats query output into HTML tables. It's really useful when you're trying to display a lot of columns from the database; you'll see an example in the later section, Section 2.1.2.

2.1.1 Starting Up SQL*Plus

To start the console version of SQL*Plus, you can simply type "sqlplus" at the operating system prompt (designated by "OS>").

OS> sqlplus

This works for both Unix-based and Microsoft operating systems. SQL*Plus should display a startup banner and then prompt you for a username and password.

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jun 20 10:41:17 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Enter user-name: bobEnter password: swordfish Connected to:Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.1.0 - Production SQL>

Seeing the "SQL>" prompt is your cue that your installation is set up properly. (The password, "swordfish" in this case, won't echo on the screen.)

You can also launch SQL*Plus with the username and password on the command line:

OS> sqlplus bob/swordfish

I do not recommend this, because some operating systems provide a way for other users to see your command-line arguments, which would allow them to break into your account.

You can use the /NOLOG option to start up SQL*Plus without connecting to the database, and supply the username and password via the CONNECT command.

If the computer where you're running SQL*Plus also has a properly configured Oracle Net^[2] installation, and you have been authorized by the database administrator to connect to remote databases (that is, database servers running on other computers), you will be able to connect to these other databases from SQL*Plus.

^[2] Oracle Net is the current name for the product previously known as Net8 or SQL*Net.

This requires knowing something called an Oracle Net connect identifier (also known as a service name) that you must supply along with your username and password. A connect identifier could look like this:

test01.ariel.datacraft.com

To use this identifier, you can append it to your username and password, separated by an at-sign (@) symbol:

SQL> CONNECT bob/[email protected]> Connected.

When starting the pseudo-GUI version of SQL*Plus, supplying your credentials is straightforward, although it calls the connect identifier a "Host String" (see Figure 2-2). If you want to connect to a database server running on the local machine, just leave the "Host String" field blank.

Figure 2-2. The GUI login screen of SQL*Plus

Take a Creative, Even Radical Approach - student2.ru

To run iSQL*Plus, put the correct URL into your browser (probably http://hostname/isqlplus) and supply your connection information as in Figure 2-3.

Figure 2-3. The iSQL*Plus login page

Take a Creative, Even Radical Approach - student2.ru