test blog only
set linesize 400
set heading off
set pagesize 9999
select column_name
,data_type
,len
,start_pos
,end_pos
,(case when (data_type='CHAR' or data_type='VARCHAR2')
then ','||lower(column_name)||' '||'position('||start_pos||':'||end_pos||')'
when data_type='NUMBER'
then ','||lower(column_name)||' '||'position('||start_pos||':'||end_pos||')
integer external defaultif ('||st!art_pos||':'||end_pos||')=blanks'
when data_type='DATE'
then ','||lower(column_name)||' '||'position('||start_pos||':'||end_pos||') date nullif
('||start_pos||':'||end_pos||')=blanks' end) as scr
from (select column_name, data_type, len, ((end_pos-len)+1) as start_pos, end_pos
from (select column_name, data_Type, len, sum(len) over (order by column_id) as end_pos
from (select column_name
,column_id
,substr(data_type,1,10) data_Type
,decode(data_type,
'NUMBER',(case when length(column_name) <= 10
then 10
when length(column_name) > 10
then length(column_name)
! ! end)
,'CHAR',data_length
,'DATE',length(sysdate)) as len
from user_tab_cols
where table_name='&table_name'
order by column_id)));