SQL*Plus Substitution Variables

Christopher Jones Updated February 2004

 Contents 

1 Introduction

2 Using Substitution Variables
    2.1 Creating, Showing and Deleting Substitution Variables
    2.2 Referencing Substitution Variables
    2.3 Prompting for Undefined Variables
    2.4 Difference Between "&" and "&&" Prefixes
    2.5 Storing a Query Column Value in a Substitution Variable
    2.6 Predefined Substitution Variables
    2.7 Script Parameters
    2.8 More on Substitution Variables

3 Using Bind Variables
    3.1 Assigning Substitution Variables to Bind Variables
    3.2 Assigning Bind Variables to Substitution Variables

4 Using System Variables
    4.1 System Variables Influencing Substitution Variables
        4.1.1 SET CONCAT
        4.1.2 SET DEFINE
        4.1.3 SET ESCAPE
        4.1.4 SET NULL
        4.1.5 SET NUMFORMAT
        4.1.6 SET NUMWIDTH
        4.1.7 SET SQLPROMPT
        4.1.8 SET VERIFY
    4.2 System Variables in Titles and EXIT

5 SQL*Plus Substitution Variable Commands
    5.1 ACCEPT Command
    5.2 COLUMN Command
    5.3 DEFINE Command
    5.4 EDIT Command
    5.5 EXIT Command
    5.6 HOST Command
    5.7 TTITLE, BTITLE, REPHEADER and REPFOOTER Commands
        5.7.1 Using "&" Prefixes With Title Variables
        5.7.2 Variables and Text Spacing in Titles
    5.8 UNDEFINE Command
    5.9 WHENEVER Command

6 Substitution Variables Namespace, Types, Formats and Limits
    6.1 Substitution Variable Namespace
    6.2 Substitution Variable Types
    6.3 Substitution Variable Formats
    6.4 Substitution Variable Limits

7 iSQL*Plus and Substitution Variables
    7.1 iSQL*Plus 9i and SQL*Plus Substitution Variable Compatibility
    7.2 iSQL*Plus Parameters

8 Substitution Variable Summary

9 Substitution Variable Examples
    9.1 Setting a Substitution Variable's Value
    9.2 Using a Substitution Variable
    9.3 Finding All Defined Substitution Variables
    9.4 Inserting Data Containing "&" Without Being Prompted
    9.5 Putting the Current Date in a Spool File Name
    9.6 Appending Alphanumeric Characters Immediately After a Substitution Variable
    9.7 Putting a Period After a Substitution Variable
    9.8 Using a Fixed Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER
    9.9 Using a Changing Value Variable in a TTITLE, BTITLE, REPHEADER or REPFOOTER
    9.10 Using the Value of a Bind Variable in a SQL*Plus Command Like SPOOL
    9.11 Passing Parameters to SQL*Plus Substitution Variables
    9.12 Passing Operating System Variables to SQL*Plus
    9.13 Passing a Value to a PL/SQL Procedure From the Command Line
    9.14 Allowing Script Parameters to be Optional and Have a Default Value
    9.15 Passing a Value to an iSQL*Plus Dynamic Report for the Web
    9.16 Customizing Parameter Prompts for an iSQL*Plus Dynamic Report for the Web
    9.17 Using a Variable for the SQL*Plus Return Status
    9.18 Putting the Username and Database in the Prompt

 

출처 : http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var.html