Very Computer

here is one solution using sed and awk in a script "extrac"
     1  sed '
     2  /^--/d                    remove the lines bebining with--
     3  /--/!d                    keep the lines containing--
     4  s/INTEGER/INTEGER(4),/    complete information for INTEGER
     5  s/[()]/ /g                subtitute ( with blanc )
     6  s/[,;-]//g                destroy caracters,;-
     7  ' shell.txt |             replace with the filename variable
argument
     8  awk '{ print "namefield", $(NF), "typename", $(NF-2),
"size",$(NF-1)}'
~                                 format the output with awk.
~
!sh extrac
namefield sec_id typename INTEGER size 4
namefield class_id typename VARCHAR size 4
namefield symbol typename VARCHAR size 30
namefield curr_cd typename VARCHAR size 3
namefield alias typename VARCHAR size 20

source='fe113r_Instrument.sql'output='fe113r_Instrument.map'

cat $source |
awk '/^[     ]*RETURNING[     ]/,/;/'  |
sed 's/^[     ]*RETURNING[     ]//; s/[,;]--/  /g; s/(\([1-9][0-9]*\))/  \1  /g'
|
tr  A-Z a-z |
awk 'BEGIN { print "storedproc fe113r_Instrument" }
       NF==3   { print "fieldname", $(NF), "typename", $1, "size",  $2  }
       /integer/  { print "fieldname", $(NF), "typename", $1, "size", "4" }
             END { print "storedprocend" }' >$output

Quote:> > FILENAME: fe113r_Instrument.sql
> > --DROP PROCEDURE fe113r_Instrument;
> > CREATE PROCEDURE fe113r_Instrument(
> >         i_class_id LIKE CURRENCY.class_id,
> >         i_curr_cd LIKE CURRENCY.curr_cd
> > )
> > RETURNING  INTEGER,--sec_id
> >            VARCHAR(4),--class_id
> >            VARCHAR(30),--symbol
> >            VARCHAR(3),--curr_cd
> >            VARCHAR(20);--alias

> > FILENAME: fe113r_Instrument.map
> > storedproc fe113r_Instrument
> > fieldname      sec_id               typename integer     size 4
> > fieldname      class_id             typename varchar     size 4
> > fieldname      symbol               typename varchar     size 30
> > fieldname      curr_cd              typename varchar     size 3
> > fieldname      alias                typename varchar     size 20
> > storedprocend

#!/bin/sh
name='fe113r_Instrument'
source=${name}'.sql'
output=${name}'.map'

cat $source |
awk '/^[     ]*RETURNING[     ]/,/;/'  |
sed 's/^[     ]*RETURNING[     ]//
s/[,;]--/  /g; s/(\([1-9][0-9]*\))/  \1  /g' |
tr  A-Z a-z |
awk 'BEGIN { print "storedproc '${name}'" }
       NF==3   { print "fieldname", $(NF), "typename", $1, "size",  $2  }
       /integer/  { print "fieldname", $(NF), "typename", $1, "size", "4" }
             END { print "storedprocend" }' >$output

not tested
lms