Previous | Table of Contents | Next

Page 127

To execute a SQL command or a PL/SQL block from the buffer, SQL*Plus provides the RUN command and the / (forward slash) command. The syntax of the RUN command is

R[UN]

The RUN command lists and executes the SQL command or PL/SQL block currently stored in the buffer.

Let's assume that the buffer contains this query:

SELECT empno, ename FROM emp

If you ran the query using the RUN command, it would look like Listing 8.8.

Listing 8.8 Running a Query with the RUN Command

SQL> RUN
     1* SELECT empno, ename FROM emp
EMPNO      ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
14 rows selected.

RUN displays the command from the buffer and returns the results of the query. In addition, RUN makes the last line in the buffer the current line.

The / command is similar to the RUN command. It executes the SQL command or PL/SQL block stored in the buffer, but it does not display the contents of the buffer, as shown in
Listing 8.9.

Listing 8.9 Running a Query with the / Command

SQL> /
EMPNO      ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD


Page 128

Listing 8.9 Continued

      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
14 rows selected.

NOTE
Unlike the RUN command, the / command does not make the last line in the buffer the current line.

To run a SQL command, a SQL*Plus command, or a PL/SQL block from a command line, there are two commands:

The syntax of the START command is

START file_name[.ext] [arg1 arg2...]

The file_name[.ext] represents the command file you wish to run. If you omit the extension, SQL*Plus assumes the default command-file extension (usually .sql).

SQL*Plus searches in the current directory for a file with the filename and extension that you specify in your START command. If no such file is found, SQL*Plus will search the directory or directories specified in the SQLPATH environment variable for the file. You could also include the full pathname for the file, for example: C:\MYDYR\MYFILE.SQL.

You can include any SQL command, SQL*Plus command, or PL/SQL block that you would normally enter interactively into a command file. An EXIT or QUIT command used in a command file exits SQL*Plus.

The arguments section ([arg1 arg2...]) represents values you want to pass to parameters in the command file. The parameters in the command file must be specified in the following format: &1, &2, ...(or &&1, &&2, ...). If you enter one or more arguments, SQL*Plus substitutes the values into the parameters in the command file. The first argument replaces each occurrence of &1, the second replaces each occurrence of &2, and so on.

Page 129

The START command defines the parameters with the values of the arguments. If you run the command file again in the same SQL*Plus session, you can enter new arguments or omit the arguments to use the current values of the parameters. To run a command file named DELTBL.SQL, you enter the following:

SQL> START DELTBL

The @ ("at") command functions very much like the START command. The only difference is that the @ command can be run both from inside a SQL*Plus session and at the command-line level when starting SQL*Plus, whereas a START command can be run only from within a SQL*Plus session. To start a SQL*Plus session and execute the commands from a command file, enter

$ sqlplus [username/password] @file_name[.ext] [arg1 arg2...]

If the START command is disabled, this will also disable the @ command. The section "Restricting a User's Privileges in SQL*Plus," later in this chapter, contains more information on disabling SQL*Plus commands.

Using the EXECUTE CommandStarting with SQL*Plus 3.2, there is a new command, EXECUTE, that enables the execution of a single PL/SQL statement directly at the SQL*Plus prompt, rather than from the buffer or a command file. EXECUTE's main usage is for running a PL/SQL statement that references a function or a stored procedure, as shown in Listing 8.10.

Listing 8.10 Use EXECUTE with Stored Procedures

SQL> VARIABLE id NUMBER          -- Define a bind variable
SQL> EXECUTE :id := ADD_CASES(10);
PL/SQL procedure successfully completed.
SQL> PRINT id
       ID
--------
       10
SQL> EXECUTE :id := ADD_CASES(3);
PL/SQL procedure successfully completed.
SQL> PRINT id
       ID
--------
        0

The value returned by the stored procedure ADD_CASES is stored in the bind variable :id.

Saving SQL*Plus/SQL CommandsYou can save the SQL*Plus or SQL command stored in the buffer in an operating system file (called command file), using the SAVE command. The syntax of the SAVE command is

SAV[E] file_name[.ext] [CRE[ATE] | [REP[LACE] | APP[END]]

Page 130

The file_name[.ext] is the name of the operating system file where you want to store the contents of the SQL buffer. To name the file, use the file-naming conventions of the operating system where SQL*Plus is running. If you do not provide an extension for the file, SQL*Plus uses the default extension, .sql. You could also specify a path as part of the file name. If you do not specify a path, the SAVE command will use the directory named in the SQLPATH environment variable as the path for the file.

The CRE[ATE] parameter creates the file. If the file already exists, you will receive an error message.

The REP[LACE] parameter replaces an existing file with the contents of the SQL buffer. If the file does not exist, SAVE...REPLACE creates it.

The APP[END] parameter appends the contents of the SQL buffer at the end of the file. If the file does not exist, SAVE...APPEND creates it.

Retrieving SQL*Plus/SQL CommandsTo retrieve SQL*Plus or SQL commands, use the GET command. The syntax of the command is

GET file_name[.ext] [LIS[T] | NOL[IST]]

The GET command loads an operating system file—file_name—that contains SQL*Plus or SQL commands, into the SQL buffer, so that you can edit the commands in the file or run them. The default extension for the file is .sql.

The LIS[T] parameter lists the contents of the file as SQL*Plus loads the file in the buffer. LIST is the default. The NOL[IST] parameter does not list the contents of the file.

If you do not specify the full path for the filename in the GET command, SQL*Plus searches for it first in the current directory, then in the directories listed in the environment variable SQLPATH.

TIP
If you use Windows 95/NT, you can have SQL*Plus look in a specific directory for your command files by starting the application in that directory. To do this, change the Windows shortcut Start In property to the directory that contains the files. Files that you open, create, or save without specifying
a directory path will be opened, created, or saved in the directory in which you start SQL*Plus.

Using the SQL*Plus COPY Command

Although its effectiveness has been somewhat reduced when Oracle introduced the CREATE TABLE...UNRECOVERABLE AS SELECT... in version 7.2, the COPY command is still one of the most useful SQL*Plus commands; yet it is not understood very well and therefore is not used very often. The COPY command can be used for several functions:

Previous | Table of Contents | Next