|SQL*Plus User's Guide and Reference||
See your Oracle installation and user's manual(s) for your operating system to learn which physical key performs each function on the keyboard commonly used with your host computer.
Note: A SQL*Plus key may perform different functions when pressed in other products or the operating system.
Fill in each blank in Table 2 - 1 with the name of the corresponding keyboard key. Then locate each key on your keyboard.
|SQL*Plus Key Name||Keyboard Key Name||Function|
|[Return]||___________||End a line of input.|
|[Backspace]||___________||Move cursor left one character to correct an error.|
|[Pause]||___________||Suspend program operation and display of output.|
|[Resume]||___________||Resume program operation and output [Pause].|
|[Cancel]||___________||Halt program operation; return to the SQL*Plus command prompt.|
|[Interrupt]||___________||Exit SQL*Plus and return to the host operating system.|
|Table 2 - 1. SQL*Plus Special Keys and their Functions|
Example 2-1 Starting SQL*Plus
This example shows you how to start SQL*Plus. Follow the steps shown.
$ SQLPLUS SCOTT/TIGER
and press [Return]. You can also arrange to log in to SQL*Plus automatically when you log on to your host operating system. See the Oracle installation and user's manual(s) provided for your operating system for details.
Example 2-2 Exiting SQL*Plus
To leave SQL*Plus, enter the EXIT command at the SQL*Plus command prompt:
SQL*Plus displays the version of Oracle from which you disconnected and the versions of tools available through SQL*Plus. After a moment you will see the operating system prompt.
Before continuing with this chapter, follow steps 3, 4, and 5 of Example 2-1 to start SQL*Plus again. Alternatively, log in using the shortcut shown under "Shortcuts to Starting SQL*Plus" above.
To tell SQL*Plus what to do, simply type the command you wish to enter. Usually, you separate the words in a command from each other by a space or tab. You can use additional spaces or tabs between words, if you wish, to make your commands more readable.
Note: You will see examples of spacing and indentation throughout this Guide. When you enter the commands in the exercises, you do not have to space them as shown, but you may find them clearer to read if you do.
You can enter commands in capitals or lowercase. For the sake of clarity, all table names, column names, and commands in this Guide appear in capital letters.
You can enter three kinds of commands at the command prompt:
If you get a response indicating that help is not available, consult your database administrator. For more details about the help system, see the HELP command.
Example 2-3 Entering a SQL Command
In this example, you will enter and execute a SQL command to display the employee number, name, job, and salary of each employee in the sample table EMP.
SQL> SELECT EMPNO, ENAME, JOB, SAL
2 FROM EMP WHERE SAL < 2500;
EMPNO ENAME JOB SAL ---------- ------------ ---------- ---------- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 7782 CLARK MANAGER 2450 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 800 7934 MILLER CLERK 1300 9 rows selected SQL>
Dividing a SQL Command into Separate Lines You can divide your SQL command into separate lines at any points you wish, as long as individual words are not split between lines. Thus, you can enter the query you entered in Example 2-3 on one line:
SQL> SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL < 2500;
You can also enter the query on several lines:
SQL> SELECT 2 EMPNO, ENAME, JOB, SAL 3 FROM EMP 4 WHERE SAL < 2500;
In this Guide, you will find most SQL commands divided into clauses, one clause on each line. In Example 2-3, for instance, the SELECT and FROM clauses were placed on separate lines. Many people find this most convenient, but you may choose whatever line division makes your command most readable to you.
Ending a SQL Command You can end a SQL command in one of three ways:
Note: You cannot enter a comment (/* */) on the same line on which you enter a semicolon.
A slash (/) on a line by itself also tells SQL*Plus that you wish to run the command. Press [Return] at the end of the last line of the command. SQL*Plus prompts you with another line number. Type a slash and press [Return] again. SQL*Plus will execute the command and store it in the buffer (see "The SQL Buffer" below for details).
A blank line tells SQL*Plus that you have finished entering the command, but do not want to run it yet. Press [Return] at the end of the last line of the command. SQL*Plus prompts you with another line number.
Press [Return] again; SQL*Plus now prompts you with the SQL*Plus command prompt. SQL*Plus does not execute the command, but stores it in the SQL buffer (see "The SQL Buffer" below for details). If you subsequently enter another SQL command, SQL*Plus overwrites the previous command in the buffer.
Creating Stored Procedures Stored procedures are PL/SQL functions, packages, or procedures. To create stored procedures, you use SQL CREATE commands. The following SQL CREATE commands are used to create stored procedures:
When you use CREATE to create a stored procedure, a message appears if there are compilation errors. To view these errors, you use SHOW ERRORS. For example:
SQL> SHOW ERRORS PROCEDURE ASSIGNVL
See Chapter 6 for a description of the SHOW command.
To execute a PL/SQL statement that references a stored procedure, you can use the EXECUTE command. EXECUTE runs the PL/SQL statement that you enter immediately after the command. For example:
SQL> EXECUTE :ID := EMP_MANAGEMENT.GET_ID('BLAKE')
See Chapter 6 for a description of the EXECUTE command.
SQL*Plus does not store the semicolon or the slash you type to execute a command in the SQL buffer.
Note: SQL*Plus commands are not stored in the SQL buffer.
To enter a PL/SQL subprogram in SQL*Plus, you need to be in PL/SQL mode. You are placed in PL/SQL mode when
SQL*Plus stores the subprograms you enter at the SQL*Plus command prompt in the SQL buffer. Execute the current subprogram by issuing a RUN or slash (/) command. Likewise, to execute a SQL CREATE command that creates a stored procedure, you must also enter RUN or slash (/). A semicolon (;) will not execute these SQL commands as it does other SQL commands.
SQL*Plus sends the complete PL/SQL subprogram to Oracle for processing (as it does SQL commands). See your PL/SQL User's Guide and Reference for more information.
You might enter and execute a PL/SQL subprogram as follows:
SQL> DECLARE 2 x NUMBER := 100; 3 BEGIN 4 FOR i IN 1..10 LOOP 5 IF MOD (i, 2) = 0 THEN --i is even 6 INSERT INTO temp VALUES (i, x, 'i is even'); 7 ELSE 8 INSERT INTO temp VALUES (i, x, 'i is odd'); 9 END IF; 10 x := x + 100; 11 END LOOP; 12 END; 13 . SQL> / PL/SQL procedure successfully completed.
When you run a subprogram, the SQL commands within the subprogram may behave somewhat differently than they would outside of the subprogram. See your PL/SQL User's Guide and Reference for detailed information on the PL/SQL language.
To speed up command entry, you can abbreviate many SQL*Plus commands to one or a few letters. Abbreviations for some SQL*Plus commands are described along with the commands in Chapters 3, 4, and 5. For abbreviations of all SQL*Plus commands, refer to the command descriptions.
Example 2-4 Entering a SQL*Plus Command
This example shows how you might enter a SQL*Plus command to change the format used to display the column SAL of the sample table EMP.
SQL> COLUMN SAL FORMAT $99,999 HEADING SALARY
SQL> RUN 1 SELECT EMPNO, ENAME, JOB, SAL 2* FROM EMP WHERE SAL < 2500 EMPNO ENAME JOB SALARY -------- ------------- ---------- -------- 7369 SMITH CLERK $800 7499 ALLEN SALESMAN $1,600 7521 WARD SALESMAN $1,250 7654 MARTIN SALESMAN $1,250 7782 CLARK MANAGER $2,450 7844 TURNER SALESMAN $1,500 7876 ADAMS CLERK $1,100 7900 JAMES CLERK $800 7934 MILLER CLERK $1,300
The COLUMN command formatted the column SAL with a dollar sign ($) and a comma (,) and gave it a new heading. The RUN command then reran the query of Example 2-3, which was stored in the buffer. SQL*Plus does not store SQL*Plus commands in the SQL buffer.
Continuing a Long SQL*Plus Command on Additional Lines You can continue a long SQL*Plus command by typing a hyphen at the end of the line and pressing [Return]. If you wish, you can type a space before typing the hyphen. SQL*Plus displays a right angle-bracket (>) as a prompt for each additional line. For example:
SQL> COLUMN SAL FORMAT $99,999 - > HEADING SALARY
Ending a SQL*Plus Command You do not need to end a SQL*Plus command with a semicolon. When you finish entering the command, you can just press [Return]. If you wish, however, you can enter a semicolon at the end of a SQL*Plus command.
The examples in this Guide are based on running SQL*Plus with the system variables at their default settings. Depending on the settings of your system variables, your output may appear slightly different than the output shown in the examples. (Your settings might differ from the default settings if you have a SQL*Plus LOGIN file on your computer.)
For more information on system variables and their default settings, see the SET command. For details on the SQL*Plus LOGIN file, refer to the section "Setting Up Your SQL*Plus Environment" under "Saving Commands for Later Use" and to the SQLPLUS command.
To list the current setting of a SET command variable, enter SHOW followed by the variable name at the command prompt. See the SHOW command for information on other items you can list with SHOW.
You control the autocommit feature with the SQL*Plus SET command's AUTOCOMMIT variable. It has these forms:
SET AUTOCOMMIT ON
Turns autocommit on.
SET AUTOCOMMIT OFF
Turns autocommit off (the default).
SET AUTOCOMMIT n
Commits changes after n SQL commands or PL/SQL blocks.
Example 2-5 Turning Autocommit On
To turn the autocommit feature on, enter
SQL> SET AUTOCOMMIT ON
Until you change the setting of AUTOCOMMIT, SQL*Plus will automatically commit changes from each SQL command or PL/SQL block that specifies changes to the database. After each autocommit, SQL*Plus displays the following message:
When the autocommit feature is turned on, you cannot roll back changes to the database.
To commit changes to the database after a number of SQL DML commands or PL/SQL blocks, for example, ten, enter
SQL> SET AUTOCOMMIT 10
SQL*Plus counts SQL DML commands and PL/SQL blocks as they are executed and commits the changes after the tenth SQL DML command or PL/SQL block.
Note: For this feature, a PL/SQL block is regarded as one transaction, regardless of the actual number of SQL commands contained within it.
To turn the autocommit feature off again, enter the following command:
SQL> SET AUTOCOMMIT OFF
To confirm that AUTOCOMMIT is now set to OFF, enter the following SHOW command:
SQL> SHOW AUTOCOMMIT autocommit OFF
For more information, see the AUTOCOMMIT variable of the SET command.
Note: Pressing [Cancel] will not stop the printing of a file that you have sent to a printer with the OUT clause of the SQL*Plus SPOOL command. (You will learn about printing query results.) You can stop the printing of a file through your operating system; see your operating system manuals for information.
To delete all timers, enter CLEAR TIMING at the command prompt.
To run a host operating system command, enter the SQL*Plus command HOST followed by the host operating system command. For example, this SQL*Plus command runs a host command, DIRECTORY *.SQL:
SQL> HOST DIRECTORY *.SQL
When the host command finishes running, the SQL*Plus command prompt appears again.
SQL> RUNFORM myform
Example 2-6 Using the DESCRIBE Command
To list the column definitions of the three columns in the sample table DEPT, enter
SQL> DESCRIBE DEPT
The following output results:
Name Null? Type ------------------------------- ------- ---------- DEPTNO NOT NULL NUMBER(2) DNAME CHAR(14) LOC CHAR(13)
Note: DESCRIBE accesses information in the Oracle data dictionary. You can also use SQL SELECT commands to access this and other information in the database. See your Oracle7 Server SQL Language Reference Manual for details.
Example 2-7 Using the DESCRIBE Command
To list the definition of a function called AFUNC, enter
SQL> DESCRIBE afunc
The following output results:
FUNCTION afunc RETURNS NUMBER Argument Name Type In/Out Default? --------------- -------- -------- --------- F1 CHAR IN F2 NUMBER IN
If you wish, you can use the PAUSE variable of the SQL*Plus SET command to have SQL*Plus pause after displaying each screen of a query or report. Refer to SET for details.
Example 2-8 Interpreting an Error Message
For example, if you misspell the name of a table while entering a command, an error message will tell you that the table or view does not exist:
SQL> DESCRIBE DPT Object does not exist.
You will often be able to figure out how to correct the problem from the message alone. If you need further explanation, take one of the following steps to determine the cause of the problem and how to correct it:
Copyright © 1996 Oracle Corporation.
All Rights Reserved.