mail

How to disable SQL*Plus copyright / connected / disconnected / ... messages ?

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
This hides :
mail

How to chain queries with SQL*Plus ?

Mixing methods 1 and 2 (i.e. using both ; and / after each query) led to weird results such as executing each query twice.

Method 1 :

  1. Write the queries into file /path/to/myQueries.sql, with :
    • the file name must have a .sql extension
    • place a / after every query, including the last one
    • no need for a trailing ; in queries
    query1
    /
    query2
    /
    query3
    /
  2. run queries :
    • within SQL*Plus :
      @/path/to/myQueries.sql
    • directly from the shell or a script :
      sqlplus / as sysdba @"/path/to/myQueries.sql"
      The command below would work fine here, but it uselessly uses the echo | construct :
      echo "@/path/to/myQueries.sql" | sqlplus / as sysdba

Method 2 :

  1. echo "query1;" > /path/to/myQueries
    Don't forget the trailing ; in queries
  2. echo "query2;" >> /path/to/myQueries
  3. echo "query3;" >> /path/to/myQueries
  4. cat /path/to/myQueries | sqlplus / as sysdba
    We have no other choice here than using the cat | construct since queries are not properly formatted like in the method 1 above.

Method 3 :

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

How to connect to Oracle with SQL*Plus ?

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>
mail

SQL*Plus command hacks

Specify the text editor :

SQL> define _editor=vi

Or, as oracle : echo define _editor=vi >> $ORACLE_HOME/sqlplus/admin/glogin.sql

Then, should you make a typo or any kind of error in a command you type in SQL*Plus :
  1. SQL> select name from v$contrlofile;
    select name from v$contrlofile
                     *
    ERROR at line 1:
    ORA-00942: table or view does not exist
  2. SQL> ed ==> you enter the defined text editor.
  3. Edit the query within the text editor until you're satisfied with it. Then save and exit :
    "afiedt.buf" 2 lines, 33 characters written
    
     1* select name from v$controlfile
    
  4. Then run : SQL> run
     1* select name from v$controlfile
    
    NAME
    --------------------------------------------------------------------------------
    /u01/app/oracle/oradata/MYDB/control01.ctl
mail

A dummy query for tests with SQL*Plus

Try one of these :
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
mail

How to format the resultset returned by SQL*Plus ?

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.