Total Pageviews

Thursday, March 22, 2012

SQLPlus KB

Get a list of these SET options in sql*plus with the command:

SQLPLUS> HELP SET

ARRAY[SIZE] {15|n}

Fetch size (1 to 5000) the number of rows that will be retrieved in one go.

AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}

Autocommit commits after each SQL command or PL/SQL block

AUTOP[RINT] {OFF|ON}

Automatic PRINTing of bind variables.(see PRINT)

AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

Display a trace report for SELECT, INSERT, UPDATE or DELETE statements

EXPLAIN shows the query execution path by performing an EXPLAIN PLAN.

STATISTICS displays SQL statement statistics.

Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS

BLO[CKTERMINATOR] {.|c|OFF|ON}

Set the non-alphanumeric character used to end PL/SQL blocks to c

CMDS[EP] {;|c|OFF|ON}

Change or enable command separator - default is a semicolon (;)

COLSEP { |text}

The text to be printed between SELECTed columns normally a space.

ECHO {OFF|ON}

Display commands as they are executed

EMB[EDDED] {OFF|ON}

OFF = report printing will start at the top of a new page.

ON = report printing may begin anywhere on a page.

ESC[APE] {\|c|OFF|ON}

Defines the escape character. OFF undefines. ON enables.

FEED[BACK] {6|n|OFF|ON}

Display the number of records returned (when rows >= n )

OFF (or n=0) will turn the display off

FLU[SH] {OFF|ON}

Buffer display output (OS)

(no longer used in Oracle 9)

HEA[DING] {OFF|ON}

print column headings

HEADS[EP] {||c|OFF|ON}

Define the heading separator character (used to divide a column heading onto > one line.)

OFF will actually print the heading separator char

see also: COLUMN command

LIN[ESIZE] {150|n}

Width of a line (before wrapping to the next line)

Earlier versions default to 80, Oracle 9 is 150

MARK[UP] HTML [ON|OFF]

[HEAD text] [BODY text] [TABLE text]

[ENTMAP {ON|OFF}][SPOOL {ON|OFF}]

[PRE[FORMAT] {ON|OFF}]

Output HTML text, which is the output used by iSQL*Plus.

NEWP[AGE] {1|n}

The number of blank lines between the top of each page and the top title.

0 = a form feed between pages.

NULL text

Replace a null value with 'text'

The NULL clause of the COLUMN command will override this for a given column.

NUMF[ORMAT] format

The default number format.

see COLUMN FORMAT.

NUM[WIDTH] {10|n}

The default width for displaying numbers.

PAGES[IZE] {14|n}

The height of the page - number of lines.

0 will suppress all headings, page breaks, titles

PAU[SE] {OFF|ON|text}

press [Return] after each page

enclose 'text' in single quotes

SERVEROUT[PUT] {OFF|ON} [SIZE n] [FOR[MAT] {WRA[PPED] WOR[D_WRAPPED] TRU[NCATED]}]

whether to display the output of stored procedures (or PL/SQL blocks)

i.e., DBMS_OUTPUT.PUT_LINE

SIZE = buffer size (2000-1,000,000) bytes

SHOW[MODE] {OFF|ON}

Display old and new settings of a system variable

SQLBL[ANKLINES] {ON|OFF}

Allow blank lines within an SQL command. reverts to OFF after the curent command/block.

SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}

Convert the case of SQL commands and PL/SQL blocks

(but not the SQL buffer itself)

SQLCO[NTINUE] {> text}

Continuation prompt (used when a command is continued on an additional line using a hyphen -)

SQLN[UMBER] {OFF|ON}

Set the prompt for the second and subsequent lines of a command or PL/SQL block.

ON = set the SQL prompt = the line number.

OFF = set the SQL prompt = SQLPROMPT.

SQLP[ROMPT] {SQL>|text}

Set the command prompt.

SQLT[ERMINATOR] {;|c|OFF|ON}

Set the char used to end and execute SQL commands to c.

OFF disables the command terminator - use an empty line instead.

ON resets the terminator to the default semicolon (;).

SUF[FIX] {SQL|text}

Default file extension for SQL scripts

