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.
odba.eu 