file=$1
table=$2
awk -F'|' '
BEGIN {
   colDefaultLength = 1;
}
{ printf "Record %d [%s]\n", NR, $0 }
NR==1 {
   for ( c=1; c<=NF; c++ ) {
      colName[c] = $c;
   }
   colCount = NF;
if (!colName[colCount]) print "No name for last column " colCount;
   if (!colName[colCount]) colCount--;
print "Column list:"
for (c=1; c<=colCount; c++) printf "  %d [%s]\n", c, colName[c];

   next;
}
{
   for ( c=1; c<=NF; c++ ) {
      if (length($c) > colLength[c]) colLength[c] = length($c);
           if ( $c ~ /^ *$/ )                     continue;
           if ( $c ~ /^[0-9]+$/        )          colIsNum[c]++
      else if ( $c ~ /^[0-9]*\.[0-9]*$/ )         colIsDec[c]++
      else if ( $c ~ /^[0-9]+\/[0-9]+\/[0-9]+$/ ) colIsDat[c]++
      else                                        colIsTxt[c]++;
   }
}
END {
printf "END Column list:"
for (c=1; c<=colCount; c++) printf "  %d [%s] len=%d\n", c, colName[c], colLength[c];

   print "create table " TableName "("
   for ( c=1; c<=colCount; c++ ) {

      if ( colName[c] == "" ) {
         if (colLength[c]+0 == 0) {
            continue;
         } else {
            colName[c] = "Column" c;
         }
      }


      if ( colLength[c]+0 == 0 ) colLength[c] = colDefaultLength;
      var2Len = colLength[c];
           if (var2Len <   50) var2Len = 50
      else if (var2Len <  100) var2Len = 100
      else if (var2Len <  500) var2Len = 500
      else if (var2Len < 1000) var2Len = 1000;

      out = "  " colName[c];
           if ( colIsTxt[c] ) out = out " varchar2(" var2Len ")"
      else if ( colIsDat[c] ) out = out " date"
      else if ( colIsDec[c] ) out = out " number(38,4)"
      else if ( colIsNum[c] ) out = out " number(" colLength[c] ")"
      else                    out = out " varchar2(" var2Len ")";
      if ( c != colCount ) out = out ",";

      print out

   }
   print ");";
}
' TableName=$table $file

ID|ID2|ID3|ID4|ID5|ID6
7666303|7666|73037341|XXX|XXXX-YYYY/Z|

$ ./mohan4.sh mohan4.txt xx
Record 1 [ID|ID2|ID3|ID4|ID5|ID6]
Column list:
  1 [ID]
  2 [ID2]
  3 [ID3]
  4 [ID4]
  5 [ID5]
  6 [ID6]
Record 2 [7666303|7666|73037341|XXX|XXXX-YYYY/Z|]
END Column list:  1 [ID] len=7
  2 [ID2] len=4
  3 [ID3] len=8
  4 [ID4] len=3
  5 [ID5] len=11
  6 [ID6] len=0
create table xx(
  ID number(7),
  ID2 number(4),
  ID3 number(8),
  ID4 varchar2(50),
  ID5 varchar2(50),
  ID6 varchar2(50)
);
$