TAB {OFF|ON}

Format white space in terminal output.

OFF = use spaces to format white space.

ON = use the TAB char.

Note this does not apply to spooled output files.

The default is system-dependent. Enter SHOW TAB to see the default value.

TERM[OUT] {OFF|ON}

OFF suppresses the display of output from a command file

ON displays the output.

TERMOUT OFF does not affect the output from commands entered interactively.

TI[ME] {OFF|ON}

Display the time at the command prompt.

TIMI[NG] {OFF|ON}

ON = display timing statistics for each SQL command or PL/SQL block run.

OFF = suppress timing statistics

TRIM[OUT] {OFF|ON}

Display trailing blanks at the end of each line.

ON = remove blanks, improving performance

OFF = display blanks.

This does not affect spooled output.

SQL*Plus ignores TRIMOUT ON unless you set TAB ON.

TRIMS[POOL] {ON|OFF}

Allows trailing blanks at the end of each spooled line.

This does not affect terminal output.

UND[ERLINE] {-|c|ON|OFF}

Set the char used to underline column headings to c.

VER[IFY] {OFF|ON}

ON = list the text of a command before and after replacing substitution variables with values.

OFF = dont display the command.

WRA[P] {OFF|ON}

Controls whether to truncate or wrap the display of long lines.

OFF = truncate

ON = wrap to the next line

The COLUMN command (WRAPPED and TRUNCATED clause) can override this for specific columns.

Script example

/*

Multiple line comments

Can go between these delimiters

*/

SET TERM OFF

-- TERM = ON will display on terminal screen (OFF = show in LOG only)

SET ECHO ON

-- ECHO = ON will Display the command on screen (+ spool)

-- ECHO = OFF will Display the command on screen but not in spool files.

-- Interactive commands are always echoed to screen/spool.

SET TRIMOUT ON

-- TRIMOUT = ON will remove trailing spaces from output

SET TRIMSPOOL ON

-- TRIMSPOOL = ON will remove trailing spaces from spooled output

SET HEADING OFF

-- HEADING = OFF will hide column headings

SET FEEDBACK OFF

-- FEEDBACK = ON will count rows returned

SET PAUSE OFF

-- PAUSE = ON .. press return at end of each page

SET PAGESIZE 0

-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)

SET LINESIZE 80 (use 10000 pref)


-- LINESIZE = width of page (80 is typical)

SET VERIFY OFF

-- VERIFY = ON will show before and after substitution variables

-- Start spooling to a log file

SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--

SELECT * FROM GLOBAL_NAME;

SPOOL OFF

SQL*Plus commands

The following commands can be issued in SQL*Plus (in addition to the standard SQL commands.)

@pathname Run an SQL Script (START)

@MyScript.sql parameter1 parameter2 parameter3

In the SQL-Script, refer to the parameters as &1, &2, and &3.

@@pathname Run a nested SQL Script.

@variable A substitution variable

@@variable A substitution variable valid for the session

/ Execute (or re-execute) commands in the SQL*Plus buffer

does not list commands before running

ACCEPT User input

ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FORMAT format]

[DEFAULT default] [PROMPT text|NOPROMPT] [HIDE]

APPEND Add text to the end of the current line in the buffer.

A[PPEND] text_to_add

BREAK Specify where and how formatting will change.

BREAK ON {column|expr|ROW|REPORT} action

BTITLE Place and format a title at the bottom of each page.

BTITLE printspec [text|variable]

BTITLE [OFF|ON]

CHANGE Change text on the current line.

C /oldval/newval

CLEAR Clear the SQL*Plus screen and the screen buffer.

CLEAR {BREAKS|BUFFER|COLUMNS|COMPUTES|SCREEN|SQL TIMING}

COLUMN Change display width of a column.

COMPUTE Calculate and display totals.

CONNECT Connect to a database as a specified user.

connect username/password@SID

COPY Copy data from a query into a table (local or remote)

DEFINE User variables:

DEFINE varName = String

Display a user variable

DEFINE varName

Display all variables

DEFINE

DEL Delete the current line in the SQL buffer

DESC[RIBE] Describe a table, column, view, synonym, function

