11.1.2 Command Summary

Table of Contents Previous Next


11 Utilities : 11.1 EDB*Plus : 11.1.2 Command Summary

11.1.2.1 ACCEPT
The ACCEPT command displays a prompt and waits for the user’s keyboard input. The value input by the user is placed in the specified variable.
ACC[EPT ] variable
The following example creates a new variable named my_name, accepts a value of John Smith, then displays the value using the DEFINE command.
11.1.2.2 APPEND
APPEND is a line editor command that appends the given text to the end of the current line in the SQL buffer.
In the following example, a SELECT command is built-in the SQL buffer using the APPEND command. Note that two spaces are placed between the APPEND command and the WHERE clause in order to separate dept and WHERE by one space in the SQL buffer.
11.1.2.3 CHANGE
CHANGE is a line editor command performs a search-and-replace on the current line in the SQL buffer.
C[HANGE ] /from/[to/ ]
If to/ is specified, the first occurrence of text from in the current line is changed to text to. If to/ is omitted, the first occurrence of text from in the current line is deleted.
11.1.2.4 CLEAR
The CLEAR command removes the contents of the SQL buffer, deletes all column definitions set with the COLUMN command, or clears the screen.
Clears the SQL buffer.
11.1.2.5 COLUMN
The COLUMN command controls output formatting. The formatting attributes set by using the COLUMN command remain in effect only for the duration of the current session.
If the COLUMN command is specified with no subsequent options, formatting options for current columns in effect for the session are displayed.
If the COLUMN command is followed by a column name, then the column name may be followed by one of the following:
2.
3.
Any combination of FORMAT, HEADING, and one of OFF or ON
Name of a column in a table to which subsequent column formatting options are to apply. If no other options follow column, then the current column formatting options if any, of column are displayed.
The CLEAR option reverts all formatting options back to their defaults for column. If the CLEAR option is specified, it must be the only option specified.
Format specification to be applied to column. For character columns, spec takes the following format:
n is a positive integer that specifies the column width in characters within which to display the data. Data in excess of n will wrap around with the specified column width.
For numeric columns, spec is comprised of the following elements.
If OFF is specified, formatting options are reverted back to their defaults, but are still available within the session. If ON is specified, the formatting options specified by previous COLUMN commands for column within the session are re-activated.
11.1.2.6 CONNECT
CON[NECT] username[/password][@{connectstring | variable } ]
username is a database username with which to connect to the database.
password is the password associated with the specified username. If a password is not provided, but a password is required for authentication, EDB*Plus will prompt for the password.
connectstring is the database connection string.
variable is a variable defined in the login.sql file that contains a database connection string. The login.sql file can be found in the edbplus subdirectory of the Advanced Server home directory.
In the following example, the database connection is changed to database edb on the localhost at port 5445 with username, smith.
From within the session shown above, the connection is changed to username enterprisedb. Also note that the host defaults to the localhost, the port defaults to 5444 (which is not the same as the port previously used), and the database defaults to edb.
11.1.2.7 DEFINE
The DEFINE command creates or replaces the value of a user variable (also called a substitution variable).
DEF[INE ] [ variable [ = text ] ]
If the DEFINE command is given without any parameters, all current variables and their values are displayed.
If DEFINE variable is given, only variable is displayed with its value.
DEFINE variable = text assigns text to variable. text may be optionally enclosed within single or double quotation marks. Quotation marks must be used if text contains space characters.
Note: The variable EDB is read from the login.sql file located in the edbplus subdirectory of the Advanced Server home directory.
11.1.2.8 DEL
DEL is a line editor command that deletes one or more lines from the SQL buffer.
DEL [ n | n m | n * | n L[AST ] | * | * n | * L[AST ] |
n is an integer representing the nth line
n m
n and m are integers where m is greater than n representing the nth through the mth lines
In the following example, the fifth and sixth lines containing columns sal and comm, respectively, are deleted from the SELECT command in the SQL buffer.
11.1.2.9 DESCRIBE
The DESCRIBE command displays:
The DESCRIBE command will also display the structure of the database object referred to by a synonym. The syntax is:
DESC[RIBE] [ schema.]object
11.1.2.10 DISCONNECT
The DISCONNECT command closes the current database connection, but does not terminate EDB*Plus.
11.1.2.11 EDIT
The EDIT command invokes an external editor to edit the contents of an operating system file or the SQL buffer.
ED[IT ] [ filename[.ext ] ]
filename[.ext ]
filename is the name of the file to open with an external editor. ext is the filename extension. If the filename extension is sql, then the .sql extension may be omitted when specifying filename. EDIT always assumes a .sql extension on filenames that are specified with no extension. If the filename parameter is omitted from the EDIT command, the contents of the SQL buffer are brought into the editor.
11.1.2.12 EXECUTE
The EXECUTE command executes an SPL procedure from EDB*Plus.
EXEC[UTE ] spl_procedure [ ([ parameters ]) ]
11.1.2.13 EXIT
The EXIT command terminates the EDB*Plus session and returns control to the operating system. QUIT is a synonym for EXIT. Specifying no parameters is equivalent to EXIT SUCCESS COMMIT.
[ COMMIT | ROLLBACK ]SUCCESS | FAILURE |WARNING
A variable created with the DEFINE command whose value is returned as the return code.
If COMMIT is specified, uncommitted updates are committed upon exit. If ROLLBACK is specified, uncommitted updates are rolled back upon exit. The default is COMMIT.
11.1.2.14 GET
The GET command loads the contents of the given file to the SQL buffer.
GET filename[.ext ] [ LIS[T ] | NOL[IST ] ]
filename[.ext ]
filename is the name of the file to load into the SQL buffer. ext is the filename extension. If the filename extension is sql, then the .sql extension may be omitted when specifying filename. GET always assumes a .sql extension on filenames that are specified with no extension.
If LIST is specified, the content of the SQL buffer is displayed after the file is loaded. If NOLIST is specified, no listing is displayed. The default is LIST.
11.1.2.15 HELP
The HELP command obtains an index of topics or help on a specific topic. The question mark (?) is synonymous with specifying HELP.
The name of a specific topic – e.g., an EDB*Plus command, for which help is desired.
11.1.2.16 HOST
The HOST command executes an operating system command from EDB*Plus.
11.1.2.17 INPUT
The INPUT line editor command adds a line of text to the SQL buffer after the current line.
The following sequence of INPUT commands constructs a SELECT command.
11.1.2.18 LIST
LIST is a line editor command that displays the contents of the SQL buffer.
L[IST] [ n | n m | n * | n L[AST] | * | * n | * L[AST] | L[AST] ]
n represents the buffer line number.
n m displays a list of lines between n and m.
n * displays a list of lines that range between line n and the current line.
n L[AST] displays a list of lines that range from line n through the last line in the buffer.
* displays the current line.
* n displays a list of lines that range from the current line through line n.
* L[AST] displays a list of lines that range from the current line through the last line.
L[AST] displays the last line.
11.1.2.19 PASSWORD
Use the PASSWORD command to change your database password.
PASSW[ORD] [user_name]
You must have sufficient privileges to use the PASSWORD command to change another user's password. The following example demonstrates using the PASSWORD command to change the password for a user named acctg:
11.1.2.20 PAUSE
The PAUSE command displays a message, and waits for the user to press ENTER.
PAU[SE] [optional_text]
optional_text specifies the text that will be displayed to the user. If the optional_text is omitted, Advanced Server will display two blank lines. If you double quote the optional_text string, the quotes will be included in the output.
11.1.2.21 PRINT
The PRINT command displays the value of a bind variable.
PRI[NT] [bind_variable_name]
bind_variable_name specifies the name of a bind variable. Omit bind_variable_name to generate a list that includes the values of all bind variables.
11.1.2.22 PROMPT
The PROMPT command displays a message to the user before continuing.
PRO[MPT] [message_text]
message_text specifies the text displayed to the user. Double quote the string to include quotes in the output.
11.1.2.23 QUIT
The QUIT command terminates the session and returns control to the operating system. QUIT is a synonym for EXIT.
[SUCCESS | FAILURE | WARNING | value | sub_variable]
The default value is QUIT SUCCESS COMMIT.
11.1.2.24 REMARK
Use REMARK to include comments in a script.
REM[ARK] [optional_text]
11.1.2.25 SAVE
Use the SAVE command to write the SQL Buffer to an operating system file.
SAV[E] file_name
[CRE[ATE] | REP[LACE] | APP[END]]
file_name specifies the name of the file (including the path) where the buffer contents are written. If you do not provide a file extension, .sql is appended to the end of the file name.
Include the CREATE keyword to create a new file. A new file is created only if a file with the specified name does not already exist. This is the default.
Include the REPLACE keyword to specify that Advanced Server should overwrite an existing file.
Include the APPEND keyword to specify that Advanced Server should append the contents of the SQL buffer to the end of the specified file.
11.1.2.26 SET
Use the SET command to specify a value for a session level variable that controls EDB*Plus behavior. The following forms of the SET command are valid:
Use the SET AUTOCOMMIT command to specify commit behavior for Advanced Server transactions.
{ON | OFF | IMMEDIATE | statement_count}
Specify ON to turn autocommit behavior on.
Specify OFF to turn autocommit behavior off.
IMMEDIATE has the same effect as ON.
Include a value for statement_count to instruct EDB*Plus to issue a commit after the specified count of successful SQL statements.
Use the SET COLUMN SEPARATOR command to specify the text that Advanced Server displays between columns.
SET COLSEP column_separator
The default value of column_separator is a single space.
Use the SET ECHO command to specify if SQL and EDB*Plus script statements should be displayed onscreen as they are executed.
The SET FEEDBACK command controls the display of interactive information after a SQL statement executes.
Specify an integer value for row_threshold. Setting row_threshold to 0 is same as setting FEEDBACK to OFF. Setting row_threshold equal 1 effectively sets FEEDBACK to ON.
Use the SET FLUSH command to control display buffering.
Set FLUSH to OFF to enable display buffering. If you enable buffering, messages bound for the screen may not appear until the script completes. Please note that setting FLUSH to OFF will offer better performance.
Set FLUSH to ON to disable display buffering. If you disable buffering, messages bound for the screen appear immediately.
Use the SET HEADING variable to specify if Advanced Server should display column headings for SELECT statements.
The SET HEADSEP command sets the new heading separator character used by the COLUMN HEADING command. The default is '|'.
Use the SET LINESIZE command to specify the width of a line in characters.
SET LIN[ESIZE] width_of_line
The default value of width_of_line is 132.
Use the SET NEWPAGE command to specify how many blank lines are printed after a page break.
SET NEWP[AGE] lines_per_page
The default value of lines_per_page is 1.
Use the SET NULL command to specify a string that is displayed to the user when a NULL column value is displayed in the output buffer.
SET NULL null_string
Use the SET PAGESIZE command to specify the number of printed lines that fit on a page.
SET PAGES[IZE] line_count
Use the line_count parameter to specify the number of lines per page.
The SET SQLCASE command specifies if SQL statements transmitted to the server should be converted to upper or lower case.
Specify UPPER to convert the command text to uppercase.
Specify LOWER to convert the command text to lowercase.
Specify MIXED to leave the case of SQL commands unchanged. The default is MIXED.
The SET PAUSE command is most useful when included in a script; the command displays a prompt and waits for the user to press Return.
If SET PAUSE is ON, the message Hit ENTER to continue… will be displayed before each command is executed.
Use the SET SPACE command to specify the number of spaces to display between columns:
SET SPACE number_of_spaces
Use SET SQLPROMPT to set a value for a user-interactive prompt:
By default, SQLPROMPT is set to "SQL> "
Use the SQL TERMOUT command to specify if command output should be displayed onscreen.
The SQL TIMING command specifies if Advanced Server should display the execution time for each SQL statement after it is executed.
11.1.2.27 SHOW
Use the SHOW command to display current parameter values.
SHO[W] {ALL | parameter_name}
11.1.2.28 SPOOL
The SPOOL command sends output from the display to a file.
SP[OOL] output_file | OFF
Use the output_file parameter to specify a path name for the output file.
11.1.2.29 START
Use the START command to run an EDB*Plus script file; START is an alias for @ command.
STA[RT] script_file
11.1.2.30 UNDEFINE
The UNDEFINE command erases a user variable created by the DEFINE command.
UNDEF[INE] variable_name [ variable_name...]
Use the variable_name parameter to specify the name of a variable or variables.
The WHENEVER SQLERROR command provides error handling for SQL errors or PL/SQL block errors. The syntax is:
WHENEVER SQLERROR
{CONTINUE
[COMMIT|ROLLBACK|NONE]
|EXIT
[SUCCESS|FAILURE|WARNING|n|sub_variable]
[COMMIT|ROLLBACK]}
Include the CONTINUE clause to instruct EDB*Plus to perform the specified action before continuing.
Include the COMMIT clause to instruct EDB*Plus to COMMIT the current transaction before exiting or continuing.
Include the ROLLBACK clause to instruct EDB*Plus to ROLLBACK the current transaction before exiting or continuing.
Include the NONE clause to instruct EDB*Plus to continue without committing or rolling back the transaction.
Include the EXIT clause to instruct EDB*Plus to perform the specified action and exit if it encounters an error.

11 Utilities : 11.1 EDB*Plus : 11.1.2 Command Summary

Table of Contents Previous Next