=======================
command line options
=======================
sqlplus -? - Help for command line options
sqlplus -v - Return version for sqlplus and database
for example:
SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
sqlplus -S - Silent mode. This would suppress the SQL*Plus banner, prompts,
and echoing of commands.
sqlplus -L - Attempts to log on just once.
sqlplus -prelim - Opens session in Preliminary mode.
Sometimes, because the threashold of sessions is reached,
a DBA cannot login to the database. Even to perform a SHUTDOWN.
In this case, the solution would be to use the -prelim option.
When opening a session in Preliminary mode, it will not try to create
private session structures in the SGA.
This allows you to connect to perform debugging or shutdown
operations.
sqlplus -R <level>
- Restrict mode.This would disable SQL*Plus commands that interact with the file system.
The level can be 1, 2 or 3.
The most restrictive is -R 3.
R3 which disables all user commands interacting with the file system.
sqlplus -M "<options>"
- Markup mode.
Would generate output in HTML format.
Sets automatic HTML markup of output.
The options have the form:
HTML [ON|OFF]
[HEAD text]
[BODY text]
[TABLE text]
[ENTMAP {ON|OFF}]
[SPOOL {ON|OFF}]
[PRE[FORMAT] {ON|OFF}]
sqlplus /nolog
Login without connecting to any schema.
=======================
Connect as sysdba
=======================
From privileged account:
sqlplus / as sysdba
or
sqlplus /nolog
connect as sysdba
In Oracle 9, one must use ""
sqlplus "/as sysdba"
=======================connect as sysdba
In Oracle 9, one must use ""
sqlplus "/as sysdba"
Display options
=======================
SET COLSEP , - Separate columns with a comma
SET ESCAPE \ - This allows to enter special characters into database as strings.
For example 'A\&B' would be parsed to string 'A&B', rather than
to variable substitution.
SET DEFINE OFF - Do not substitute variables with using &&
SET ECHO OFF - Do not print the sql command
SET FEEDBACK OFF - Do not display "5 rows selected"
SET HEADING OFF - Do not display Heading line
SET HEADSEP OFF - This may or may not be useful...depends on your headings.
SET LINESIZE X - X should be the sum of the column widths
SET FEEDBACK OFF - Do not display "5 rows selected"
SET HEADING OFF - Do not display Heading line
SET HEADSEP OFF - This may or may not be useful...depends on your headings.
SET LINESIZE X - X should be the sum of the column widths
SET LONG X - X number of displayed characters for LONG column
SET LONGCHUNKSIZE X - Controls the number of characters retrieved at one time from a
LONG column.
The Default is 80.
With this default, the one long string coming from CLOB -
will be split into several 80 chars lines. each line is
a separate iteration to sever.
LONGCHUNKSIZE value should be equal to LONG.
LONGCHUNKSIZE value should be equal to LONG.
SET NEWPAGE NONE - Get rid on first empty line in spool file.
SET NUMW X - X should be the length you want for numbers.
SET PAGESIZE 0 - No header rows
SET SHOW OFF - Do not display old: value new: value messages for SET changes.
SET SERVEROUTPUT ON-Enable messaged from DBMS_OUTPUT to be printed to console.
SET SQLBLANKLINES ON - Ignore blank lines.
This is useful when dealing with multi line commands,
and not using '_' as "line continuation".
SET TERMOUT OFF - Do not display output generated by commands in script.
SET TRIMSPOOL ON - Trim the trailing blanks from file.
SET TRIMOUT ON - Trim the trailing blanks on screen.
SET TIMING ON - Display the elapsed time
SET VERIFY OFF - Do not display old: value new: value messages
for variable substitution.SET UNDERLINE OFF - Do not underline heading
NLS
Format date with NLS
ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MMDD HH24:MI:SS';
COLUMN MY_COLUMN FORMAT A300 WORD_WRAP
COLUMN MY_COLUMN FORMAT A300 WORD_WRAP
WORD_WRAP - Wraps each line to the length specified by LINESIZE.
That way words are not cut between lines.
NEW_VALUE - An option to define, and assign value to the defined variable.
Usage of NEW_VALUE Example:
That way words are not cut between lines.
NEW_VALUE - An option to define, and assign value to the defined variable.
Usage of NEW_VALUE Example:
COL report_name_col NEW_VALUE report_name NOPRINT
SELECT 'MY_FILE_'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')||'.csv' report_name_col FROM DUAL;
=======================
Using variables
=======================
Option A. Static Variables
SET SHOW OFF
SET VERIFY OFF
DEFINE report_name = SELECT 'AIRCEL_HSC_REP_295_'||'201512'||'.csv' FROM DUAL;
SPOOL &report_name
Option B. Dynamic Variables
SET SHOW OFF
SET VERIFY OFF
COL report_name_col NEW_VALUE report_name NOPRINT
SELECT 'AIRCEL_HSC_REP_295_'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')||'.csv'
SPOOL &report_name
=======================
SELECT 'MY_FILE_'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')||'.csv' report_name_col FROM DUAL;
SELECT &report_name FROM DUAL
SPOOL &report_name
Using variables
=======================
Option A. Static Variables
SET SHOW OFF
SET VERIFY OFF
DEFINE report_name = SELECT 'AIRCEL_HSC_REP_295_'||'201512'||'.csv' FROM DUAL;
SPOOL &report_name
Option B. Dynamic Variables
SET SHOW OFF
SET VERIFY OFF
COL report_name_col NEW_VALUE report_name NOPRINT
SELECT 'AIRCEL_HSC_REP_295_'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')||'.csv'
SPOOL &report_name
=========================
How to get rig of trailing blanks in output file
=========================
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 4000
This will set output line to 4000, and the trailing lines in generated file will be trimmed.
=======================
Format Display options=======================
COL COL_A NOPRINT - Suppress display of a column
=======================
Additional options
=======================Generating HTML Reports from SQL*Plus Oracle Referenceexec DBMS_LOCK.SLEEP(120); - Sleep in sqlplusREM - Remark line=======================HTML Display options=======================SET MARKUP HTML ON SPOOL ON - Would produce output in HTML format.
i.e. HTML pages automatically encapsulated with
<HTML> and <BODY> tags.
PREFORMAT OFF
ENTMAP ON
=======================spool options=======================SPOOL my_fileSPOOL my_file appendError: SP2-0333: Illegal spool file name: (bad character: ' ')SPOOL my_file append return an error.Reason: append mode is available from sqlplus version 10 and higher.Example:SQL> spool bbb append SP2-0333: Illegal spool file name: "bbb append" (bad character: ' ')SQL> select * from V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for 32-bit Windows: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production SQL> exit >sqlplus -version SQL*Plus: Release 9.2.0.1.0 - ProductionAs a workaround, use the OS append option
TYPE file2 >> file1
less file2 >> file1=======================Example, spool to a file without spool to sqlplus window.=======================
Create new file my_sql.sql
SET FEEDBACK OFFSET HEADING OFFSET NEWPAGE NONESET SHOW OFFSET VERIFY OFFSET TERMOUT OFFSET TRIMSPOOL ONSET PAGESIZE 0SET LINESIZE 600
SET COLSEP "," SPOOL my_output.txt SELECT col_1||' , '||col_2||' , '||col_3 FROM MY_TABLE;SPOOL OFFRun the sqlplussqlplus -s my_user/my_pass@orainst @my_sql.sql=======================IF in sqlplus=======================-- Activate Variables Substitution
SET DEFINE ON -- Suppress echo for substitution variables SET VERIFY OFF -- activate STDOUT SET SERVEROUTPUT ON
SET FEEDBACK OFF
COL INSTALL_AUTO_UPD NEW_VAL INSTALL_AUTO_UPD_VAL COL Auto_Activate_GSM_NET FOR A22 DEFINE Auto_Activate_GSM_NET=0
PROMPT Running with Auto_Activate_GSM_NET set to Value &&Auto_Activate_GSM_NET SET TERMOUT OFF
SELECT DECODE (&&Auto_Activate_GSM_NET,1,'cre_moco_util_objects.sql','dummy_install.sql')
AS INSTALL_AUTO_UPD
FROM DUAL;
SET TERMOUT ON
PROMPT PROMPT ======================================= PROMPT CREATE OR REPLACE PACKAGE MOCO_UTIL PROMPT ======================================= @@ &INSTALL_AUTO_UPD_VAL PROMPT PROMPT ======================================= PROMPT Done PROMPT ======================================= PROMPT SET DEFINE OFF SPOOL OFF=======================Example, split a file to 10 files, and append commit at the end=======================Example: Split input file aa.sql to several files, each one 3000 Lines. And append commit at the end of each onesplit -l 3000 -e --additional-suffix=.sql aa.sql find . -type f -print0 | while IFS= read -r -d $'\0' file; do echo "$file" | echo "commit;" >> "$file" ; done=======================Reference=======================SQL*Plus Command Reference in docs.oracle.com
Nice Blog: Simulating Control Flow in sqlplus