procedure, package or package contents.

DISCONNECT Logoff (but don't exit)

EDIT Load the SQL*Plus buffer into an editor.

By default, saves the file to AFIEDT.BUF

EXECUTE Run a single PLSQL statement

EXEC :answer := EMP_PAY.BONUS('SMITH')

EXIT [n] Commit, logoff and exit (n = error code)

EXIT SQL.SQLCODE

GET file Retrieve a previously stored command file

HELP topic Topic is an SQL PLUS command or HELP COMMANDS

HOST Execute a host operating system command

HOST CD scripts

INPUT Edit sql buffer - add line(s) to the buffer

LIST n m Edit sql buffer - display buffer lines n to m

For all lines - specify m as LAST

PAUSE message Wait for the user to hit RETURN

PRINT variable List the value of a bind variable or REF Cursor (see VARIABLE / SHOW)

PROMPT message Echo a message to the screen

REMARK REMARK comment or --comment-- or /* comment */

RUN Execute (or re-execute) commands in the SQL*Plus buffer

Lists the commands before running

RUNFORM Run a SQL*Forms application

SAVE file Save the contents of the SQL*Plus buffer in a command file

SAVE file [CRE[ATE] | REP[LACE] | APP[END]]

SET Display or change SQL*Plus settings

SHOW List the value of a system variable (see PRINT)

SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL]

SPOOL file Store query results in file

SPOOL OFF Turn off spooling

SPOOL OUT sends file to printer

SQLPLUS Start SQL*Plus and connect to a database.

STA[RT] Run an SQL Script (see @)

STARTUP [NOMOUNT|MOUNT|OPEN]

TIMING Record timing data TIMING {START |SHOW |STOP}

see CLEAR TIMING

TTITLE Define a page title

UNDEFINE Delete a user/substitution variable UNDEFINE varName (see DEFINE)

VARIABLE Define a bind variable (Can be used in both SQLPlus and PL/SQL)

VAR[IABLE] [variable {NUMBER|CHAR|CHAR(n)|REFCURSOR}]

A RefCursor bind variable can be used to reference PL/SQL cursor variables in stored procedures.

PRINT myRefCursor

EXECUTE somePackage.someProcedure(:myRefCursor)

VARIABLE on its own will display the definitions made.

WHENEVER OSERROR Exit if an OS error occurs

WHENEVER SQLERROR Exit if an SQL or PLSQL error occurs

SQL*Plus Prompt:

To display the currently connected UserName and SID, instead of just SQL>;

SET sqlprompt '>_user:&_connect_identifier >; '

Add the line above to the file: $ORACLE_SID/sqlplus/admin/glogin.sql (this tip requires Oracle 10g or greater)

Editing SQL scripts in SQL*Plus

To run your operating system's default text editor from the SQL*Plus command-line type Edit:

SQL > EDIT

The variable _EDITOR, may be used to setup a preferred text editor, for example, define vi as the SQL*Plus editor:

DEFINE _EDITOR = vi

or define Metapad.exe as the SQL*Plus editor:

DEFINE _EDITOR = Metapad.exe

Include an editor definition like the above in your user profile (login.sql) or site profile ($ORACLE_HOME/sqlplus/admin/glogin.sql) so that it is always enabled when you start SQL*Plus.

To create a new script with a text editor, enter EDIT followed by the name of the new file:

SQL > EDIT myscript.sql

The new file will be created in the current directory (wherever you started SQL*Plus)

When you save the script with the text editor, it is saved back into the same file.

You must include a semicolon at the end of each SQL command and a slash (/) on a line by itself after each PL/SQL block in the file.

You can include multiple SQL commands and PL/SQL blocks in a script.

In addition to external editors it is also possible to use the (rather limited) set of editing commands within SQL*Plus itself.

A text Append text at the end of the current line

C/old/new Change old to new in the current line

C/text Delete text from the current line

DEL Delete the current line

L List all lines in the SQL buffer

CLEAR BUFFER Delete all lines

Example
set markup html on
set pages 50000
set serveroutput off
set feedback off
set echo off
set trimspool off

No comments:

Post a Comment