Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



 

Go to previous file in sequence Go to next file in sequence

Learning SQL*Plus Basics



Read this chapter while sitting at your computer and try out the examples shown. Before beginning, make sure you have access to the sample tables described in Chapter 1.

 

Getting Started

To begin using SQL*Plus, you must first become familiar with the functions of several keys on your keyboard and understand how to start and leave SQL*Plus.

 

Using the Keyboard

Several keys on your keyboard have special meaning in SQL*Plus. Table 2 - 1 lists these keys.

 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 

Starting SQL*Plus

Now that you have identified important keys on your keyboard, you are ready to start SQL*Plus.

 Example 2-1 Starting SQL*Plus

 This example shows you how to start SQL*Plus. Follow the steps shown.

 

Note: Some operating systems expect you to enter commands in lowercase letters. If your system expects lowercase, enter the SQLPLUS command in lowercase.

 

If SQL*Plus does not start, you should see a message meant to help you correct the problem. For further information, refer to the Oracle8 Server Messages manual for Oracle messages, or to your operating system manual for system messages.

 

Shortcuts to Starting SQL*Plus

When you start SQL*Plus, you can enter your username and password, separated by a slash (/), following the command SQLPLUS. For example, if your username is SCOTT and your password is TIGER, you can enter

 

$ 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.

 

Leaving SQL*Plus

When you are done working with SQL*Plus and wish to return to the operating system, enter the EXIT command at the SQL*Plus command prompt.

 Example 2-2 Exiting SQL*Plus

 To leave SQL*Plus, enter the EXIT command at the SQL*Plus command prompt:

 

SQL> EXIT
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.

 

Entering and Executing Commands

Entering Commands

Your computer's cursor, or pointer (typically an underline, a rectangular block, or a slash), appears after the command prompt. The cursor indicates the place where the next character you type will appear on your screen.

 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:

 

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.

 

Getting Help

To get online help for SQL*Plus commands, type HELP at the command prompt followed by the name of the command. For example:

 

SQL>HELP ACCEPT
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 in Chapter 7.

 

Executing Commands

After you enter the command and direct SQL*Plus to execute it, SQL*Plus processes the command and re-displays the command prompt, indicating that you can enter another command.

 

Running SQL Commands

The SQL command language enables you to manipulate data in the database. See your Oracle8 Server SQL Reference Manual for information on individual SQL commands.

 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>

Understanding SQL Command Syntax

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.

 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:

 

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 2-3, and press [Return]. SQL*Plus will process the command and store it in the SQL buffer (see "The SQL Buffer" below for details). If you mistakenly press [Return] before typing the semicolon, SQL*Plus will prompt 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.

 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:

 

Entering any of these commands places you in PL/SQL mode, where you can enter your PL/SQL subprogram (see also "Running PL/SQL Blocks" in this chapter). 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:

 

SQL> SHOW ERRORS PROCEDURE ASSIGNVL
See Chapter 7 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 7 for a description of the EXECUTE command.

 

The SQL Buffer

The area where SQL*Plus stores your most recently entered SQL command or PL/SQL block is called the SQL buffer. The command or block remains there until you enter another. Thus, if you want to edit or re-run the current SQL command or PL/SQL block, you may do so without re-entering it. See Chapter 3 for details about editing or re-running a command or block stored in the buffer.

 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.

 

Executing the Current SQL Command or PL/SQL Block from the Command Prompt

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.

 

Running PL/SQL Blocks

You can also use PL/SQL subprograms (called blocksto 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.

 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 the subprogram. See your PL/SQL User's Guide and Reference for detailed information on the PL/SQL language.

 

Running SQL*Plus Commands

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, refer to the Command Reference in Chapter 7.

 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 in Chapter 7.

 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 re-ran the query of Example 2-3, which was stored in the buffer. SQL*Plus does not store SQL*Plus commands in the SQL buffer.

 

Understanding SQL*Plus Command Syntax

SQL*Plus commands have a different syntax from SQL commands or PL/SQL blocks.

 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.

 

Variables that Affect Running Commands

The SQL*Plus command SET controls many variables--called 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 command 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 in Chapter 7. For details on the SQL*Plus LOGIN file, refer to the section "Setting Up Your SQL*Plus Environment" under "Saving Commands for Later Use" in Chapter 3 and to the SQLPLUS command in Chapter 6.

 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 in Chapter 7 for information on other items you can list with SHOW.

 

Saving Changes to the Database Automatically

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. It has these four 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.
SET AUTOCOMMIT IMMEDIATE  Turns autocommit on.
Example 2-5 Turning Autocommit On

 To turn the autocommit feature on, enter

 

SQL> SET AUTOCOMMIT ON
Alternatively, you can enter the following to turn the autocommit feature on:

 

SQL> SET AUTOCOMMIT IMMEDIATE
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:

 

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 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 in Chapter 7.

 

Stopping a Command while it is Running

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]. (Refer to Table 2 - 1 at the beginning of this chapter to see how [Cancel] is labelled on your keyboard.) SQL*Plus will stop the display and return to the command prompt.

 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 in Chapter 4.) You can stop the printing of a file through your operating system. For more information, see the Oracle installation and user's manual(s) provided for your operating system.

 

Collecting Timing Statistics on Commands You Run

Use the SQL*Plus command TIMING to collect and display data on the amount of computer resources used to run one or more commands or blocks. TIMING collects data for an elapsed period of time, saving the data on commands run during the period in a timer. See TIMING in Chapter 7 and the Oracle installation and user's manuals provided for your operating system for more information.

 To delete all timers, enter CLEAR TIMING at the command prompt.

 

Running Host Operating System Commands

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:

 

SQL> HOST DIRECTORY *.SQL
When the host command finishes running, the SQL*Plus command prompt appears again.

 

Getting Help

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.

 

Listing a Table Definition

To see the definitions of each column in a given table, use the SQL*Plus DESCRIBE command.

 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                                    VARCHAR2(14)
LOC                                      VARCHAR2(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 Oracle8 Server SQL Reference Manual for details.

 

Listing PL/SQL Definitions

To see the definition of a function or procedure, use the SQL*Plus DESCRIBE command.

 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

Controlling the Display

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. Press [Pause]. (Refer to Table 2 - 1 to see how [Pause] is labelled on your keyboard.) 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 in Chapter 7.

 

Interpreting Error Messages

If SQL*Plus detects an error in a command, it will try to help you out by displaying an error message.

 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
ERROR:
ORA-04043: object DPT 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:

 


Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation. 
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index