SQL*Plus

Contents

• Introduction
• Formatting (including CSV)
• Set Options
• Substitution Variables
• Line Editor
• Dynamic SQL, and snippets
• Misc 
• Other 

Introduction

PROMPT a text

Display text

prompt =========================
PAUSE (Press ENTER to continue, ^C to stop, ^D in unix)

Display the text and wait for ENTER

DESC table-name/proc-name

Describes the table or procedure

xyz -
continuation

The continuation character is "-" (dash), even within strings

"..."
'...'

Enclose string with single or double quotes

HOST

Execute OS command

 

[HKEY_LOCAL_MACHINE\Software\ORACLE]
"ORACLE_HOME"="D:\ORANT"

[HKEY_LOCAL_MACHINE\Software\ORACLE\ALL_HOMES]
"HOME_COUNTER"="1"
"DEFAULT_HOME"="DEFAULT_HOME"
"LAST_HOME"="0"

[HKEY_LOCAL_MACHINE\Software\ORACLE\HOME0]
"ID"="0"
"ORACLE_HOME"="D:\ORANT"
"ORACLE_HOME_NAME"="DEFAULT_HOME"

PATH=C:\PROGRA~1\MSOFFICE\OFFICE;C:\WINNT;C:\ORANT\BIN;

[HKEY_LOCAL_MACHINE\Software\ORACLE]
"TNS_ADMIN"="D:\ORANT\network\admin"

Formatting

HEADING -->
      xxxx   xxxx  <-COLSEP->  xxxx  <- HEADSEP
      ------ ----------------- ----  <- UNDERLINE

      nnn    ttt   <-COLSEP->  aaa   <- NULL, NUMFORMAT, NUMWIDTH
      nnn    ttt               aaa   <- TRIMOUT, TRIMSPOOL

PAGESIZE, NEWPAGE, PAUSE-->
      xxxx   xxxx              xxxx
      ------ ----------------- ----
      nnn    ttt               aaa
      nnn    ttt ttt tt tttt t aaa
             ttt                     <- WRAP
                                     <- RECSEP, RECSEPCHAR
      nnn    ttt               aaa

      10 rows selected.              <- FEEDBACK

BREAK ON col_name SKIP n DUP ON col_name2 SKIP m DUP

Skip 1 line for every change in column col_name
DUP: show value on every line

COMPUTE { AVG | COUNT | MAX | MIN | NUMBER | STD | SUM | VAR }
[ LABEL label_text ]
OF column name
ON { group_column_name | ROW | REPORT }

Use with BREAK
Example: COMPUTE sum of mvt_amt on sort_col
BREAK ON REPORT
COMPUTE SUM OF ... ON REPORT --> grand total

CLEAR COLUMNS
CLEAR BREAKS

Remove column formats and break definitions

COLUMN col_name HEADING hhh FORMAT fff

Heading is text at top of column
Formats:
a5 --> 5 characters
999 ---> 3 digits
0 --> leading zero
S --> sign of number
PR --> negative numbers in brackets "<" and ">" (at end of number)
D --> decimal point
G --> group symbol
, . ---> comma or period
$ ---> floating $ sign
EEEE --> scientific notation (4 Es exactly at end of number)
RN --> roman numerals (integer between 1 and 3999)
(See also substitution variables)
hhh: HEADING "line1|line2" --> line break in heading (see SET HEADSEP)
For formatting dates, use "to_char"

COLUMN col_name

Display settings for col_name

COLUMN col_name CLEAR

Clear settings for col_name

CLEAR COLUMNS

Clear all column settings

SET COLSEP text

Column separator, default " "

SET HEADING [ ON | OFF ]

Show heading at top of page (column names). See NEWPAGE for the empty lines between pages.

SET HEADSEP [ ON | OFF | char ]

Char showing linebreak in header (default |)

SET UNDERLINE { ON | OFF | char]

Char under heading, default "-"

SET FEEDBACK [ ON|OFF| n ]

Show "no rows selected", or "6 rows".
n if more than n

SET NEWPAGE [ NONE | n ]

Number of lines between pages. It is also the number of lines at the top of the results
Set to NONE in dynamic queries

SET LINESIZE n

Width of line. Maximum is 32767 (unconfirmed)

SET LOBOFFSET

Offset

SET LONG n

 Number of char to display for column with long type. 80 is default.

SET TAB [ OFF | ON ]

 Turn off/on the replacement of whitespace with tabs. Personally, I like it off.

 

 

SET NEWPAGE { n | NONE }

n is number of empty lines between pages,
0 for FormFeed character
NONE for no lines and no FormFeed character (note that even with NONE, the heading will still show)

 

SET NUMFORMAT format_spec

