Section 2.1.  SQL*Plus

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 statements from the user, sends them off to the Oracle server, and 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 and complicated menus. Ironically, when I started using Oracle (circa 1986), this product's predecessor was boldly named UFIUser Friendly Interface. Two decades later, even the latest version of SQL*Plus is still unlikely to win any user friendliness awards, but at least it doesn't crash very often. Today, Oracle supports three different styles of executing SQL*Plus: As a console program This is a program that runs from a shell or command prompt[*] (an environment that is sometimes called a console). [*] Oracle calls this the "command-line interface" version of SQL*Plus, but I find that somewhat confusing, because two of the three 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. Beware: Oracle has been threatening to desupport this product for years, and it hasn't really been updated since Oracle8i Database. Via iSQL*Plus (in Oracle9i Database or later) This program executes from a web browser connected to a middle-tier machine running Oracle's HTTP server and iSQL*Plus server. Figure 2-1 is a screenshot of a SQL*Plus console-style session. Figure 2-1. SQL*Plus in a console session Usually, I prefer the console program because: It tends to draw the screen faster, which can be significant for queries with lots of output. It has a 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. It is available virtually everywhere that Oracle server or client tools are installed. That said, I am also rather fond of the way that 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, "Running a SQL Statement." 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 10.1.0.3.0 - Production on Thu May 19 17:15:43 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. Enter user-name: bob Enter password: swordfish Connected to: Personal Oracle Database 10g Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> Seeing the "SQL>" prompt is your cue that your installation is set up properly. (The password, "swordfish", 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 read your password. On multiuser systems, you can instead use the /NOLOG option to start SQL*Plus without connecting to the database, and then supply the username and password via the CONNECT command. OS> sqlplus /nolog SQL*Plus: Release 10.1.0.3.0 - Production on Thu May 19 17:16:45 2005 Copyright (c) 1982, 2004, Oracle. All rights reserved. SQL> CONNECT bob/swordfish SQL> Connected. If the computer you're running SQL*Plus on also has a properly configured Oracle Net[*] installation, and you have been authorized by the database administrator to connect to remote databases (that is, database servers running on other computers), you can connect to these other databases from SQL*Plus. Doing so requires knowing 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: [*] Oracle Net is the current name for the product previously known as Net8 and SQL*Net. hqhr.WORLD To use this identifier, you can append it to your username and password, separated by an at-sign (@) symbol: SQL> CONNECT bob/swordfish@hqhr.WORLD SQL> 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 To run iSQL*Plus, put the correct URL into your browser (probably http://hostname/isqlplus), and supply your connection information as shown in Figure 2-3. Figure 2-3. The iSQL*Plus login page Once you have SQL*Plus running, you can do all kinds of things; here are the most common: Run a SQL statement Compile and store a PL/SQL program in the database Run a PL/SQL program Issue a SQL*Plus-specific command Run a script that contains a mix of the above We'll take a look at these in the following sections. 2.1.2. Running a SQL Statement In the console version of SQL*Plus, the query: SELECT isbn, author, title FROM books; produces output similar to that shown in Figure 2-1.[*] [*] Well, I cheated a bit in that figure because I used some column formatting commands. If this were a book about SQL*Plus or how to display database data, I would expound on the many ways SQL*Plus lets you control the appearance of the output by setting various formatting and display preferences. You can take my word for it, though; there are more options than you can shake a stick at. If you want "pretty" output with less work, you may be better off with iSQL*Plus. Here, you enter the statement in the "Enter statements" field and press the "Execute" button. Output appears in a table in your browser, as in Figure 2-4. Figure 2-4. Query with result in iSQL*Plus You must terminate SQL statements in the console or pseudo-GUI with a semicolon, but the terminator is not required when entering a single statement in iSQL*Plus. 2.1.3. Running a PL/SQL Program So, here we go (drum roll please). Let's type a short PL/SQL program into SQL*Plus: SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('Hey look, ma!'); 3 END; 4 / PL/SQL procedure successfully completed. SQL> Oops. Although it has successfully completed, this particular program was supposed to invoke PL/SQL's built-in program that echoes back some text. SQL*Plus's somewhat annoying behavior is to suppress such output by default. To get it to display properly, you must use a SQL*Plus command to turn on SERVEROUTPUT: SQL> SET SERVEROUTPUT ON SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('Hey look, Ma!'); 3 END; 4 / Hey look, Ma! PL/SQL procedure successfully completed. SQL> I generally put the SERVEROUTPUT command in my startup file (see the later section, "Loading your own custom environment automatically on startup"), causing it to be enabled until one of the following occurs: You disconnect, log off, or otherwise end your session. You explicitly set SERVEROUTPUT to OFF. Oracle discards session state either at your request or because of a compilation error (see "Healing Invalids" in Chapter 20). In Oracle versions through Oracle9i Database Release 2, you issue a new CONNECT statement; Oracle Database 10g's SQL*Plus automatically reruns your startup file after each CONNECT. When you enter SQL or PL/SQL statements into the console or pseudo-GUI SQL*Plus, the program assigns a number to each line after the first. There are two benefits to the line numbers: first, it helps you designate which line to edit with the built-in line editor (which you might actually use one day); and second, if Oracle detects an error in your code, it will usually report the error accompanied by a line number. You'll have plenty of opportunities to see that behavior in action. To tell SQL*Plus that you're done entering a PL/SQL statement, you must usually include a trailing slash (see line 4 in the previous example). Although mostly harmless, the slash has several important characteristics: The meaning of the slash is "execute the most recently entered statement," regardless of whether the statement is SQL or PL/SQL. The slash is a command unique to SQL*Plus; it is not part of the PL/SQL language, nor is it part of SQL. It must appear on a line by itself; no other commands can be included on the line. In most versions of SQL*Plus prior to Oracle9i Database, if you accidentally precede the slash with any spaces, it doesn't work! Beginning with Oracle9i Database, SQL*Plus conveniently overlooks leading whitespace. Trailing space doesn't matter in any version. As a convenience feature, SQL*Plus offers PL/SQL users an EXECUTE command, which saves typing the BEGIN, END, and trailing slash. So the following is equivalent to the short program I ran earlier: SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Hey look, Ma!') A trailing semicolon is optional, but I prefer to omit it. As with most SQL*Plus commands, EXECUTE can be abbreviated and is case-insensitive, so most interactive use gets reduced to: SQL> exec dbms_output.put_line('Hey look, Ma!') 2.1.4. Running a Script Almost any statement that works interactively in SQL*Plus can be stored in a file for repeated execution. The easiest way to run such a script is to use the SQL*Plus "at-sign" (@) command.[*] For example, this runs all the commands in the file abc.pkg: [*] START, @, and @@ commands are available in the nonbrowser versions of SQL*Plus. In iSQL*Plus, you can use the "Browse" and "Load Script" buttons for a similar result. SQL> @abc.pkg The file must live in my current directory (or on SQLPATH somewhere). If you prefer words to at-signs, you can use the equivalent START command : SQL> START abc.pkg and you will get identical results. Either way, this command causes SQL*Plus to do the following: Open the file named abc.pkg. Sequentially attempt to execute all of the SQL, PL/SQL, and SQL*Plus statements in the file. When complete, close the file and return you to the SQL*Plus prompt (unless the file invokes the EXIT statement, which will cause SQL*Plus to quit). For example: SQL> @abc.pkg Package created. Package body created. SQL> The default behavior is to display only the output from the individual statements on the screen; if you want to see the original source from the file, use the SQL*Plus command SET ECHO ON. In my example, I've used a filename extension of pkg. If I leave off the extension, this is what happens: SQL> @abc SP2-0310: unable to open file "abc.sql" As you can see, the default file extension is sql. By the way, the "SP2-0310" is the Oracle-supplied error number, and "SP2" means that it is unique to SQL*Plus . (For more details about SQL*Plus error messages, refer to Oracle's SQL*Plus User's Guide and Reference.) 2.1.5. What Is the "Current Directory?" Any time you launch SQL*Plus from an operating system command prompt, SQL*Plus treats the operating system's then-current directory as its own current directory. In other words, if I were to start up using: C:\BOB\FILES> sqlplus then any file operations inside SQL*Plus (such as opening or running a script) will default to the directory C:\BOB\FILES. The same is true if you start the GUI version from the operating system prompt with the command: C:\BOB\FILES> sqlplusw If you use a shortcut or menu option to launch SQL*Plus, the current directory is the directory the operating system associates with the launch mechanism. So how would you change the current directory once you're inside SQL*Plus? It depends on the version. In the console program, you can't do it. You have to exit, change directories in the operating system, and restart SQL*Plus. In the GUI version, though, completing a File Open or File Save menu command will have the side effect of changing the current directory. If you're running iSQL*Plus, the concept of the current directory is relevant only in the browser's file save and retrieve dialogs, so the behavior will vary by browser. If your script file is in another directory, you can precede the filename with the path:[*] [*] As a pleasant surprise, you can use forward slashes as directory delimiters on both Unix and Microsoft operating systems, at least as of Oracle8i Database. This allows your scripts to port more easily between operating systems. SQL> @/files/src/release/1.0/abc.pkg The idea of running scripts in other directories raises an interesting question. What if abc.pkg is located in this other directory and, in turn, calls other scripts? It might contain the lines: REM Filename: abc.pkg @abc.pks @abc.pkb (Any line beginning with REM is a comment or "remark" that SQL*Plus ignores.) Executing the abc.pkg script is supposed to run abc.pks and abc.pkb. But because I have not included path information, where will SQL*Plus look for these other files? Let's see: C:\BOB\FILES> sqlplus ... SQL> @/files/src/release/1.0/abc.pkg SP2-0310: unable to open file "abc.pks" SP2-0310: unable to open file "abc.pkb" It looks only in the directory where I started. To address this problem, Oracle created the @@ command. This double at-sign means during this call, "pretend I have changed the current directory to be that of the currently executing file." So, the preferred way of writing the calls in the abc.pkg script is: REM Filename: abc.pkg @@abc.pks @@abc.pkb Now I get: C:\BOB\FILES> sqlplus ... SQL> @/files/src/release/1.0/abc.pkg Package created. Package body created. ... just as I was hoping. 2.1.6. Other SQL*Plus Tasks There are dozens of commands specific to SQL*Plus, but I have space to mention only a few more that are particularly important or particularly confusing. For a thorough treatment of this venerable product, get a copy of Jonathan Gennick's book Oracle SQL*Plus: The Definitive Guide or, for quick reference, his Oracle SQL*Plus Pocket Reference (O'Reilly). 2.1.6.1 Setting your preferences As with many command-line environments, you can change the behavior of SQL*Plus by changing the value of some of its built-in variables and settings. We've already seen one example, the SET SERVEROUTPUT statement. There are many options on the SQL*Plus SET command, such as SET SUFFIX (changes the default file extension) and SET LINESIZE n (sets the maximum number of characters in each displayed line before wrapping). To see all the SET values applicable to your current session, use the command: SQL> SHOW ALL If you're using the GUI version of SQL*Plus, you can also view and set these preferences by choosing the Options Environment menu option. SQL*Plus can also create and manipulate its own in-memory variables, and it sets aside a few special variables that will affect its behavior. Actually, there are two separate types of variables in SQL*Plus: DEFINEs and bind variables . To assign a value to a DEFINE variable, you can use the DEFINE command: SQL> DEFINE x = "the answer is 42" To view the value of x, specify: SQL> DEFINE x DEFINE X = "the answer is 42" (CHAR) You would refer to such a variable using an ampersand (&). SQL*Plus does a simple substitution before sending the statement to Oracle, so you will need single-quote marks around the variable when you want to use it as a literal string. SELECT '&x' FROM DUAL; For bind variables, you first declare the variable. You can then use it in PL/SQL, and display it using the SQL*Plus PRINT command: SQL> VARIABLE x VARCHAR2(10) SQL> BEGIN 2 :x := 'hullo'; 3 END; 4 / PL/SQL procedure successfully completed. SQL> PRINT :x X -------------------------------- hullo This can get a little bit confusing because there are now two different "x" variables, one that has been DEFINEd and one that has been declared. SQL> SELECT :x, '&x' FROM DUAL; old 1: SELECT :x, '&x' FROM DUAL new 1: SELECT :x, 'the answer is 42' FROM DUAL :X 'THEANSWERIS42' -------------------------------- ---------------- hullo the answer is 42 Just remember that DEFINEs are always character strings expanded by SQL*Plus, and declared variables are used as true bind variables in SQL and PL/SQL. 2.1.6.2 Saving output to a file Frequently, you will want to save output from a SQL*Plus session to a fileperhaps because you are generating a report, or because you want a record of your actions, or because you are dynamically generating commands to execute later. An easy way to do this in SQL*Plus is to use its SPOOL command: SQL> SPOOL report SQL> @run_report ...output scrolls past and gets written to the file report.lst... SQL> SPOOL OFF The first command, SPOOL report, tells SQL*Plus to save everything from that point forward into the file report.lst. The file extension of .lst is the default and can be overridden by supplying your own extension in the SPOOL command: SQL> SPOOL report.txt SPOOL OFF tells SQL*Plus to stop saving the output and to close the file. The SPOOL command works fine in the GUI version of SQL*Plus, but it does not work explicitly when using iSQL*Plus. Instead, save output to a file by setting the iSQL*Plus "Output:" drop-down menu to "File." 2.1.6.3 Exiting SQL*Plus To exit SQL*Plus and return to the operating system, use the EXIT command: SQL> EXIT If you happen to be spooling when you exit, SQL*Plus will stop spooling and close the spool file. What happens if you modify some table data during your session but then exit before ending the transaction with an explicit transaction control statement? By default, exiting SQL*Plus forces a COMMIT, unless your sessions end with a SQL error, and you have issued the SQL*Plus' WHENEVER SQLERROR EXIT ROLLBACK command (see the later section, "Error Handling in SQL*Plus"). To disconnect from the database but remain connected to SQL*Plus, use the command DISCONNECT, which will look something like this in action: SQL> DISCONNECT Disconnected from Personal Oracle Database 10g Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> You don't have to use DISCONNECT to change connectionsyou can just issue a CONNECT instead, and SQL*Plus will drop the first connection before connecting you to the new one. However, there is a good reason why you might want to disconnect before reconnecting: if you happen to be using operating system authentication,[*] the script might reconnect itself automatically ... maybe to the wrong account. I've seen it happen. [*] Operating system authentication is a way that you can bypass the username/password prompt when you log in to SQL*Plus. 2.1.6.4 Editing a statement SQL*Plus keeps the most recently issued statement in a buffer, and you can edit this statement using either the built-in line editor or an external editor of your choosing. To start with, I'll show how to set and use an external editor. Use the EDIT command to have SQL*Plus save the current command buffer to a file, temporarily pause SQL*Plus, and invoke the editor: SQL> EDIT By default, the file will be saved with the name afiedt.buf, but you can change that with the SET EDITFILE command. Or, if you want to edit an existing file, just supply its name as an argument to EDIT: SQL> EDIT abc.pkg Once you've saved the file and exited the editor, the SQL*Plus session will read the contents of the newly edited file into its buffer, and then resume. The default external editors that Oracle assumes are: ed for Unix, Linux, and relatives Notepad For Microsoft Windows variants Although the selection of default editors is actually hardcoded into the sqlplus executable file, you can easily change the current editor by assigning your own value to the SQL*Plus _EDITOR variable. Here's an example that I frequently use: SQL> DEFINE _EDITOR = /bin/vi where /bin/vi is the full path to an editor that's popular among a handful of strange people. I recommend using the editor's full pathname here, for security reasons. If you really want to use SQL*Plus' built-in line editor (and it can be really handy), the essential commands you need to know are: L Lists the most recent statement. n Makes the nth line of the statement the current line. DEL Deletes the current line. C /old/new/ In the current line, changes the first occurrence of old to new. The delimiter (here a forward slash) can be any arbitrary character. n text Makes text the current text of line n. I Inserts a line below the current line. To insert a new line prior to line 1, use a line zero command (e.g., 0 text). By the way, EDIT works just fine in the GUI version of SQL*Plus, or you can copy and paste as well. If you are using iSQL*Plus, EDIT won't work, but you can either copy and paste from its editing window or use the "Save Script" and "Load Script" buttons. 2.1.6.5 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: It searches for the file $ORACLE_HOME/qlplus/admin/glogin.sql 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. Next, it runs the file login.sql in the current directory, if it exists.[*] [*] If it doesn't exist, and you have set the environment variable SQLPATH to one or more colon-delimited directories, SQL*Plus will search through those directories one at a time and execute the first login.sql that it finds. As a rule, I don't use SQLPATH because I am easily confused by this sort of skulking about. 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, glogin.sql executes, followed by login.sql; in the case of conflicting preferences or variables, the last setting wins. Here are a few of my favorite login.sql settings: REM Number of lines of SELECT statement output before reprinting headers SET PAGESIZE 999 REM Width of displayed page, expressed in characters SET LINESIZE 132 REM Enable display of DBMS_OUTPUT messages. Use 1000000 rather than REM "UNLIMITED" for databases earlier than Oracle Database 10g Release 2 SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED REM Change default to "vi improved" editor DEFINE _EDITOR = /usr/local/bin/vim REM Format misc columns commonly retrieved from data dictionary COLUMN segment_name FORMAT A30 WORD_WRAP COLUMN object_name FORMAT A30 WORD_WRAP REM set the prompt (works in SQL*Plus from Oracle9i Database or later) SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > " 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.7. 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 the current session, SQL*Plus terminates 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 sets its return code to a nonzero value, which you can detect in the calling environment.[*] Otherwise, SQL*Plus always ends with a 0 return code, which may falsely imply that the script succeeded. [*] Using, for example, $? in the Unix shell or %ERRORLEVEL% in Microsoft Windows. Another form of this command is: 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.8. Why You Will Love and Hate SQL*Plus In addition to the features you just read about, the following 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 have been slow to add support for Oracle's object types, which were introduced a number of years ago. 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. 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. Unless I break down and use what I consider the dangerous SQLPATH feature, SQL*Plus looks only in the startup directory for login.sql; it would be better if it would fall back to look in my home directory for the startup script. 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. For SQL*Plus versions prior to Oracle Database 10g, running the CONNECT command erases all your SQL*Plus settings. In other words, SQL*Plus does not rerun the login.sql (or glogin.sql) startup script, causing SERVEROUTPUT to return to its annoying default state, among other things. This also causes trouble for folks who create fancy prompts in their login.sql filefor example, a prompt such as "TEST8i>" will remain even after doing a CONNECT to the PROD8i database. However, Oracle Database 10g reruns the login.sql or glogin.sql script automatically when you reconnect. And Oracle9i Database Release 2 introduced a variable called _CONNECT_IDENTIFIER that always has the value of the Oracle SID to which SQL*Plus is currently connected.