Chapter 4: List of SQLS*Plus Commands

Command

Usage

Description

  variable
Name of the variable If variable does not exist, SQLS*Plus creates it.

  DEF[AULT]
Use set default value if a reply is not provided.

  PROMPT text
Skip a line and display text before accepting the variable value.

  NOPR[OMPT]
Skip a line and wait for an input without prompt.

  HIDE
Suppress the display of the typed characters.

ON REPORT specifies that break related action (SKIP lines or calculate COMP values) will be done at the
end of the report

ON ROW executes SKIP action (if specified) after every row

DUP|NODUP

NODUP prints blanks for duplicate values in break column

DUP prints the value of a break column in every row

SKIP skips specified number of lines or whole page on break

BREAK is needed for COMP statements to work

COMP expects to have BREAK columns defined for ON part of the COMP statement

BTI[TLE] [printspec [text | variable] …] | [ON | OFF]

Syntax

where printspec represents one or more of the following clauses used to place and format the text:

CE[NTER]

LE[FT]

S[KIP] [n]

COL [n]

TAB [n]

Enter BTITLE with no clauses to list the current BTITLE definition.

Options

See the TTITLE command for information on terms and clauses in the BTITLE command syntax.

Examples

BTITLE LEFT ‘REPORT ‘ RIGHT ‘PAGE:’
SQL.PNO

See the TTITLE command syntax for information on terms and clauses in the R[IGHT] BTITLE command syntax.

<table/view name”

Cat <table/view name>

Selects first 3000 rows Options: -a – select all rows

COMP[UTES] – clears all defined computes

SCR[EEN] – clear screen

ON|OFF – turn off/on column format attributes

PRINT|NOPRINT – show or hide column from the query output

 COMP[UTE] [function [LAB[EL] text]  … OF {column } … ON {column}

Supported functions:

COMP with no parameters lists all current COMPUTE  definitions.

LABEL Defines the label for the computed function value

OF {column}

OF defines columns that is used to calculate value summary of function

ON {column}

ON defines columns that is used to group values summary functions around.

Corresponding BREAK ON columns must exists for ON clause in COMP to work

If multiple
COMPUTE commands use the same ON column, only the last ON column would

 connect user/password@ip[\\instance_name][:db_name]

Connect creates a database session. Multiple sessions can exists at the same time

Instance name and database names are optional

Instead of db_name it is possible to use below environmental variables to set up default database for connection:

a) SQLSDBNAME

b) SQLCMDDBNAM E (standard sqlcmd variable)

-l list defines

Below are the pre-defined variables

_CONNECT_IDENTIFIER Connection identifier used to make connection.

_CONNECT_DATABASE Database used to make connection, where available.

_EDITOR
Editor used by the EDIT command.

_S_VERSION
Version of the connected SQL Server Database.

_S_LEVEL
Level of the connected SQL Server Database.

_USER
User name used to make connection.

-r list dependencies recursively

“detail” option provides detailed describe information

Default editor is “notepad”

Environment variable SQLSEDIT can be used to set custom editor

-v show rows that don’t match pattern -I ignore case

Search pattern across all table columns

-c print table count at the end -n 1000 chunk size
-i 1000 max iterations -q be quiet

You can specify an additional “where” clause:

purge Table where id=23

Purge executes a series (-i) of delete statements, where each statement deletes (-n) rows at a time and commits.

maxsize default is 40 characters sample default is 10% of table size

Maxsize defines maximum column size for long character columns

Sample defines what percent of the table data to scan to create optimal format definitions

N – when number of selected records is over N, number of records returned will be shown

Table of Contents
Type chapter title (level 1) – 1 Type chapter title (level 2) – 2 Type chapter title (level 3) – 3
Type chapter title (level 1) – 4 Type chapter title (level 2) – 5 Type chapter title (level 3) – 6
output line size

If newpage is set to 0, form feed character is printed at the beginning of each page

  HTML [ON|OFF]