Set the format for numbers

SET NUMWIDTH n

Default of 9 for number width

 

SET PAGESIZE n

Number of lines per page. The column headers are normally repeated at top of page. The maximum is 50000 (unconfirmed)
0 --> no headers, footers or column headings

 

 

SET PAUSE { ON | OFF | pause_msg }

Pause after each page. Press ENTER to continue.
N.B. Also press ENTER to see the first page

 

SET RECSEP { WRAPPED | EACH | OFF }

Print seperators between the records (lines) for lines with a wrapped column (default), for each line or none. The character is defined by SET RECSEPCHAR.

SET RECSEPCHAR "char"

Character to be printed in the record separator (see SET RECSEP). Default is space.

SET TRIMOUT [ ON | OFF ]

trim spaces on screen

SET TRIMSPOOL [ ON | OFF ]

trim spaces in output file (default is no, so put yes)

SHOW BTITLE
SHOW TTITLE

Show titles

SHOW lno
SHOW pno

Show line number, show page number

SHOW REPHEADER
SHOW REPFOOTER

Show report header/footer

SET WRAP [ ON | OFF ]

Wrap long lines

STORE SET _current_settings.sql REPLACE

Store all settings in the file _current_settings.sql

@_current_settings.sql

Restore all the settings in file _current_settings.sql

HTML markup:

SET MARKUP HTML ON HEAD "<title>SQL*PLUS output</title>" BODY "bgcolor=#FFFFFF" ENTMAP ON SPOOL ON PREFORMAT OFF
SPOOL GLU.HTML
-- whatever
SPOOL OFF
SET MARKUP HTML OFF
-- ENTMAP ON: Transforms the > and < into /gt; and &lt;
-- PREFORMAT ON: Display with <PRE>

Keep a log

define the_log_short    = &the_output_path.&the_file_name._short.log
define the_log_complete = &the_output_path.&the_file_name._complete.log
define the_log_temp     = &the_output_path.&the_file_name._temp.log

spool &the_log_temp.
prompt ==================================================================
...
spool off
host cat &the_log_temp >> &the_log_complete

spool &the_log_temp.
...
spool off
host cat &the_log_temp >> &the_log_complete

host echo "Summary of data .... " >> &the_log_short

-- clean up
host rm &the_log_temp

Example:

 
COLUMN  TODAY     NEW_VALUE    CURRENT_DATE   NOPRINT
COLUMN  TIME      NEW_VALUE    CURRENT_TIME   NOPRINT
COLUMN  DATABASE  NEW_VALUE    DATA_BASE      NOPRINT
COLUMN  PASSOUT   NEW_VALUE    DBNAME         NOPRINT
COLUMN passout2 new_value user noprint
REM
TTITLE LEFT "Date: " current_date -                     --> Note that variables are without the ampersand
       CENTER "Daily report" -   
       col 72 "Page:" format 999 SQL.PNO -              --> use right instead
       SKIP 1 -                                         --> Next line
       LEFT "Time: " current_time -
       CENTER "The report heading" -
       RIGHT format a15 SQL.USER -
       SKIP 1 -
       CENTER format a20 data_base -
       SKIP 2
REM
REM
SET heading off   --> turned on further down
SET pagesize 0    --> set to 58 further down
REM
SET termout off   --> turned on further down
SELECT TO_CHAR (SYSDATE, 'MM/DD/YY') today, TO_CHAR (SYSDATE, 'HH:MI AM') TIME,
          NAME
       || ' database' DATABASE, RTRIM (NAME) passout, USER passout2
  FROM v$database;
REM
SET termout on
SET heading on
SET pagesize 58
SET newpage 0
 

Comma-Separated Values (CSV)

set showmode off
set termout off feedback off verify off echo off newpage none heading on underline off pagesize 40000 trimspool on
set colsep ";" null ""
clear breaks


spool xyz.csv
-- ... Put query here
spool off

set termout on feedback 6 newpage 1 heading on underline "-" colsep " " null "*"

(Note: pagesize of 40'000 because Excel allows up to 36'000 and some
Note: pagesize of 0 removes an extra empty line at end of queries)

Minimal settings for new environment

set pagesize 2000
set linesize 120
Then format the columns with:column the_column_name format A20

Tab separated columns

column tab_char new_value tab_char
select chr(9) as tab_char from dual;
set colsep "&tab_char."

Display counts by year

column this_year new_value this_year
column last_year new_value last_year
column two_years_ago new_value two_years_ago
column over_two_years_ago new_value over_two_years_ago
select 'yr_' || to_char( sysdate , 'YYYY') as this_year
     , 'yr_' || to_char(add_months(sysdate, -12), 'YYYY') as last_year
     , 'yr_' || to_char(add_months(sysdate, -24), 'YYYY') as two_years_ago
     , 'yr_' || to_char(add_months(sysdate, -36), 'YYYY') || '_and_before' as over_two_years_ago
  from dual;

