SQL*Plus - The Oracle CLI

@path/to/script.sql

• executes the specified script, with path/to/script.sql being :
  • a filesystem path
  • a HTTP / FTP url (but not HTTPS)
• the official doc is slightly puzzling when it comes to directories (current / default ?) and paths. Just remember that this construct runs a script (source) :
  • either found in the current directory
  • or specified with an absolute or relative path
  • or found in your ORACLE_PATH or SQLPATH

@@path/to/script.sql

• pretty similar to @
• the main difference is when scripts include other scripts :
  • let's consider mainScript.sql :

    SELECT DEPARTMENT_ID, CITY FROM EMP_DETAILS_VIEW WHERE SALARY>12000;
    @subScript1.sql
    @@ subScript2.sql

  • during its execution, when reaching the @, it looks for subScript1.sql in the current working directory and runs it
  • then, when reaching the @@, it looks for subScript2.sql in the same path as mainScript.sql and runs it
• @@ "includes" the specified script, which must be in the same directory than the script having the @@ command.

start

short for @ (sources : 1, 2)

• splits long resultsets into smaller chunks so that they can be conveniently printed on sheets of paper
• repeats the columns headers on each page
• is enabled by default
• defaults to 14 lines per page, which includes :
  • the column headers
  • the ---------------- spacer
  • 11 lines of results
  • a blank line

With paging (default) :

NAME
--------------------------------------------------------------------------------
log_archive_dest_state_1

log_archive_dest_state_11

NAME
--------------------------------------------------------------------------------
log_archive_dest_state_12

log_archive_dest_state_22

NAME
--------------------------------------------------------------------------------
log_archive_dest_state_23

log_archive_dest_state_31

31 rows selected.

Without paging :

log_archive_dest_state_1

log_archive_dest_state_31

31 rows selected.

Just use the -s command line flag :

echo 'who is a chatterbox' | sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 5 12:53:45 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

SQL> SP2-0734: unknown command beginning "who is a c..." - rest of line ignored.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

echo 'who is a chatterbox' | sqlplus -s / as sysdba

SP2-0734: unknown command beginning "who is a c..." - rest of line ignored.

echo "SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') \"NOW\" FROM DUAL;" | sqlplus -s / as sysdba

NOW
----------------------------------------------------------------------------
07-05-2016 13:00:09

• the version numbers
• copyright messages
• connected and disconnected messages
• the leading any output line (result, error message)

Method 1 :

Method 2 :

Method 3 :

export ORACLE_SID=myOracleSid; sqlplus '/ as sysdba' << EOSQL
query1;
query2;
query3;
EOSQL

method 1 :

sqlplus bob/password

No space in the login + "/" + password string

method 2 :

1. sqlplus
2. Enter user-name: bob
3. Enter password: (nothing echo'ed)

method 3 :

1. sqlplus /nolog
2. connect
3. Enter user-name: bob
4. Enter password: (nothing echo'ed)
5. Connected.
   SQL>

method 4 :

1. sqlplus /nolog
2. connect bob
3. Enter password: (nothing echo'ed)
4. Connected.
   SQL>

Specify the text editor :

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;

echo "SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') \"NOW\" FROM DUAL;" | sqlplus / as sysdba

NOW
----------------------------------------------------------------------------
07-07-2016 11:09:41

select * from dual;

D
-
X

For a (so far) unknown reason, query results returned by SQL*Plus are sometimes ugly / difficult to read because of the columns width which are WAY too large to fit the screen, resulting in wrapping lines. This is not convenient at all for tabular data
To fix this, use SQL*Plus's COLUMN.
Example :

export ORACLE_SID=ORACLE_SID; sqlplus '/ as sysdba' << EOSQL
COLUMN process format a10;
COLUMN status format a15;
SELECT process, status, thread#, sequence#, delay_mins from V\$MANAGED_STANDBY;
EOSQL

COLUMN process format a10; means "display the process column 10 characters wide".

In this example, the $ is \-escaped because the whole command is executed from the *Nix shell and we don't want expressions such as V$MANAGED_STANDBY to be expanded by the shell.

COLUMN has MANY more interesting features, read more and view examples in SQL*Plus COLUMN FORMAT tips .

Such formatting is for reporting purposes only : it allows displaying a string n characters wide if we like it so whatever its width is defined in the table. COLUMN does not alter data or tables.