Loading your own custom environment automatically on startup

To customize your SQL*Plus environment and have it assign your preferences from one session to the next, you will want to edit one or both of its auto-startup scripts. The way SQL*Plus behaves on startup is:

1. It searches for the file sqlplus/admin/glogin.sql in the Oracle home directory and, if found, executes any commands it contains. This "global" login script applies to everyone who executes SQL*Plus from that Oracle home, no matter which directory they start in.

2. Next, it searches for and runs the file login.sql in the current directory.

The startup script can contain the same kinds of statements as any other SQL*Plus script: SET commands, SQL statements, column formatting commands, and the like.

Neither file is required to be present. If both files are present, both get executed; in the case of conflicting preferences or variables, the later setting prevails.

Here are a few of my favorite login.sql settings:

REM Number of lines of SELECT statement output before re-printing headersSET PAGESIZE 999 REM Width of displayed page, expressed in charactersSET LINESIZE 132 REM Enable display of DBMS_OUTPUT messagesSET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED REM Change editor SQL*Plus invokes with "ed" commandDEFINE _EDITOR = vi REM Format misc columns commonly retrieved from data dictionaryCOLUMN segment_name FORMAT A30 WORD_WRAPCOLUMN object_name FORMAT A30 WORD_WRAP

In iSQL*Plus, there is no notion of the current directory, so there is no way to have a personal login.sql file. Only the glogin.sql on the server running iSQL*Plus has any effect.

2.1.6 Error Handling in SQL*Plus

The way SQL*Plus communicates success depends on the class of command you are running. With most SQL*Plus-specific commands, you can calibrate success by the absence of an error message. Successful SQL and PL/SQL commands, on the other hand, usually result in some kind of positive textual feedback.

If SQL*Plus encounters an error in a SQL or PL/SQL statement, it will, by default, report the error and continue processing. This behavior is desirable when you're working interactively. But, when you're executing a script, there are many cases in which you want an error to cause SQL*Plus to terminate. Use the following command to make that happen:

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE

Thereafter in this session, SQL*Plus will terminate if the database server returns any error messages in response to a SQL or PL/SQL statement. The SQL.SQLCODE part means that, when SQL*Plus terminates, it will set its "return code" to a nonzero value, which you can detect in the calling environment.^[5] Otherwise SQL*Plus always ends with a 0 return code, which may falsely imply that the script succeeded.

^[5] Using, for example, $? in the Unix shell, or %ERRORLEVEL% in Microsoft Windows.

A more common form of this command would be:

SQL> WHENEVER SQLERROR SQL.SQLCODE EXIT ROLLBACK

which means that you also want SQL*Plus to roll back any uncommitted changes prior to exiting.

2.1.7 Why You Will Love and Hate SQL*Plus

In addition to the features you just read about, here are some particular features of SQL*Plus that you will come to know and love:

· With SQL*Plus, you can run "batch" programs, supplying application-specific arguments on the sqlplus command line, and referring to them in the script using &1 (first argument), &2 (second argument), etc.

· SQL*Plus provides complete and up-to-date support for all SQL and PL/SQL statements. This can be important when you're using features unique to Oracle. Third-party environments may not provide 100% coverage; for example, some still don't understand Oracle's object types, which were introduced several years ago. (See Chapter 21 for a discussion of using object types in PL/SQL.)

· SQL*Plus runs on all of the same hardware and operating system platforms on which the Oracle server runs.

But as with any tool, there are going to be some irritations:

· In console versions of SQL*Plus, the statement buffer is limited to the most recently used statement; SQL*Plus offers no further command history.

· When you switch users by issuing a CONNECT command in the middle of a session or a script, SQL*Plus does not rerun the login.sql (or glogin.sql) startup script, causing SERVEROUTPUT to get disabled. This also causes no end of trouble for folks who create fancy prompts in their login.sql file—for example, a prompt such as "TEST9i>" will remain even after doing a CONNECT to the PROD9i database. You have to remember to run the startup after every reconnect.

· With SQL*Plus, there are no modern command-interpreter features such as automatic completion of keywords or hints about which database objects are available while typing in a statement.

· Online help consists of minimal documentation of the SQL*Plus command set. (Use HELP command to get help on a specific command.)

· There is no ability to change the current directory once you've started SQL*Plus. This can be annoying when opening or saving scripts if you don't like typing full pathnames. If you discover that you're in an inconvenient directory, you have to quit SQL*Plus, change directories, and restart SQL*Plus.

· SQL*Plus looks only in the startup directory for login.sql; it would be better if it always searched in my home directory, so I wouldn't need a copy of login.sql in every directory where I work.

The bottom line is that SQL*Plus is something of a "real programmer's" tool that is neither warm nor fuzzy. But it is ubiquitous, doesn't crash, and is likely to be supported as long as there is an Oracle Corporation.

2.2 Performing Essential PL/SQL Tasks

Let's turn to the highlights of creating, running, deleting, and otherwise managing PL/SQL programs, using SQL*Plus as the front end. Don't expect to be overwhelmed with detail here; treat this section as a glimpse of topics that will be covered in much greater detail in the chapters ahead.