Problem solve Get help with specific problems with your technologies, process and projects.

Annoying DESCribe in 9i SQL*Plus

The DESCribe command uses the LINESIZE value to control the display of fields. Here is a handy way around it.

Ever use the DESC command in a SQL*Plus session, then have to scroll right to find some of the fields? The DESCribe...

command uses the LINESIZE value to control the display of fields. Here is a handy way around it.

From the testing I've done, it turns out it is not related to the database version. Instead, it is related to the version of SQL*Plus you use to connect to the database. SQL*Plus 3.3 does not change the display of a DESC command based on the LINESIZE parameter. With SQL*Plus versions shipped with Oracle 8i and 9i, the LINESIZE parameter directly affects how the DESCribe command displays information. When we upgraded to 8i, we did not change to SQL*Plus 8.1 on the client machines, so we never noticed this problem. Only when we added 9i and the latest SQL*Plus to the mix of systems did this become an issue.

In our environment we have a login script that is the same for everyone. We set the LINESIZE = 300. This worked well until we moved to Oracle 9i and started using SQL*Plus 9.0.0. Now the normal queries still work fine, but when you need to describe any table, the linesize affects the display and causes the user to waste time scrolling the window to see all the info. We've only been using this for a week and have had positive feedback from users and developers.

In a SQL*Plus session:

edit desc.sql 
     set linesize 80 
     desc &1 
     set linesize 300 

Then save and exit. To use:

SQL> start desc user_tables 
This will shrink the line, describe the object, then increase the linesize back to a larger value. For even more control you can replace the 80 and 300 with variables. For example:
   set linesize &1 
   desc &2 
   set linesize &3 
To use:
SQL> start desc 75 user_tables 250 

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was last published in September 2002

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.