SQL> select count(*) from dba_indexes where owner='&OWN' New 1: select count(*) from dba_tables where owner='PHYSICS' Old 1: select count(*) from dba_tables where owner='&OWN'

SQL> select count(*) from dba_tables where owner='&OWN' When you define a variable with &, however, SQL*Plus discards the variable and its value immediately after use, so that repeated use of &variablename results in repeated prompts for the value of variablename. The first difference you'll notice between & and & is that when SQL*Plus encounters a variable defined with &, it prompts you for the value and then uses this value for every subsequent occurrence of that variable it encounters.

New 1: select owner from dba_tables where table_name='DUAL' Old 1: select owner from dba_tables where table_name='&FRED' SQL> select owner from dba_tables where table_name='&FRED' For example, in select &foo from dual, &foo refers to the variable foo. Everything from the & to the following space is considered the variable name.If variablename is undefined, then SQL*Plus prompts the user for a value. &variablename is used to refer to the variable variablename in SQL*Plus, much the same way $variablename is used in shell scripts.& and & indicate substitution variables in SQL*Plus scripts or commands.Just about everyone has defined a variable in SQL*Plus using &. This lets you use user-defined and database values not just in subsequent queries, but also in calls to other scripts and SQL*Plus's other functionality. You can change which identifiers should be used as the prefix, suffix and part delimiter in a variable expression in Tools->Tool Properties, in the General / VariablesВ category.Everyone knows the basic features of sql*plus, but one underused feature that can make your scripts an order of magnitude more useful is the ability to store and reuse values, including values read from the database, in variables. Variables in DbVisualizer may be used anywhere in a statement as long as there is no data type specified. Note that none of the pre-defined variables below will show in the prompt window. These are replaced with actual values just before the SQL is sent to the DB server. scope=post The scope must be specified as post when using variables representing columns when exporting BLOB/CLOB values toВ separate files named based on column values.ДЄ few pre-defined DbVisualizer variables can be used anywhere in the SQL.A variable assigned the return value for a function must be declared as dir=out, and a variable used for a procedure parameter must use aВ dirВ type matching the procedure parameter direction declaration. dir=in | out | inout The direction for a variable used with the command (it is ignored for other uses).nobind Used in combination with when a type is set and defines that the variable should be replaced as a literal in the SQL rather than being bound as a parameter marker.A value must be set when using this option, unless it is an output variable (see dirВ below) noshow This option define that the variable should not appear in the prompt window.A symbol indicate this condition in the prompt window where Defines that the variable is part of the WHERE clause.Represented with a symbol in the prompt window pk Indicates that the variable is part of the primary key in the final SQL.The options part is used to express certain conditions. If no type is specified, it is treated as a literal This is used to determine how the data should be passed between DbVisualizer and the database server. In addition DbVisualizer defines: BinaryData and TextData (for CLOB). The type of variable: String, Boolean, Integer, Float, Long, Double, BigDecimal, Date, Time and Timestamp. If multiple variables in a script have the same name, the substitution dialogВ shows only one and the entered value will be applied to all variables with that name This is the name that appear in the prompt window.

Sql prompt input