Welcome, Guest

Using SQL*Plus in Oracle

Options

Previous Topic

Next Topic

@2016-02-22 14:54:12

SQL*Plus Command Summary

Command	Description
@ ("at" sign)	Runs the SQL*PLus statements in the specified script. The script can be called from the local file system or from a web server.
@@ (double "at" sign)	Runs a script. This command is similar to the @ ("at" sign) command. It is useful for running nested scripts because it looks for the specified script in the same path as the script from which it was called.
/ (slash)	Executes the SQL command or PL/SQL block.
ACCEPT	Reads a line of input and stores it in a given user variable.
APPEND	Adds specified text to the end of the current line in the buffer.
ARCHIVE LOG	Starts or stops the automatic archiving of online redo log files, manually (explicitly) archives specified redo log files, or displays information about redo log files.
ATTRIBUTE	Specifies display characteristics for a given attribute of an Object Type column, and lists the current display characteristics for a single attribute or all attributes.
BREAK	Specifies where and how formatting will change in a report, or lists the current break definition.
BTITLE	Places and formats a specified title at the bottom of each report page, or lists the current BTITLE definition.
CHANGE	Changes text on the current line in the buffer.
CLEAR	Resets or erases the current clause or setting for the specified option, such as BREAKS or COLUMNS.
COLUMN	Specifies display characteristics for a given column, or lists the current display characteristics for a single column or for all columns.
COMPUTE	Calculates and prints summary lines, using various standard computations, on subsets of selected rows, or lists all COMPUTE definitions.
CONNECT	Connects a given user to Oracle.
COPY	Copies results from a query to a table in a local or remote database.
DEFINE	Specifies a user variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables.
DEL	Deletes one or more lines of the buffer.
DESCRIBE	Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function or procedure.
DISCONNECT	Commits pending changes to the database and logs the current user off Oracle, but does not exit SQL*Plus.
EDIT	Invokes a host operating system text editor on the contents of the specified file or on the contents of the buffer.
EXECUTE	Executes a single PL/SQL statement.
EXIT	Terminates SQL*Plus and returns control to the operating system.
GET	Loads a host operating system file into the SQL buffer.
HELP	Accesses the SQL*Plus help system.
HOST	Executes a host operating system command without leaving SQL*Plus.
INPUT	Adds one or more new lines after the current line in the buffer.
LIST	Lists one or more lines of the SQL buffer.
PASSWORD	Allows a password to be changed without echoing the password on an input device.
PAUSE	Displays the specified text, then waits for the user to press [Return].
PRINT	Displays the current value of a bind variable.
PROMPT	Sends the specified message to the user's screen.
QUIT	Terminates SQL*Plus and returns control to the operating system. QUIT is identical to EXIT.
RECOVER	Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database.
REMARK	Begins a comment in a script.
REPFOOTER	Places and formats a specified report footer at the bottom of each report, or lists the current REPFOOTER definition.
REPHEADER	Places and formats a specified report header at the top of each report, or lists the current REPHEADER definition.
RUN	Lists and executes the SQL command or PL/SQL block currently stored in the SQL buffer.
SAVE	Saves the contents of the SQL buffer in a host operating system file (a script).
SET	Sets a system variable to alter the SQL*Plus environment for your current session.
SHOW	Shows the value of a SQLPlus system variable or the current SQLPlus environment.
SHUTDOWN	Shuts down a currently running Oracle instance.
SPOOL	Stores query results in an operating system file and, optionally, sends the file to a printer.
START	Executes the contents of the specified script. The script can only be called from a url.
STARTUP	Starts an Oracle instance and optionally mounts and opens a database.
STORE	Saves attributes of the current SQL*Plus environment in a host operating system file (a script).
TIMING	Records timing data for an elapsed period of time, lists the current timer's title and timing data, or lists the number of active timers.
TTITLE	Places and formats a specified title at the top of each report page, or lists the current TTITLE definition.
UNDEFINE	Deletes one or more user variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).
VARIABLE	Declares a bind variable that can be referenced in PL/SQL.
WHENEVER OSERROR	Performs the specified action Exits SQL*Plus if an operating system command generates an error.
WHENEVER SQLERROR	Performs the specified action Exits SQL*Plus if a SQL command or PL/SQL block generates an error.

@2016-02-22 16:24:10

Using Substitution Variables

Substitution variables are of two types, command line and embedded.

Command Line Substitution Variable

A command line substitution variable is of the form &n where "n" is an integer in the range of 1-8. These are used to allow specifying arguments on the SQLPLUS command line at login. For example, the line from the code is:

select * from dba_tables where owner=upper('&1');

The &1 would be replaced by the first argument following the userid and program name on the command line. If the procedure name is TABLE_REP then the command would be:

SQLPLUS SYSTEM/MANAGER @TABLE_REP SCOTT

and "SCOTT" would be substituted for &1 in the code.

Embedded Variable

An embedded variable is either prompted for using the PROMPT....ACCEPT command or is automatically prompted for when encountered in your code. Embedded variables are usually named variables. For example, the line from the code is:

select * from dba_tables where owner=upper('&table_owner');

When the code is executed the SQLPLUS engine will prompt you for:

Enter value for table_owner:

and whatever you enter will be substituted for the variable. If the substitution has to be made several times you can either use the PROMPT...ACCEPT and a single ampersand on each incidence of the variable or use double ampersands on each incidence of the variable.

@2016-02-23 10:16:38

Using Spool Command

The spool command in Oracle is used within SQL*Plus to direct the output of any query to a server-side flat file.

Once spool is set, SQL*Plus will continue to spool the output until the command SPOOL OFF.

The spool command is very useful when you want to generate SQL from the data dictionary. Here is an example of a SQL*Plus spool command:

spool 'c:\data\myfile.txt'

select table_name from dba_tables where table_name like 'CLAIM%';

spool off;

Do not forget to include the semicolon at the end of the SQL statement.

How to add SYSDATE to a spool file name?

To write dates into spool comands, for example, say that you want to run a SQL*Plus script where it spools to a file name that contains SYSDATE, you can select SYSDATE into a variable name using the DUAL pseudo-table, and execute the name as part of the spool command:

column col_outfilename noprint new_value outfilename;

select 'c:\data\myfile-' || to_char(sysdate, 'yymmdd') || '.txt' col_outfilename from dual;

spool '&outfilename'

select table_name from dba_tables where table_name like 'CLAIM%';

spool off

@2016-02-23 15:11:29

PROMPT Command

The PROMPT command in SQL*Plus outputs a line of data to the screen.

PROMPT

PROMPT Please enter a valid department

PROMPT For example:  10, 20, 30, 40

SELECT DEPARTMENT_NAME FROM EMP_DETAILS_VIEW

WHERE DEPARTMENT_ID = &NEWDEPT

You can enter a department number at the prompt Department ID?>. By default, SQL*Plus lists the line containing &NEWDEPT before and after substitution, and then displays the department name corresponding to the number entered at the Department ID?> prompt.

Please enter a valid department

For example:  10, 20, 30, 40

Department ID?>

You can use SET VERIFY OFF to prevent this behavior.

Previous Topic

Next Topic

1 2

Comments

You must Sign In to comment on this topic.

Print this topic

Watch this topic

Normal

Threaded