Shell Programming and Scripting

Hi I have the following Input

--
-- TABLE: BUSINESS_UNIT
--

ALTER TABLE RATINGS.BUSINESS_UNIT ADD CONSTRAINT FK1_BUSINESS_UNIT
FOREIGN KEY (PEOPLESOFT_CHART_FIELD_VALUE_ID)
REFERENCES RATINGS.PEOPLESOFT_CHART_FIELD_VALUE(PEOPLESOFT_CHART_FIELD_VALUE_ID)
;

ALTER TABLE RATINGS.CASE ADD CONSTRAINT FK7_CASE
FOREIGN KEY (ENTITY_ID, VERSION_NBR)
REFERENCES RATINGS.ENTITY_VERSION(ENTITY_ID, VERSION_NBR)
;



ALTER TABLE RATINGS.CANADIAN_PROVINCE ADD CONSTRAINT FK1_CANADIAN_PROVINCE
FOREIGN KEY (ENTITY_ID, VERSION_NBR)
REFERENCES RATINGS.ENTITY_VERSION(ENTITY_ID, VERSION_NBR)
;

--
-- TABLE: CANADIAN_TERRITORY
--

ALTER TABLE RATINGS.CANADIAN_TERRITORY ADD CONSTRAINT FK1_CANADIAN_TERRITORY
FOREIGN KEY (ENTITY_ID, VERSION_NBR)
REFERENCES RATINGS.ENTITY_VERSION(ENTITY_ID, VERSION_NBR)
;

I need to get ouput of alter staments where in it

REFERENCES RATINGS.ENTITY_VERSION


Output looks like


ALTER TABLE RATINGS.CANADIAN_TERRITORY ADD CONSTRAINT FK1_CANADIAN_TERRITORY
FOREIGN KEY (ENTITY_ID, VERSION_NBR)
REFERENCES RATINGS.ENTITY_VERSION(ENTITY_ID, VERSION_NBR)
;

Code:

cat abc.txt
--
-- TABLE: BUSINESS_UNIT
--

ALTER TABLE RATINGS.BUSINESS_UNIT ADD CONSTRAINT FK1_BUSINESS_UNIT
FOREIGN KEY (PEOPLESOFT_CHART_FIELD_VALUE_ID)
REFERENCES RATINGS.PEOPLESOFT_CHART_FIELD_VALUE(PEOPLESOFT_CHART_FIELD_VALUE_ID)
;

....

Code:

 cat abc.txt | perl -e 'while(<>){
        chomp;
        next if (m/--/);
        $str .= $_;
        if ($_ =~ m/;/){
                print "$str\n" if ($str =~ m/REFERENCES RATINGS.ENTITY_VERSION/);
                $str = "";
        }
}'

Replace abc.txt with the file you have.

HTH,
PL