Release 9.2
Part Number A90842-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| SQL*Plus User's Guide and Reference Release 9.2 Part Number A90842-01 |
|
This chapter helps you learn the basics of using SQL*Plus, including the following topics:
Unless stated otherwise, descriptions of command use are generally applicable to both command-line and iSQL*Plus user interfaces.
In command-line SQL*Plus, you type commands at the SQL*Plus prompt. 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 to make your commands more readable.
Case sensitivity is operating system specific. 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 in either the command-line or the iSQL*Plus user interfaces:
The manner in which you continue a command on additional lines, end a command, or execute a command differs depending on the type of command you wish to enter and run. Examples of how to run and execute these types of commands are found on the following pages.
You can use the Backspace and the Delete keys in both command-line SQL*Plus and iSQL*Plus. In iSQL*Plus, you can cut and paste using your web browser's edit keys to edit the statements in the Input area. You can also cut or copy scripts or statements from other applications such as text editors, and paste them directly into the Input area.
In iSQL*Plus, the Save Script button enables you to save scripts to a text file. You can also load scripts with the Load Script button. Saving and loading scripts may be useful when editing and testing.
The area where SQL*Plus stores your most recently entered SQL command or PL/SQL block (but not SQL*Plus commands) is called the SQL buffer. The command or block remains there until you enter another. If you want to edit or re-run the current SQL command or PL/SQL block, you may do so without re-entering it. For more information about editing or re-running a command or block stored in the buffer see "Running Scripts".
SQL*Plus does not store SQL*Plus commands, or the semicolon or slash characters you type to execute a command in the SQL buffer.
In command-line SQL*Plus, you type a command and direct SQL*Plus to execute it by pressing the Return key. SQL*Plus processes the command and re-displays the command prompt when ready for another command.
In iSQL*Plus, you type a command or a script into the Input area and click the Execute button to execute the contents of the Input area. The results of your script are displayed below the Input area by default. Use the History screen to access and rerun commands previously executed in the current session.
iSQL*Plus executes a SQL or PL/SQL statement at the end of the Input area, even if it is incomplete or does not have a final ";" or "/". If you intend to run iSQL*Plus scripts in the SQL*Plus command-line, you should make sure you use a ";" or "/" to terminate your statements.
iSQL*Plus retains the state of your current system variables and other options from one execution to the next. If you use the History screen to re-execute a script, you may get different results from those previously obtained, depending on the current system variable values.
Some SQL*Plus commands have no logical sense or are not applicable in iSQL*Plus. See Appendix D, "Commands Not Supported in iSQL*Plus" for a list of SQL*Plus commands not supported in iSQL*Plus.
The SQL command language enables you to manipulate data in the database. See your Oracle9i SQL Reference for information on individual SQL commands.
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 EMP_DETAILS_VIEW view.
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
If you make a mistake, use Backspace to erase it and re-enter. When you are done, press Return to move to the next line.
FROM EMP_DETAILS_VIEW WHERE SALARY > 12000;
The semicolon (;) means that this is the end of the command. Press Return. SQL*Plus processes the command and displays the results on the screen:
EMPLOYEE_ID LAST_NAME JOB_ID SALARY ----------- ------------------------- ---------- -------------- 100 King AD_PRES $24,000 101 Kochhar AD_VP $17,000 102 De Haan AD_VP $17,000 145 Russell SA_MAN $14,000 146 Partners SA_MAN $13,500 201 Hartstein MK_MAN $13,000 6 rows selected.
After displaying the results and the number of rows retrieved, SQL*Plus displays the command prompt again. If you made a mistake and therefore did not get the results shown above, simply re-enter the command.
The headings may be repeated in your output, depending on the setting of a system variable called PAGESIZE. Sometimes, the result from a query will not fit the available page width. You will need to adjust a system variable called LINESIZE, which sets the width of the output in characters, see "Setting Page Dimensions". Typically, in the examples in this guide this is set to 70 characters. You may need to SET LINESIZE to 70 so the query output appears the same as in this guide. Whether you see the message concerning the number of records retrieved depends on the setting of a system variable called FEEDBACK. You will learn more about system variables in "System Variables that Affect How Commands Run". To save space, the number of records selected will not be shown in the rest of the examples in this guide.
Just as spoken language has syntax rules that govern the way we assemble words into sentences, SQL*Plus has syntax rules that govern how you assemble words into commands. You must follow these rules if you want SQL*Plus to accept and execute your commands.
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 5-1, "Entering a SQL Command" on three lines:
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
In this guide, you will find most SQL commands divided into clauses, one clause on each line. In Example 5-1, "Entering a SQL Command", for instance, the SELECT and FROM clauses were placed on separate lines. Many people find this clearly visible structure helpful, but you may choose whatever line division makes commands most readable to you.
You can end a SQL command in one of three ways:
A semicolon (;) tells SQL*Plus that you want to run the command. Type the semicolon at the end of the last line of the command, as shown in Example 5-1, "Entering a SQL Command", and press Return. SQL*Plus will process the command and store it in the SQL buffer (see "The SQL Buffer" for details). If you mistakenly press Return before typing the semicolon, SQL*Plus prompts you with a line number for the next line of your command. Type the semicolon and press Return again to run the command.
|
Note: You cannot enter a comment on the same line after a semicolon. For more information about placing comments, see "Placing Comments in Scripts". |
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 executes the command and stores it in the buffer (see "The SQL Buffer" for details).
A blank line in a SQL statement or script 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.
|
Note: You can change the way blank lines appear and behave in SQL statements using the SET SQLBLANKLINES command. For more information about changing blank line behavior, see the SET command. |
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" for details). If you subsequently enter another SQL command, SQL*Plus overwrites the previous command in the buffer.
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:
Entering any of these commands places you in PL/SQL mode, where you can enter your PL/SQL subprogram. For more information, see "Running PL/SQL Blocks". When you are done typing your PL/SQL subprogram, enter a period (.) on a line by itself to terminate PL/SQL mode. To run the SQL command and create the stored procedure, you must enter RUN or slash (/). A semicolon (;) will not execute these CREATE commands.
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:
SHOW ERRORS PROCEDURE ASSIGNVL
For more information about the SHOW command, see Chapter 13, "SQL*Plus Command Reference".
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:
EXECUTE :ID := EMPLOYEE_MANAGEMENT.GET_ID('BLAKE')
For more information about the EXECUTE command, see Chapter 13, "SQL*Plus Command Reference".
You can run (or re-run) the current SQL command or PL/SQL block by entering the RUN command or the slash (/) command at the command prompt. The RUN command lists the SQL command or PL/SQL block in the buffer before executing the command or block; the slash (/) command simply runs the SQL command or PL/SQL block.
You can also use PL/SQL subprograms (called blocks) to manipulate data in the database. See your PL/SQL User's Guide and Reference for information on individual PL/SQL statements.
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 treats PL/SQL subprograms in the same manner as SQL commands, except that a semicolon (;) or a blank line does not terminate and execute a block. Terminate PL/SQL subprograms by entering a period (.) by itself on a new line. You can also terminate and execute a PL/SQL subprogram by entering a slash (/) by itself on a new line.
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:
DECLARE x NUMBER := 100; BEGIN FOR i IN 1..10 LOOP IF MOD (i, 2) = 0 THEN --i is even INSERT INTO temp VALUES (i, x, 'i is even'); ELSE INSERT INTO temp VALUES (i, x, 'i is odd'); END IF; x := x + 100; END LOOP; END; . /
When you run a subprogram, the SQL commands within the subprogram may behave somewhat differently than they would outside the subprogram. See your PL/SQL User's Guide and Reference for detailed information on the PL/SQL language.
You can use SQL*Plus commands to manipulate SQL commands and PL/SQL blocks and to format and print query results. SQL*Plus treats SQL*Plus commands differently than SQL commands or PL/SQL blocks. For information on individual SQL*Plus commands, see Chapter 13, "SQL*Plus Command Reference".
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 Chapter 3, "Configuring SQL*Plus", Chapter 5, "SQL*Plus Basics", and Chapter 6, "Using Scripts in SQL*Plus". For abbreviations of all SQL*Plus commands, see Chapter 13, "SQL*Plus Command Reference".
This example shows how you might enter a SQL*Plus command to change the format used to display the column SALARY of the sample view, EMP_DETAILS_VIEW.
COLUMN SALARY FORMAT $99,999 HEADING 'MONTHLY SALARY'
If you make a mistake, use Backspace to erase it and re-enter. When you have entered the line, press Return. SQL*Plus notes the new format and displays the SQL*Plus command prompt again, ready for a new command.
RUN
EMPLOYEE_ID LAST_NAME JOB_ID MONTHLY SALARY ----------- ------------------------- ---------- -------------- 100 King AD_PRES $24,000 101 Kochhar AD_VP $17,000 102 De Haan AD_VP $17,000 145 Russell SA_MAN $14,000 146 Partners SA_MAN $13,500 201 Hartstein MK_MAN $13,000 6 rows selected.
The COLUMN command formatted the column SALARY with a dollar sign ($) and a comma (,) and gave it a new heading. The RUN command then re-ran the query of Example 5-1, "Entering a SQL Command", which was stored in the buffer. SQL*Plus does not store SQL*Plus commands in the SQL buffer.
SQL*Plus commands have a different syntax from SQL commands or PL/SQL blocks.
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:
COLUMN SALARY FORMAT $99,999 - HEADING 'MONTHLY SALARY'
Since SQL*Plus identifies the hyphen as a continuation character, entering a hyphen within a SQL statement is ignored by SQL*Plus. SQL*Plus does not identify the statement as a SQL statement until after the input processing has joined the lines together and removed the hyphen. For example, entering the following:
SELECT 200 - 100 FROM DUAL;
returns the error:
SELECT 200 100 FROM DUAL * ERROR at line 1: ORA-00923: FROM keyword not found where expected
To ensure that the statement is interpreted correctly, reposition the hyphen from the end of the first line to the beginning of the second line.
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 SQL*Plus command SET controls many variables--called SET variables or system variables--the settings of which affect the way SQL*Plus runs your commands. System variables control a variety of conditions within SQL*Plus, including default column widths for your output, whether SQL*Plus displays the number of records selected by a command, and your page size. System variables are also called SET variables.
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 "SQL*Plus Configuration" 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.
Through the SQL DML commands UPDATE, INSERT, and DELETE--which can be used independently or within a PL/SQL block--specify changes you wish to make to the information stored in the database. These changes are not made permanent until you enter a SQL COMMIT command or a SQL DCL or DDL command (such as CREATE TABLE), or use the autocommit feature. The SQL*Plus autocommit feature causes pending changes to be committed after a specified number of successful SQL DML transactions. (A SQL DML transaction is either an UPDATE, INSERT, or DELETE command, or a PL/SQL block.)
You control the autocommit feature with the SQL*Plus SET command's AUTOCOMMIT variable.
To turn the autocommit feature on, enter
SET AUTOCOMMIT ON
Alternatively, you can enter the following to turn the autocommit feature on:
SET AUTOCOMMIT IMMEDIATE
Until you change the setting of AUTOCOMMIT, SQL*Plus automatically commits changes from each SQL DML command that specifies changes to the database. After each autocommit, SQL*Plus displays the following message:
COMMIT COMPLETE
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, for example, 10, enter
SET AUTOCOMMIT 10
SQL*Plus counts SQL DML commands as they are executed and commits the changes after each 10th SQL DML command.
|
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:
SET AUTOCOMMIT OFF
To confirm that AUTOCOMMIT is now set to OFF, enter the following SHOW command:
SHOW AUTOCOMMIT
AUTOCOMMIT OFF
For more information, see the AUTOCOMMIT variable of the SET command.
Suppose you have displayed the first page of a 50 page report and decide you do not need to see the rest of it. Press Cancel, the system's interrupt character, which is usually CTRL+C. SQL*Plus stops the display and returns to the command prompt.
In iSQL*Plus, click the Cancel button.
|
Note: Pressing Cancel does 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 in Chapter 7, "Formatting SQL*Plus Reports".) You can stop the printing of a file through your operating system. For more information, see your operating system's installation and user's guide. |
You can execute a host operating system command from the SQL*Plus command prompt. This is useful when you want to perform a task such as listing existing host operating system files.
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:
HOST DIRECTORY *.SQL
When the host command finishes running, the SQL*Plus command prompt appears again.
|
Note: Operating system commands entered from a SQL*Plus session using the HOST command do not effect the current SQL*Plus session. For example, setting an operating system environment variable does not effect the current SQL*Plus session, but may effect SQL*Plus sessions started subsequently. You can suppress access to the HOST command. For more information about suppressing the HOST command see Chapter 10, "SQL*Plus Security". |
While you use SQL*Plus, you may find that you need to list column definitions for a table, or start and stop the display that scrolls by. You may also need to interpret error messages you receive when you enter a command incorrectly or when there is a problem with Oracle or SQL*Plus. The following sections describe how to get help for those situations.
To see the definitions of each column in a given table or view, use the SQL*Plus DESCRIBE command.
To list the column definitions of the columns in the sample view EMP_DETAILS_VIEW, enter
DESCRIBE EMP_DETAILS_VIEW;
Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) JOB_ID NOT NULL VARCHAR2(10) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) LOCATION_ID NUMBER(4) COUNTRY_ID CHAR(2) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) DEPARTMENT_NAME NOT NULL VARCHAR2(30) JOB_TITLE NOT NULL VARCHAR2(35) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_NAME VARCHAR2(40) REGION_NAME VARCHAR2(25)
|
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 Oracle9i SQL Reference for details. |
To see the definition of a function or procedure, use the SQL*Plus DESCRIBE command.
To list the definition of a function called AFUNC, enter
DESCRIBE afunc
FUNCTION afunc RETURNS NUMBER Argument Name Type In/Out Default? --------------- -------- -------- --------- F1 CHAR IN F2 NUMBER IN
Suppose that you wish to stop and examine the contents of the screen while displaying a long report or the definition of a table with many columns. The display will pause while you examine it. To continue, press Resume.
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. For more information, refer to the SET command.
If SQL*Plus detects an error in a command, it displays an error message. See Chapter 14, "SQL*Plus Error Messages" for a list of SQL*Plus error messages.
If you attempt to execute a file that does not exist or is unavailable by entering:
START EMPLYYES.SQL
An error message indicates that the table does not exist:
SP2-0310: unable to open file "emplyyes.sql"
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:
If the error is unnumbered, look up correct syntax for the command that generated the error in Chapter 13, "SQL*Plus Command Reference" of this guide for a SQL*Plus command, in the Oracle9i SQL Reference for a SQL command, or in the PL/SQL User's Guide and Reference for a PL/SQL block. Otherwise, contact your DBA.
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|