column &this_year format 999G999G999G990
column &last_year format 999G999G999G990
column &two_years_ago format 999G999G999G990
column &over_two_years_ago format 999G999G999G990

select sum(case when trunc(the_date_field, 'YY')=trunc(sysdate , 'YY') then 1 end) as &this_year
     , sum(case when trunc(the_date_field, 'YY')=trunc(add_months(sysdate, -12), 'YY') then 1 end) as &last_year
     , sum(case when trunc(the_date_field, 'YY')=trunc(add_months(sysdate, -24), 'YY') then 1 end) as &two_years_ago
     , sum(case when trunc(the_date_field, 'YY')<trunc(add_months(sysdate, -24), 'YY') then 1 end) as &over_two_years_ago
     , to_char(the_date_field, 'YYYY')
  from a_table
  group by to_char(the_date_field, 'YYYY')
  order by to_char(the_date_field, 'YYYY');

Note that column names must begin with an alphabetical character. I put "yr_" above.

Set Options

SHOW ALL

Show all the SQL*plus parameters

SHOW setting

Show one of the settings below

 SET COPYCOMMIT n

Number of times that commit is done. Default: 0

SET COPYTYPECHECK {ON | OFF}

Check the data types. Default is ON.

SET ECHO [ ON | OFF ]

The command is echoed in output before execution

SET SQLCONTINUE "> "

Continuation character for multiline SQL statements.

SET SQLNUMBER [ ON | OFF ]

Show number in multiline SQL statements. ON is recommended. If OFF, the normal prompt shows. See example below.

SET SQLPREFIX char

