Acquiring Current Date and Time
This SELECT statement not only provides the current date and time, but also demonstrates use of the DUAL table, column name aliasing, and use of the to_char SQL function.
SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') "Current Date / Time"
Viewing Database Object Metadata
I posted previously on the usefulness of the Oracle Data Dictionary. This examples makes use of the Oracle Data Dictionary and demonstrates setting a column to a fixed width.
column object_name format a20
SELECT object_name, object_type, created, last_ddl_time, timestamp, status
Run Operating System Commands from SQL*Plus
One of my favorite commands in SQL*Plus is the host command. This command is useful when one wants to run a particular script but cannot remember the exact name of the script file or its host directory. The command "host ls" or "host dir" can be used to see directory listings.
Make nulls Easily Identifiable
It can sometimes be difficult to distinguish null from an empty string or other value that does not get printed in SQL*Plus for a SELECT statement. The set null command is useful here because it allows one to specify what string should appear to indicate null values. For example, I frequently set up my SQL*Plus environments with
set null <null>.
Use column Command to Format Columns Appropriately
The SQL*Plus column command is useful for setting a column's format to appear more aesthetically pleasing in an SQL*Plus query and other work done in SQL*Plus.
When writing output from PL/SQL to SQL*Plus (such as with the built-in package/procedure DBMS_OUTPUT.PUT_LINE), it can be frustrating when no output appears. This is typically remedied by changing the specification of the serveroutput with a command
set serveroutput on. Even with
serveroutputenabled, one might still not see all of the results. This can be adjusted with the size parameter for the
serveroutputoption (including an
show serveroutputin SQL*Plus to see its settings. Another useful SQL*Plus option to set for displaying LONG data is set long NNNNN to display full CLOB output in SQL*Plus.
Working with set and Options
Many of the tips covered in this blog post are related to the SQL*Plus
setcommand. The currently configured value for a particular SQL*Plus variable can be displayed with the
showcommand. The command
help setcan also be used to view the potential SQL*Plus environment variables that can be set and SQL*PLUS - SET Statement contains additional details on them.
SQL*Plus has never been known for its ease of use, but its appeal does seem to grow as one uses it regularly. One recent piece of evidence regarding the continued use of SQL*Plus is the recent blog post How to Execute Process Flow from SQL*Plus. SQL*Plus is a tool that can be particularly useful for quick operations, especially when one knows a few tips and tricks to make it easier to use.
Other SQL*Plus References
⇒ SQL*Plus Product Page
⇒ SQL*Plus Documentation
⇒ SQL*Plus FAQ
⇒ René Nyffenegger's SQL*Plus Posts (several referenced in this post)
⇒ SQL*Plus Tips and Tricks
⇒ SQL*Plus Tips for Oracle Beginners
⇒ PSOUG SQL*Plus Reference