REM  script6-1.sql (generate_copybook.sql)
REM  
REM  Reads table definition from data dictionary and generates a COBOL
REM    copybook description to define the requisite download file.
REM
REM  Known issues:
REM      Does not know how to specify PICTURE for LONG, RAW, LONG RAW or other
REM        exotic datatypes.  Will print 'UNDEFINED?' instead.
REM      Picture clause may not be legal for very large precision numbers
REM        (or columns defined as NUMBER without specification of precision)
REM        since Oracle's precision exceeds most COBOL implementations
REM      Uses first seven characters of the tables comments (if present) as 
REM        the output file name; otherwise uses the table name.  This may
REM        require changes to fit local file naming standards.

define outfile = ''
column out_file_name new_value outfile
REM      The following SELECT statement sets the output file name
select  decode(comments,'',table_name,substr(comments,1,7)) out_file_name
	from dba_tab_comments
	where
		owner      = upper('&ownerid')
	and	table_name = upper('&tablename')
/
column var1 noprint
column var2 noprint
spool &outfile..cpy
select 1 var1, 1 var2, 
       '      ***************************************************************'
    from dual
union
select 1 , 2 , '      *  COBOL DECLARATIONS FOR ORACLE TABLE '||
                        upper('&tablename')
    from dual
union
select 1 , 3 , 
       '      ***************************************************************'
    from dual
union
select 1 , 4 , '       01  DCL'||replace(upper('&tablename'),'_','-')||'.'
    from dual
union
select 2, column_id,
       '          05  '||
       rpad(substr(comments,4,4)||'-'||replace(column_name,'_','-'), 32)||
       decode(data_type,'VARCHAR2'    ,'PIC X('||data_length||').',
                        'VARCHAR'     ,'PIC X('||data_length||').',
                        'CHAR'        ,'PIC X('||data_length||').',
                        'DATE'        ,'PIC X(5).',
                        'NUMBER'      ,'PIC '||
                                       lpad('9', nvl(data_precision,38)
                                                 -nvl(data_scale,0)+1, '-')||
                                       decode(data_scale,0,'','','','.'||
                                              rpad('9',data_scale,'9')) ||
                                       '.',
                        'UNDEFINED?')
    from dba_tab_columns dtc,
	 dba_tab_comments dtcm
    where dtc.owner = upper('&ownerid')
      and dtc.table_name = upper('&tablename')
      and dtc.owner = dtcm.owner
      and dtc.table_name = dtcm.table_name
order by 1, 2
/ 
spool off
undef tablename
undef outfile