Index DDL generator, SQLPLUS want trucation after 80 columns

From: Linda Hagedorn <Linda_at_pets.com>
Date: Tue, 19 Sep 2000 09:43:04 -0700
Message-Id: <10624.117362@fatcity.com>

Hello,

I wrote a little index DDL generator using SQL. To get the DDL in the correct order I had to add some literal and sort fields that cannot be part of the output. I'd like to have a linesize of 80 and truncate the rest of the fields. SQLPLUS is wrapping the lines. Does anyone know if there is a way to truncate a SQLPLUS output line at nn columns?

I'm creating 36+ indexes from production. I realize I can cut/paste from an import file, but my eyes were getting blurry looking at the (Oracle are you listening?) scrambled output format.

Any information is appreciated.

This is the ouput I want from the generator (minor hand editing is acceptable - commas, sizes):

CREATE INDEX BVAMDIN.SYS_C003235 on BVADMIN.BV_ALERT_SPEC ( STRINGCOL1
INTCOL3
PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE (INITIAL                     NEXT

MINEXTENTS 1 MAXEXTENTS 2G PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE INDEXES NOLOGGING ; This is the wrapped output with the sort fields I want truncated:

CREATE INDEX BVAMDIN.SYS_C003235 on BVADMIN.BV_ALERT_SPEC (

                                         SYS_C003235
        1

STRINGCOL1
1                                        SYS_C003235
        2

INTCOL3
2                                        SYS_C003235
        2

PCTFREE 10 INITRANS 2 MAXTRANS 255
                                         SYS_C003235
        3

STORAGE (INITIAL                     NEXT
                                         SYS_C003235
        4

MINEXTENTS 1 MAXEXTENTS 2G PCTINCREASE 0 FREELISTS 1
                                         SYS_C003235
        5

FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE INDEXES NOLOGGING ;
                                         SYS_C003235
        6

This is the INDEX DDL generator.

set linesize 80 ;
SELECT distinct
substr('CREATE INDEX BVAMDIN.'
||index_name||' on BVADMIN.'||table_name||' ( ',1,80) , ' ', substr(index_name,1,35), 1
from dba_indexes
where index_name in
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
union
select column_name, to_char(column_position), substr(index_name,1,35), 2 from dba_ind_columns
where index_name in
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
union
select substr('PCTFREE 10 INITRANS 2 MAXTRANS 255 ',1,80) , ' ' , substr(INDEX_NAME,1,35), 3
FROM DBA_INDEXES
WHERE INDEX_NAME IN
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
UNION

SELECT substr('STORAGE (INITIAL '||'                   '
||' NEXT '||'                 ',1,80), ' ', 

substr(INDEX_NAME,1,35), 4
FROM DBA_INDEXES
WHERE INDEX_NAME IN
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
UNION
SELECT substr('MINEXTENTS 1 MAXEXTENTS 2G PCTINCREASE 0 FREELISTS 1 ',1,80) ,' ',substr(INDEX_NAME,1,35), 5
FROM DBA_INDEXES
WHERE INDEX_NAME IN
('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
UNION
SELECT substr('FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE INDEXES NOLOGGING ; ',1,80)
,' ',substr(INDEX_NAME,1,35), 6
FROM DBA_INDEXES
WHERE INDEX_NAME IN
   ('BILLING_ADDRESSES_ID_IDX','SYS_C003235','BV_DEST_ITEMS_INDEX',
'SYS_C003400','BV_EXT_BO_REL','BV_EXT_BO_REL_IDX1','BV_EXT_BO_REL_IDX2')
order by 3, 4 , 2 ; Received on Tue Sep 19 2000 - 11:43:04 CDT