Oracle SQL*Plus, the Definitive Guide
Frequently Asked Questions
How do I tell SQL*Plus to use an editor other than NOTEPAD?
SQL*Plus uses the user variable named _EDITOR to point to the executable to be invoked in response to the EDIT command. Under Windows 3.1/95/NT/98/2000/whatever, the default setting is "NOTEPAD". You can change this value to point to any editor you like. The following example sets the editor to WordPad
define _editor="C:\PROGRA~1\ACCESS~1\WORDPAD.EXE" The DEFINE command only changes the setting for the current session. To make the setting permenant, place the DEFINE command in your login.sql file. That way it will be executed each time you start SQL*Plus. Chapter 11 in the book contains more information about login.sql.
How do I make my editor customization, or any other customization, stick?
If you are on Windows, create a file named login.sql, and
place the necessary command(s) in that file. Save the login.sql
file to your Oracle bin directory. For example: c:\orawin95\bin.
The reason for saving it to the bin directory is that the SQL*Plus shortcut
will have that directory specified as the working directory. Whenever
you start SQL*Plus, it will look in the current directory for login.sql.
If it finds the file, it will execute it.
SQL*Plus also follows the sqlpath when searching for login.sql.
So if you want to put login.sql somewhere else, just change
the registry setting for sqlpath to include that directory.
Another option is to edit your glogin.sql file. That should
be in c:\orawin95\plus80, if you are running Oracle 8.0.x.
The procedure is the same on Unix, except for the advice about placing
login.sql in the Oracle bin directory. Place
login.sql somewhere else, perhaps in your home directory,
and set the sqlpath environment variable to point to that directory.
What does the message "Input truncated to XX characters" mean?
SQL*Plus generates this message when you execute a script file that does not have a newline character following the last line of the script. What happens is that SQL*Plus hits the end-of-file before it hits the end of the line. You and I may not regard that as an "error", but SQL*Plus does. To get rid of this message, edit your script file, go to the end of the last line, press ENTER, and resave the file.
Why does this book have no information on the SELECT statement?
This question is essentially asking why my SQL*Plus book doesn't have any information about writing SQL queries. The short answer is that SQL*Plus and SQL are two different things, and my book focuses on getting the most out of the SQL*Plus utility, not on the SQL language itself. The longer answer would have to include the fact that originally I did plan a chapter on developing SQL queries. I had also planned one on PL/SQL. After some discussion, my editor and I decided to drop both because neither subject could be covered adequately in a single chapter (we thought), and we didn't want to damage the focus of the book. In retrospect, I do wish that I had included one chapter on just the SELECT statement. If/when O'Reilly and I decide to revise the book, I'll probably add that chapter. The book does use SQL*Plus as an excuse to range over a variety of topics. Adding one more to the list might be a good idea.
How do I determine the columns involved in a check constraint?
The book may leave you with the impression that the only way to get this information is to parse the SEARCH_CONDITION column of the ALL_CONSTRAINTS view. That certainly is one way to get the information. Another alternative is to select from ALL_CONS_COLUMNS. Even for a check constraint, ALL_CONS_COLUMNS will list the columns involved. It won't give you the check condition, but it will get you the columns.
How can I save formatting commands along with my SQL statement?
You can use the SQL*Plus SAVE command to write the current SQL statement (the one in the SQL buffer) to a .sql file. Only the SQL statement is written. Formatting commands such as COLUMN and TTITLE are not saved. Unfortunately, there is no automatic way to save all these settings. If it's important for you to save these, you're better off using a text editor such as Notepad or vi to create a script with all the commands that you need, than you are in trying to figure out which commands you issued after the fact.
Do the PL/SQL and SQL Engines communicate?
Figure 1-1 on page 5 illustrates the manner in which SQL queries are sent to the SQL engine and PL/SQL blocks are sent to the PL/SQL engine. This figure shows a somewhat simplified view of what really happens. Communication does occur between the two engines, even though the figure doesn't show it. SQL statements can invoke PL/SQL functions, and PL/SQL program units can execute SQL statements. The point I was trying to make here was that the three entities: SQL*Plus, PL/SQL, and SQL are all different things. While it would be technically correct to include arrows between the two engines, such arrows weren't necessary to make my point, and they would have cluttered up the figure.
Why can't I recreate a trigger using the output from SHOW_TRIGGER.SQL?
The SHOW_TRIGGER.SQL script outputs the FOR EACH ROW clause prior to the REFERENCING clause. For example:
TRIGGER JEFF.emp_hire_date_check
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW
REFERENCING NEW AS NEW OLD AS OLD
...
In order to be syntactically correct, that order should be reversed. In other words, the script output should look like this:
TRIGGER JEFF.emp_hire_date_check
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW
REFERENCING NEW AS NEW OLD AS OLD
...
Unfortunately, correcting the order is non-trivial, because of the way in which trigger definitions are represented in the ALL_TRIGGERS view. The DESCRIPTION column returns multiple lines, and the contents of the REFERENCES column really needs to be inserted into the middle of those lines. I'll probably address this when I revise the book. For now, SHOW_TRIGGER.SQL is fine for looking at a trigger, but you'll have to transpose the REFERENCING and FOR EACH ROW clauses if you want to use the script output to recreate a trigger.
I want to download the BLD_DB.SQL script file with your example data from the O'Reilly web site. I went to http://www.oreilly.com/catalog/orsqlplus, but I couldn't find your script file. Where is it?
You went to the correct page, you just didn't see the link. From the catalog page you were on, you need to click the "Examples" link. That link will take you to: http://examples.oreilly.com/9781565925786/. From there, download the file named bld_db.zip. Unzip the file (using Winzip for example), and follow the instructions in the resulting readme.txt file. The readme.txt on the web site is the same one that is inside the ZIP file.