Tech > OracleSqlPlus

SQL*Plus Hints and Tips

Configuring

tnsnames.ora

Need to create a tnsnames.ora either in ORACLEHOME/network/admin/tnsnames.oraorinthefiledefinedbytheTNS_ADMIN environment variable.

Example format:

  CAT << EOF >> tnsnames.ora
  mydb=
   (DESCRIPTION=Optional Description
     (ADDRESS=(PROTOCOL=tcp) (HOST=my.hostname.tld) (PORT=1521))
     (CONNECT_DATA=(SID=mysid)))
  EOF

Can then connect to db with

  $ORACLE_HOME/bin/sqlplus username@mydb

to try just once:

  $ORACLE_HOME/bin/sqlplus -L username@mydb

-- Frank Dean - 29 Sep 2012

Settings

Store current settings in a file:

  STORE SET file_name

Restore them after with:

  START file_name

Miscellaneous

Debugging

Show output of stuff written by DBMS_OUTPUT.PUT_LINE() * SET SERVEROUTPUT ON If you need to increase the buffer size * SET SERVEROUTPUT ON SIZE 50000

Set some sensible page heights and widths * SET PAGESIZE 40 * SET LINESIZE 132

Change the width of a column

COL[UMN] MY_EXACT_COL_NAME FOR[MAT] A20
* or numeric
COLUMN MY_NUMERIC_COL_NAME FORMAT 999
* or currency
COLUMN MY_CURRENCY_COL_NAME FORMAT $999

Change the edit filename

SET EDITF[ILE] D:\TEMP\TEMP.SQL

Creating a flat file

To create a flat file with SQL*Plus, you first must enter the following SET commands:

  SET NEWPAGE NONE
  SET COLSEP ''
  SET LINESIZE 132
  SET PAGESIZE 0
  SET ECHO OFF
  SET FEEDBACK OFF
  SET VERIFY OFF
  SET HEADING OFF
  SET MARKUP HTML OFF SPOOL OFF

  SPOOL filename

SPACE is obsolete and has been replaced with:

  SET COLSEP ''

See the 'Settings' section above for information on storing and restoring the settings. Alternatively, re-set the above changes with:

  SET NEWPAGE 1
  SET COLSEP ' '
  SET LINESIZE 80
  SET PAGESIZE 60
  SET ECHO ON
  SET FEEDBACK 6
  SET VERIFY ON
  SET HEADING ON

-- Frank Dean - 29 Sep 2012

Copying previous commands

Rather than copy and paste previous commands, you can copy a previous command straight to the command line in SQL*Plus with the mouse. However, you have to be a bit of a finger contortionist.

  1. Left click and drag to select the text to be copied.
  2. Keeping the left mouse button down, press and release the right mouse button.
  3. Your text is copied to the command line.
  4. You can now let go of the left mouse button :)

Clearing screen and buffer

  • Press shift-Del

Executing Stored Procedures

  • VARIABLE order NUMBER;
  • EXECUTE :order := new_order('test');
  • EXECUTE update_order(:order, 1234);
  • EXECUTE my_pkg.my_proc();

To see the value of the session variable 'order'

  • PRINT order

See also:

-- Frank Dean - 12 Dec 2002