Skip to Main Content
  • Questions
  • sqlplus: How to have describe use fewer columns than linesize.

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Shannon.

Asked: April 27, 2011 - 7:17 pm UTC

Answered by: Tom Kyte - Last updated: April 29, 2011 - 8:53 am UTC

Category: SQL*Plus - Version: 10.2.0

Viewed 1000+ times

You Asked

I frequently use a linesize in sqlplus that is much longer than the width of the window it is running in and would like to, at the same time, limit describe to using 80 to 130 characters per line.

The SQL*PLUS User's Guide and Reference says, "Columns output for the DESCRIBE command are typically allocated a proportion of the linesize currently specified. Decreasing or increasing the linesize with the SET LINESIZE command usually makes each column proportionally smaller or larger." [Emphasis added] http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12019.htm#i2697562

So it says that typically and usually but I haven't found a section that describes how to achive the atypical and unusual situation of describe using fewer columns than linesize's setting. Is there a way? If so, please describe and/or point to relevant documentation.



and we said...

I think they were just being "cautious" in their writing. As far as I know, describe relies entirely on linesize to set its width.

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Suggestion

April 29, 2011 - 1:21 am UTC

Reviewer: Wijnand Engelkes from Netherlands

I use a self-made sql to accomplish this.

This is my d.sql:

save currentsql replace
show linesize
desc &what_to_describe
ho echo remember to set the linesize back to the original value mentioned above
get currentsql

Would it be possible to somehow "remember" the current linesize and set it back to that afterwards?

Sorry if this is pathetic. It works for me.
Always eager to see a better solution.

Thanks for all the wonderful tips, Tom, I always start with your page every day at the office.

Tom Kyte

Followup  

April 29, 2011 - 8:53 am UTC

Ok, here is what I do to deal with this sort of stuff. In my login.sql file, I have:

column E new_value E
column S new_value SETTINGS
select decode( substr( dbms_utility.port_string, 1, 5 ), 'IBMPC', 'vi', 'vi' ) E,
       decode( substr( dbms_utility.port_string, 1, 5 ),
               'IBMPC', 'c:\temp\xtmpx'||to_char(sysdate,'yyyymmddsssss')||'.sql',
                        '/tmp/xtmpx' || to_char(sysdate,'yyyymmddsssss') ||'.sql' ) S
  from dual;
define _editor=&E



so, that sets my editor to VI always now (used to be between vi and notepad a long time ago)... And it sets another define variable to a temporary file name - the define variable is SETTINGS.


Then I have a script - sset (save settings) that is just this:


store set &SETTINGS rep


and another script - rset (restore settings) that is just this:

@&&SETTINGS



and then my desc.sql is:


@sset
set verify off
set linesize 72
set pagesize 9999
set feedback off
set echo off

alter session set cursor_sharing=force;

Prompt Datatypes for Table &1

column data_type format a20
column column_name heading "Column Name"
column data_type   heading "Data|Type"
column data_length heading "Data|Length" format a10
column nullable    heading "Nullable" format a8

select column_name,
       data_type,
       substr(
       decode( data_type, 'NUMBER',
               decode( data_precision, NULL, NULL,
                '('||data_precision||','||data_scale||')' ),
                   data_length),
              1,11) data_length,
       decode( nullable, 'Y', 'null', 'not null' ) nullable
from all_tab_columns
where owner = user
  and table_name = upper('&1')
order by column_id
/

prompt
prompt 
Prompt Indexes on &1
column index_name heading "Index|Name"
column Uniqueness heading "Is|Unique" format a6
column columns format a32 word_wrapped

select substr(a.index_name,1,30) index_name, 
       decode(a.uniqueness,'UNIQUE','Yes','No') uniqueness, 
max(decode( b.column_position,  1, substr(b.column_name,1,30),
NULL )) ||
max(decode( b.column_position,  2, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  3, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  4, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  5, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  6, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  7, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  8, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  9, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 10, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 11, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 12, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 13, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 14, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 15, ', '||
substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 16, ', '||
substr(b.column_name,1,30), NULL )) columns
from all_indexes a, all_ind_columns b
where a.owner = user
and a.table_name = upper('&1')
and b.table_name = a.table_name
and b.table_owner = a.owner
and a.index_name = b.index_name
group by substr(a.index_name,1,30), a.uniqueness
/


prompt
prompt
prompt Triggers on &1
set long 5000
select trigger_name, trigger_type,
       triggering_event, trigger_body
from user_triggers where table_name = upper('&1')
/
alter session set cursor_sharing=exact;
@rset


Save sqlplus buffer too

April 29, 2011 - 9:51 am UTC

Reviewer: Andrew Markiewicz from Madison, WI USA

I have a similar script but I also save what's in the sqlplus buffer so I don't lose the current sql statement for the inevitable event when I run one of my utility script in a session that I have a sql I don't want to lose.
My two scripts are save_state.sql and restore_state.sql

-------------------
-- save_state.sql
-------------------
define saveid=&1
set term off
store set $HOME/tmp&&saveid\_set.sql replace
save $HOME/tmp&&saveid\_orig replace
set term on

-------------------
-- restore_state.sql
-------------------
set term off
get $HOME/tmp&&saveid\_orig
set term off

@$HOME/tmp&&saveid\_set
host rm $HOME/tmp&&saveid*
undefine saveid
set term on

--------------------
-- test_savestate.sql
--------------------
select 'BEGIN STATE' from dual
.
l
show line
@save_state test_ss

set line 300
select 'CURRENT STATE' from dual
.
l
show line

@restore_state
l
show line

---------------------
-- Run test_savestate
---------------------
sql->@test_savestate
  1* select 'BEGIN STATE' from dual
linesize 200
  1* select 'CURRENT STATE' from dual
linesize 300
  1* select 'BEGIN STATE' from dual
linesize 200
sql->