PLSQL_CS_CLASS_GEN.sql

gistfile1.txt

with metadata as
(
        select 
          utc.table_name, 
          utc.column_name, 
          replace(initcap(replace( lower(utc.column_name) ,'_',' ')),' ','') as column_name_clean,
          initcap(replace( lower(utc.column_name) ,'_',' ')) as column_name_space,
          rtrim(substr(utc.column_name,1,26),'_') as column_name_26,
         case utc.data_type
            when 'DATE' then 'DateTime' || DECODE(nullable, 'Y', '?', '')
            when 'VARCHAR2' then 'string'.sql
            when 'CLOB' then 'string'
            when 'NUMBER' then 
              case when utc.data_scale=0 then 
                case 
                  when utc.data_precision = 19 then 'long'|| DECODE(nullable, 'Y', '?', '')
                  when utc.data_precision = 9 then 'int'|| DECODE(nullable, 'Y', '?', '')
                  when utc.data_precision = 4 then 'int'|| DECODE(nullable, 'Y', '?', '')
                  when utc.data_precision = 1 then 'bool'|| DECODE(nullable, 'Y', '?', '')
                  else 'int'|| DECODE(nullable, 'Y', '?', '') end
              else 'decimal'|| DECODE(nullable, 'Y', '?', '') end
            when 'CHAR' then 
              case when utc.data_length = 1 then 'char'
              else 'string' end
            else '' end as clr_data_type,
         case utc.data_type
            when 'DATE' then 'DateTime'
            when 'VARCHAR2' then 'Text'
            when 'CLOB' then 'MultilineText'
            when 'CHAR' then 'Text'
            else '' end as mvc_data_type, 
         case utc.data_type
            when 'DATE' then 'Date'
            when 'TIMESTAMP' then 'TimeStamp'
            when 'VARCHAR2' then 'Varchar2'
            when 'CLOB' then 'Clob'
            when 'NUMBER' then 
              case when utc.data_scale=0 then 
                case 
                  when utc.data_precision = 19 then 'Int64'
                  when utc.data_precision = 9 then 'Int32'
                  when utc.data_precision = 4 then 'Int16'
                  when utc.data_precision = 1 then 'Decimal-Boolean'
                  else 'Int'|| utc.data_precision  end
                else 'Decimal' end

            when 'CHAR' then 'Char'
            else '' end as odp_data_type, 
          utc.Data_Type as native_data_type,
         case when utc.data_type = 'VARCHAR2' or utc.data_type='CHAR' then Data_Length
            else null end as mvc_data_range, 
          utc.data_length,
          utc.data_precision,
          utc.data_scale,
          utc.nullable,  
            case 
              when utc.data_scale > 0 then 
                '^\d{' || (nvl(utc.data_precision,1)-nvl(utc.data_scale,0)) || '}(\.\d{' || nvl(utc.data_scale,0) || '})?$'
              else '' end as validation_reg_ex,
          'n.' || trim(rpad(' ', nvl(utc.data_scale,0), 'd')) as validation_format,
          case utc.data_type
            when 'NUMBER' then
              case when utc.data_scale=0 then utc.data_precision
              else utc.data_precision + 1 end -- +1 for the decimal
            else
              utc.data_length end as max_string_length,
          case ac.constraint_type when 'P' then 'Y' else 'N' end as PRIMARY_KEY,
          ac.constraint_type
        from all_tab_columns utc
        left join all_cons_columns acc
          join all_constraints ac on ac.constraint_name = acc.constraint_name and ac.owner = acc.owner and ac.constraint_type='P'
          on utc.column_name=acc.column_name and utc.table_name=acc.table_name
        where utc.table_name not like 'BIN%' AND  utc.table_name like 'PROJWBS%'
        order by utc.table_name, utc.column_id
)
select 'public ' || clr_data_type || ' ' || column_name || ' {get; set;}' as ClassProperty, m.*
from metadata m;