SQL*Plus Tricks Vol 1

sqlplus

SQL*Plus, some love it, some hate it.

Let’s focus on what SQL*Plus can do and leave the disadvantages aside for now.

Here are some of my top tricks to get more out of SQL*Plus.

Before getting into the subject, let’s have a look at SQL*Plus’ help command:

SQL> help index
Enter Help [topic] for help.
 @             COPY         PAUSE                    SHUTDOWN
 @@            DEFINE       PRINT                    SPOOL
 /             DEL          PROMPT                   SQLPLUS
 ACCEPT        DESCRIBE     QUIT                     START
 APPEND        DISCONNECT   RECOVER                  STARTUP
 ARCHIVE LOG   EDIT         REMARK                   STORE
 ATTRIBUTE     EXECUTE      REPFOOTER                TIMING
 BREAK         EXIT         REPHEADER                TTITLE
 BTITLE        GET          RESERVED WORDS (SQL)     UNDEFINE
 CHANGE        HELP         RESERVED WORDS (PL/SQL)  VARIABLE
 CLEAR         HOST         RUN                      WHENEVER OSERROR
 COLUMN        INPUT        SAVE                     WHENEVER SQLERROR
 COMPUTE       LIST         SET                      XQUERY
 CONNECT       PASSWORD     SHOW

One day you’ll have all these commands in mind but for now this is quite handy to get the right command as you need it — without google search.

Most of these commands have shortcuts like “LIST = L = l”. In the below tricks, I’m going to use shortcuts.

Trick 1: Configure SQL*Plus session

Here are some hints to configure your current SQL*Plus session:

SQL> set lin 200

Set line size to 200 characters. Keep in mind that your terminal should be wide enough.

SQL> set pages 100

Set the page size to 100 lines. When you query a table, SQL*Plus will print the column header/names followed by the values. The header is being repeated each n lines. In this case after 100 lines.

SQL> set timi on

This will show the execution time of each query.

SQL> set mark html on

You can print the output in html format. When you spool the output to a file, you can later view it in a browser.

Trick 2: Spool output to a file

You can capture SQL*Plus’ output to a file to review it later or with a different tool.

SQL> set mark html on
SQL> spool output.html
SQL> select * from dept;
SQL> spool off

Trick 3: Format columns

You can format the output of queries in many ways. Here are some basic formatting hints:

SQL> col name format a30

Limit the column length of a VARCHAR2 column. If the text exceeds your configured limit, it will simply break the line. For example:

SQL> col name format a40
SQL> select name from v$datafile;
NAME
----------------------------------------
/opt/oracle/oradata/DWH/datafile/o1_mf_s
ystem_74q5wbbb_.dbf

/opt/oracle/oradata/DWH/datafile/o1_mf_s
ysaux_74q5x8yx_.dbf
[...]

Next, format numbers:

SQL> col price format 999,999,999.99

Or just use a general number format for any number column:

SQL> set numf 999,999,999.99

You want to use “.,” instead of “,.” to meet your national number formatting fashion?

SQL> set numf 999G999G999G999D99
SQL> select bytes/1024 MB from v$datafile;
 MB
-------------------
      11.980.800,00
       1.064.960,00
       8.388.608,00
[..]

SQL> show parameter nls_num
NAME                   TYPE          VALUE
---------------------- ------------- -------------------
nls_numeric_characters string        ,.

SQL> alter session set nls_numeric_characters='.,';
Session wurde geändert.

SQL> show parameter nls_num
NAME                   TYPE          VALUE
---------------------- ------------- -------------------
nls_numeric_characters string        .,

SQL> select bytes/1024 MB from v$datafile;
 MB
-------------------
      11,980,800.00
       1,064,960.00
       8,388,608.00
[...]

Trick 4: Modify a SQL Query with an editor

I think you know the following problem very well.

$ export EDITOR=vi
$ sqlplus / as sysdba

SQL> select * frim dual;
select * frim dual
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

You don’t need to retype the statement. Just edit it with your preferred editor. By setting the environment variable EDITOR you can choose the editor.

SQL> ed

-- I changed the "frim" to "from", saved and quit.

Wrote file afiedt.buf
  1* select * from dual
SQL> r
  1* select * from dual
D
-
X

By the way: “r” executes the command which is currently in the buffer.

Here is another post about this subject: “SQL*Plus Tricks Vol 2“. Enjoy!

That’s IT.

Advertisements

One thought on “SQL*Plus Tricks Vol 1

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: