Favorite SQL*Plus Tips

Although there are many tools available that are far more user-friendly than SQL*Plus for manipulating and accessing Oracle database data, I still find myself occasionally using SQL*Plus when I need to do something with the database for which a more extravagant tool actually takes longer to use and feels like overkill.

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"
  FROM dual;

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
  FROM user_objects;

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.


Displaying Output

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 serveroutput enabled, one might still not see all of the results. This can be adjusted with the size parameter for the serveroutput option (including an unlimited setting). Run show serveroutput in 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 set command. The currently configured value for a particular SQL*Plus variable can be displayed with the show command. The command help set can 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.


Conclusion

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

⇒ SQL*Plus

⇒ PSOUG SQL*Plus Reference