Note: Commands to be entered within an SQL*Plus session are indicated with SQL>
Created Monday 10/12/2001
SQL*Plus is the Oracle interactive sql interpreter. It's installed with Oracle Client and also with Oracle Lite and is typically located in $ORACLE_HOME/bin/sqlplus. This document describes some simple features of SQL*Plus. The command prompt SQL> refers to an interactive session in the SQL*Plus command interpreter.
Note: Commands to be entered within an SQL*Plus session are indicated with SQL>
When SQL*Plus starts, and after a CONNECT is performed, the command interpreter reads and executes commands from the site profile followed by the local user profile, both of which may contain parameter settings and may also contain sql (both DDL and DML). The pathnames for these files are:
2. Display current settings
Most SQL*Plus settings are controlled with the SET keyword. The SHOW command is used to display the value of a particular setting, with the all argument used to display the current values of all settings.
SQL> show arraysize arraysize 15 SQL> SQL> set arraysize 20 SQL> SQL> show all appinfo is OFF and set to "SQL*Plus" arraysize 20 autocommit OFF ...
In addition to SHOW/SET, SQL*Plus uses DEFINE to set values for things like which editor to use and what format to display dates in. Use DEFINE by itself to show current definitions:
SQL> define _EDITOR="vim" SQL> define DEFINE _DATE = "05-JUN-06" (CHAR) DEFINE _USER = "FOO" (CHAR) DEFINE _EDITOR = "vim" (CHAR) ...
The output of result sets in SQL*Plus is pretty rudimentry by default but can be made a bit more readable by setting the page size, the linesize and formatting a few columns. The following sections discuss how some of the SQL*Plus variables can be used to control result set formatting.
The page size controlls how may rows appear on a single page. Each page starts with a page header and the columns of the projection (i.e., a select statement). Set page size to 0 to turn paging off.
SQL> set pages 20
Tip: Set the page size to 0 to turn off pagination
The linesize variable controls how many characters of a result set row will be written before a line break character is written and the line wrapps. The linesize can be set to pretty much any value, but that means that columns with long definitions but small data values will display strangely (e.g., "foo" in a column definition of varchar(256) will display paddd with 253 spaces).
SQL> set linesize 130
Formatting can be applied to individual columns and is useful for displaying column data with large definitions, e.g., varchar(256). A column format statement is applied to a column by name and the default case of a column is unformatted. Column formatting is pretty easy using the designator A for ascii (for char, varchar and varchar2) data and 9 for integer, 9.99 for floating point (doubles) and so on. As an example, consider three columns "somestrings" declared to be varchar2, "someints" as number(38) and "somefloats" as float(126). Unformatted rows from a such a table may appear rather messy. If we know that most values in somestrings is 10 characters or less, then we may choose to limit the column size to 10, which we do by specifying A10. Tuples that have values longer than 10 characters for this column will appear over multiple rows. The following session best illustrates this:
bash $ sqlplus foo/foo@flywheel
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 14 17:10:57 2008
SQL>
SQL> create table sometable (somestrings varchar2(256), someints number(38), somefloats float(126));
Table created.
SQL>
SQL> insert into sometable values ('10 or less', 123456789, 123456789.123456789);
SQL> insert into sometable values ('small', 123456789, 123456789.123456789);
SQL> insert into sometable values ('There is more than 10 characters here', 123456789, 123456789.123456789);
3 row(s) created.
SQL>
SQL> column somestrings format A10 <-----+
SQL> column someints format 999,999,999 <-----| ** Column formatting **
SQL> column somefloats format 999,999,999.99 <-----+
SQL>
SQL> select * from sometable;
SOMESTRING SOMEINTS SOMEFLOATS
---------- ------------ ---------------
10 or less 123,456,789 123,456,789.12 <-- This is row #1
small 123,456,789 123,456,789.12 <-- Row #2
There is m 123,456,789 123,456,789.12 <--+
ore than 1 | This is all row
0 characte | number #3
rs here ---+
The releveant comments here are use of the column statement to format a column. Use A for ascii 9 for integer and 9.9 for floating point. With numeric formatting, inclusion of the comma (,) is used for grouping. Consider: SQL> column somestrings format A10 SQL> column someints format 999,999,999 SQL> column otherints format 999999999 SQL> column somefloats format 999,999,999.99
Tip: Use comma in numeric format to include thousands grouping, e.g, 9,999.99
The SQL*Plus HOST command is used to issue commands to the operating system. The arguments to HOST can be pretty much any valid O/S command including forking a subshell.
SQL> host cat /proc/cmdline ro root=/dev/VolGroup00/LogVol00 rhgb quiet
SQL> host bash [foo@locknut oracle]$
The initialization parameter values can be viewed with the SQL*Plus show parameter command. The output columns for show parameter are NAME_COL_PLUS_SHOW_PARAM, VALUE_COL_PLUS_SHOW_PARAM and TYPE. The data type for the first two of these columns are varchar2 and the content are quite long so it's best to limit the column output to about 35 characters with about 150 characters per line. This can be done by setting the linesize and using the column command:
SQL> set linesize 150 SQL> column NAME_COL_PLUS_SHOW_PARAM format a35 SQL> column VALUE_COL_PLUS_SHOW_PARAM format a35
To view all initialization parameters:
SQL> show parameters
The show parameter command takes an optional argument, which Oracle uses to perform a case insensitive partial match. For example, to show the values for all initialization parameters that include the string "database":
SQL> show parameter database NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM -------------------------- ------- ------------------------- cluster_database boolean FALSE cluster_database_instances integer 1
Each user in oracle is assigned a Schema ID. The user and schema are tightly coupled in oracle and the two are indistinguishable (unlike other DBMS's like Sybase and Postgres in which the user id and schema are separate). The Schema ID for the currently logged in user can be determined from the user environment, like so:
SQL> select userenv('SCHEMAID') from dual;
5
Stuart Moorfoot © 10 December 2001 foo@bund.com.au