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