SQL*Plus Hints and Tips
- Configuring
  - tnsnames.ora
- Settings

SQL*Plus Hints and Tips

Configuring

tnsnames.ora

Need to create a tnsnames.ora either in $ORACLEHOME/network/admin/tnsnames.ora or in the file defined by the $TNSADMIN 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 DBMSOUTPUT.PUTLINE() * 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] MYEXACTCOLNAME FOR[MAT] A20 * or numeric COLUMN MYNUMERICCOLNAME FORMAT 999 * or currency COLUMN MYCURRENCYCOL_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.

Left click and drag to select the text to be copied.
Keeping the left mouse button down, press and release the right mouse button.
Your text is copied to the command line.
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 mypkg.myproc();

To see the value of the session variable 'order'

PRINT order