HTML is a mandatory argument which specifies that HTML output is to be generated.

HTML arguments, ON and OFF, specify whether or not to generate HTML output. The default is OFF.

  HEAD text

The HEAD text option enables to specify content for the <HEAD> tag. By default, text includes a default in-line CSS and title. If text includes spaces, it must be enclosed in quotes.

  BODY text

The BODY text option enables to specify attributes for the <BODY> tag. By default, there are no attributes. If text includes spaces, it must be enclosed in quotes.

  TABLE text

The TABLE text option enables to enter attributes for the <TABLE> tag. By default, the <TABLE> WIDTH attribute is set to 90% and the BORDER attribute is set to 1. If text includes spaces, it must be enclosed in quotes.

  ENTMAP {ON|OFF}

ENTMAP ON or OFF specifies whether or not SQL*Plus replaces special characters <, >, ” and & with the HTML entities &lt;, &gt;, &quot; and &amp; respectively. ENTMAP is set ON by default.

  SPOOL {ON|OFF}

SPOOL ON or OFF specifies whether or not SQLS*Plus writes the HTML opening tags, <HTML> and <BODY>, and the closing tags, </BODY> and </HTML>, to the start and end of each file created by the SQLS*Plus SPOOL filename command. The default is OFF.

Header and footer tags enabled by the SET MARKUP HTML SPOOL ON option are not written to the spool file until “SPOOL filename” command is not issued

  PRE[FORMAT] {ON|OFF}

PREFORMAT ON or OFF specifies whether or not SQLS*Plus writes output to the <PRE> tag or to an HTML table. The default is OFF, so output is written to a HTML table by default.

Supported CSV and JSON format outputs all fields surrounded by double quotes

TERMOUT OFF does not affect output from commands entered interactively or directed to SQLS*Plus from the OS.

APPEND – add output to the file

CREATE – would not allow to overwrite existing file

REPLACE (default) – replace existing file

OFF – stop spooling

TTI[TLE] [printspec [text | variable] …] [ON | OFF]

where printspec represents one or more of the following clauses used to place and format the text:

CE[NTER]

LE[FT]

R[IGHT]

S[KIP] [n]

COL [n]

TAB [n]

Options:

These options also apply to the BTITLE command.

text

The ttitle text. Enter text in single quotes if you want to place more than one word on a single line.

variable

A substitution variable or any of the following system-
maintained values, SQL.LNO (the current report line
number), SQL.PNO (the current report page number) ,
SQL.SYSDATE (the current report timestamp),
SQL.USER (the current connected user)

To print one of these values, reference the appropriate variable in the title. You can format variable with the FORMAT clause.

SQLS*Plus substitution variables (& variables) are
expanded before TTITLE is executed. The resulting
string is stored as the TTITLE text.

OFF

Turns the ttitle off (suppresses its display) without affecting its definition.

ON

Turns the title on (restores its display). When you
define a top title, SQLS*Plus automatically sets TTITLE
to ON.

S[KIP] [n], n>=1

Skips to the start of a new line n times; if you omit n,
one time;

COL [N]

Moves to the line column n. N can be negative.

TAB [n]

Moves forward n columns (line columns, not database
table columns) or backwards if n is a negative number

LE[FT] | CE[NTER] | R[IGHT]

Left-align, center, and right-align data on the current
line respectively. CENTER and RIGHT use current
LINESIZE value to calculate the relative position of the
data items

TTITLE with no clauses lists current TTITLE definition.

Examples

To define “Monthly Report” as the top title and to left-
align it, to center the current date, to right-align the
page number, and to display “Data in Millions” in the
center of the next line, enter

TTITLE LEFT 'Monthly Report' CENTER
SQL.SYSDATE
RIGHT 'Page:' SQL.PNO SKIP CENTER
'Data in Millions'

Enter TTITLE with no clauses to list its current definition.