Set prefix char for entering SQL commands in the middle of SQL statement of PL/SQL statement (default #).

SET SQLPROMPT "&the_db > "

Define the prompt text; see also "set time". See example below.

SET TIME [ ON | OFF ]

Show time as part of prompt; see also "set sqlprompt". See example below.

SET SQLTERMINATOR [ ON | OFF | "char" ]

Define the character that terminates SQL statement and causes execution (default ";"). ON returns to ";". See example below.

SET SQLBLANKLINES [ ON | OFF ]

Allow empty lines in SQL statements. See example below.

SET BLOCKTERMINATOR

Character that terminates a PL/SQL block entry

SET SERVEROUTPUT { ON | OFF } [ SIZE n ] [FORMAT { WRAPPED | WORD_WRAPPED | TRUNCATED }]

Show server output from PL/SQL blocks

SET SHOWMODE [ ON | OFF | BOTH ]

When changing setting values, show value before and/or after

@filename

Execute filename; see "set suffix" for default suffix.

SET SUFFIX ".sql"

Set default suffix for command files (default ".sql")

SET TERMOUT [ ON | OFF ]

Output with command files (does not affect spooling of results)

SET TIMING [ ON | OFF ]

Show elapsed execution time after each block

SET TRUNCATE [ ON | OFF ]

Truncate long lines

 

SET SQLCONTINUE "> " ???

WHENEVER SQLERROR CONTINUE;
-- command whose eventual error should not stop the script, such as drop statement
WHENEVER SQLERROR EXIT 1;

Substitution Variables

SET DEFINE { ON | OFF | prefix_char }

set subst char (default: &) (previously: set scan)

SET ESCAPE { ON | OFF | escape_char }

set escape char (default: OFF) to use before subst char
Tip: set off before giving a new value. Surround with double quotes, but not necessary

SET VERIFY { ON | OFF }

Repeat the line before execution to show value of substitution variables

SET CONCAT { ON | OFF | concat_char }

Char that marks the end of subst var (default: ".")

DEFINE var_name = a_value

Assign value to variable

UNDEFINE var_name

Remove variable

DEFINE var_name

Show value stored in variable

DEFINE

Show all values stored in variables

DEFINE var_name = &1

Define variable with value of first parameter passed to the script
Add double quotes around parameters with spaces in BOTH the calling command AND the script:
Calling: @a_script.sql "parameter with spaces"
Script: define var_name = "&1"

ACCEPT var_name [ NUMBER | CHAR | DATE ]
   [ FORMAT format_spec ]
   [ DEFAULT default_value ]
   [ PROMPT text | NOPROMPT ]
   [ HIDE ]

Prompt user for value. Use HIDE for passwords

UNDEFINE var_name var_name2

 

COLUMN current_acctg_date NEW_VALUE the_bal_date OLD_VALUE prev_date

new_value allows the variable to take the value of the result of the query.
Example:
set heading off termout off
COLUMN acctg_date NEW_VALUE the_bal_date NOPRINT
SELECT to_char(current_accounting_date, 'DD/MM/YYYY' ) as acctg_date FROM parameters;
prompt The current accounting date is &the_bal_date
set heading on termout on

DEFINE the_file_name = glu
SPOOL &glu..txt
xxx
SPOOL OFF

The results are in &glu..txt

BIND

print, set autoprint, variable

Example:

DEFINE the_id = put-a-value-here
ACCEPT the_id CHAR DEFAULT &the_id PROMPT "Enter the ID (default &the_id ): "
select * from the_table where id = &the_id ;

DEFINE the_user = whoever
DEFINE local_db = PRD1
ACCEPT pw char prompt "Enter password for &the_user on database &local_db.: " HIDE
connect &the_user/&pw@&local_db

DEFINE like_object = &1
ACCEPT like_object CHAR DEFAULT &like_object PROMPT "Enter object name (default &like_object.). Add wildcard % as necessary: "
select owner, object_name, object_type
  from all_objects
  where object_name like UPPER('&like_object.')
  order by object_name, object_type, owner;

Line Editor

DELACIL (Lpos LAST) --> DEL  A  C  I  L  ( + L for positioning + last)

a text

Append text (2 spaces to add space)

c /old/new/

change old to new (use another char instead of / too)

c /old

remove old

del b *

delete line b or current

del

delete current line

 

del last

delete last line

del 2 {3 | * | last}

delete from line 2 to line 3 or current or last

I text

input text as new line after current line *

I

start new lines after current line

L {b | last}

line b or last becomes current

L {b | *} [{e | * | last}]

list b or * to e or * or last

Dynamic SQL

Bold highlights the part that has to be customized

Dynamic Query  

• the_file_name (here "xyz")
• Enter the queries: end each query with / on new line, instead of ';'

Yes / No, default No

Create a command file

Fill in the command file name (here: "whatever") and the command (here: "fc xyz_orig.txt xyz_new.txt")

Commit / rollback

Timestamp on Spool File

Calling a Procedure and Looking at a Cursor

-- Assuming this procedure:
CREATE OR REPLACE PROCEDURE myproc (
  a IN VARCHAR2,
  b IN VARCHAR2,
  c IN OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN c FOR SELECT * ...;
  DBMS_OUTPUT.PUT_LINE('string out length: '||to_char(length(the_message)));
END;
/
 

--In SQL*plus:

-- if I have DBMS_OUTPUT.PUT_LINE('...') in the procedure:
set serveroutput on
set serveroutput size 50000


 Declare a variable to hold the cursor
VAR mycur REFCURSOR;

-- Execute the procedure
EXEC myproc('val1', 'val2', :mycur);

-- Show the values
PRINT mycur;

-- Extract value from the cursor, assuming only one field is in the cursor
-- using a pl/sql block to get the values from the cursor
var one_value number;
DECLARE    
BEGIN
  FETCH :result_set INTO :one_value;
END;
/
-- End with ; then add / for immediate execution
print one_value;
-- Use this further down in a pl/sql block as an argument to another procedure with notation ":one_value"

-- Put in a sqlplus variable (did not try yet)
column one_val new_value one_val
select :one_value as one_val from dual;
prompt The value is &one_val

 

Miscellaneous

SHOW ERRORS { function_name | procedure_name | package_name | ... }

Show errors

SHOW ERRORS [owner.]object

Show errors for object

SHOW PARAMETERS [parameter-name]

Show one or all database parameters

SHOW RELEASE

Show release number of database

SHOW SGA

Current state of System Global Area

SHOW SPOOL

Show state of spooling to file

SHOW SQLCODE

?

SHOW USER

Show current user

PASSWORD [user]

Change password

 

COPY FROM username/password@sid
   [ TO username/password@sid] (not necessary if already connected here)
     { APPEND | CREATE | INSERT | REPLACE }
     destination_table [(column_list)]
     USING select_statement

Example (in target database):
COPY FROM sch/pw@sid APPEND table_name_in_target USING SELECT * from table_in_source where ...;

APPEND

inserts the data, creates table if necessary

CREATE

creates the table, no copy if table exists

INSERT

copies only if table exists

REPLACE

delete and create destination table   

Oracle client installer sets language with regional settings values. Use REGEDIT.
Key name: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0
Name: NLS_LANG
Value: AMERICAN_AMERICA.WE8ISO8859P1

Differences between Windows and UNIX

Other

See a file helpus.sql in the orant sub-directory tree

Sources:

• Luers: PL/SQL in 21 days, p. 29
• Gennick: Oracle SQL*Plus