/*******************************************************************************
#                          ORACLE SQL SCRIPT
#*******************************************************************************
#
#    PROGRAM ID           : SQL*Plus Column Optimizer
#
#    DESCRIPTION          : Optimize the column format in SQL*Plus
#
#                           !!!   UNDER CONSTRUCTION   !!!                           
#                           !!!   UNDER CONSTRUCTION   !!!                           
#                           !!!   UNDER CONSTRUCTION   !!!                           
#
#    INPUT PARAMETERS     : none
#
#    OUTPUT PARAMETERS    : none
#
#
#    SPECIAL LOGIC NOTES  : none
#
#*******************************************************************************
#                        MODIFICATION LOG
#*******************************************************************************
#
#    DATE                               SE
#       DESCRIPTION
#    ---------------------------------------------------------------------------
#    Thu Jul 21 10:34:29 CEST 2016      (HRu)
#       initial version
#    ---------------------------------------------------------------------------
#
#******************************************************************************/
set serveroutput on
declare
   v_maxlen        number;
   v_colcnt        number;
   v_owner         varchar2(32) := 'ISTP_ADM';
   v_table         varchar2(32) := 'ISTP_VERSION_INFO_HIST';
   v_column        varchar2(32) := 'FULL_BUILD_NR';
   v_sql           varchar2(4000);
   v_forline       varchar2(4000);
   v_ninestrg      varchar2(40) := '';

   cursor c_column_cursor is
      select column_name, data_type
        from all_tab_columns
       where owner        = v_owner
         and table_name   = v_table
       order by column_id;

begin
   for column_record in c_column_cursor
   loop

      /*--------------------------------------------------------
      | determine maximum used length of the particular column |
      --------------------------------------------------------*/
      v_sql := 'select max(MAXLEN) from (select length(' ||
               column_record.column_name ||
               ') "MAXLEN" from ' ||
               v_owner ||
               '.' ||
               v_table ||
               ')';

      execute immediate v_sql into v_maxlen;

      /*-------------------------------------------------
      | build column formatting line based on data type |
      -------------------------------------------------*/
      case substr(column_record.data_type,1,8)
         when 'NUMBER' then
            v_ninestrg := '';
            for v_counter in 1..v_maxlen
            loop
               v_ninestrg := v_ninestrg || '9';
            end loop;
            v_forline := 'col ' || column_record.column_name || ' for ' || v_ninestrg;
         when 'TIMESTAM' then
            dbms_output.put_line ('alter session set nls_date_format=' || CHR(39) || 'DD-MM-YYYY' || CHR(39) || ';');
            v_forline := 'col ' || column_record.column_name || ' for a10';
         else
            v_forline := 'col ' || column_record.column_name || ' for a' || v_maxlen;
      end case;

      /*-------------------------------------------------
      | print it...                                     |
      -------------------------------------------------*/
      dbms_output.put_line (v_forline);

   end loop;

exception
   when others then
      dbms_output.put_line ('Fehler: ' || SQLCODE);

end;
/