why is this SP not compiling at last line?

From: Manoj Nair <debo_nair_at_yahoo.com>
Date: 6 Aug 2001 18:25:00 -0700
Message-ID: <92a0e580.0108061724.516c44f@posting.google.com>

create or replace procedure GPMS_Interface as

/****
* Process 5

*****/
lNewGPMSTitle VARCHAR2(255);
lOldTitleRelease VARCHAR2(255);

/***
* PROCESS 6

****/

lGPMSTitle VARCHAR2(255);
lNewGPMSNo VARCHAR2(6);
lOldGPMSNo VARCHAR2(6);

/************
* PROCESS 8

*********/
lNewGPMSTitleCT VARCHAR2(255);
lOldTitleCT VARCHAR2(255);

/***
* PROCESS 9

****/

lGPMSTitleCT VARCHAR2(255);
lNewGPMSNoCT VARCHAR2(6);
lOldGPMSNoCT VARCHAR2(6);
 

CURSOR NEW_OLD_TITLE_cursor
IS select distinct b.GPMS_TITLE, a.TITLE from title_release a, gpms_title b where b.GPMS_TITLE <> a.TITLE

                  and a.GPMS_NO = b.GPMS_NO;                 

CURSOR NEW_OLD_GPMSNo_cursor
IS select distinct b.GPMS_TITLE, b.GPMS_NO, a.GPMS_NO from title_release a, gpms_title b where b.GPMS_NO <> a.GPMS_NO

                  and a.TITLE = b.GPMS_TITLE;                 

CURSOR NEW_OLD_TITLE_CT_cursor
IS select distinct b.GPMS_TITLE, a.TITLE from constituent_titles a, gpms_title b where b.GPMS_TITLE <> a.TITLE

                  and a.GPMS_NO = b.GPMS_NO;

CURSOR NEW_OLD_GPMSNo_CT_cursor
IS select distinct b.GPMS_TITLE, b.GPMS_NO, a.GPMS_NO from constituent_titles a, gpms_title b where b.GPMS_NO <> a.GPMS_NO

                  and a.TITLE = b.GPMS_TITLE;

begin
 begin

         begin

/**************/
/* PROCESS 4 */
/*************/

update TITLE_RELEASE a set a.CAD_NO = (select b.CAD_NO from GPMS_Title b where b.GPMS_NO

• a.GPMS_NO) , a.CATALOG_NO = (select b.CATALOG_NO from GPMS_Title b where b.GPMS_NO
• a.GPMS_NO) , a.STUDIO = (select b.STUDIO from GPMS_Title b where b.GPMS_NO
• a.GPMS_NO), a.EARLIEST_THEATRICAL_DATE = (select b.EARLIEST_THEATRICAL_DATE from GPMS_Title b where b.GPMS_NO
• a.GPMS_NO) , a.COUNTRY = (select b.COUNTRY from GPMS_Title b where b.GPMS_NO
• a.GPMS_NO);

	end;
	commit;																		


/***********/

/* PROCESS 5 */
/***********/

  begin
  open NEW_OLD_TITLE_cursor;
  EXCEPTION
  WHEN OTHERS THEN
            dbms_output.put_line('NEW_OLD_TITLE cursor in exception sto_selcnt set to 1' );
  END;   fetch NEW_OLD_TITLE_cursor INTO GPMS_Interface.lNewGPMSTitle, GPMS_Interface.lOldTitleRelease ;  

  IF NEW_OLD_TITLE_cursor%NOTFOUND THEN

        dbms_output.put_line('NEW_OLD_TITLE cursor NOT FOUND sto_selcnt set to 1');
  END IF;   WHILE (NEW_OLD_TITLE_cursor%FOUND) LOOP   BEGIN

	 BEGIN
  	   insert into title_changes(DATE_OF_CHANGE,TITLE,OLD_VALUE,TYPE_OF_CHANGE)
	   		  values(sysdate,GPMS_Interface.lNewGPMSTitle,GPMS_Interface.lOldTitleRelease,
			  				'Title Changed');																												  			  				    
  	END;   		  	   																				 																			

  END LOOP;   commit;

  begin
  update title_release a set a.title = ( select distinct b.gpms_title from gpms_title b where b.GPMS_TITLE <> a.TITLE

                  and a.GPMS_NO = b.GPMS_NO ) where a.GPMS_NO in (select b.GPMS_NO from gpms_title b where a.GPMS_NO = b.GPMS_NO and

                                                                                                                         b.GPMS_TITLE <> a.TITLE);   end;
  commit;                       

/***********/
/* PROCESS 6 */
/***********/

  begin
  open NEW_OLD_GPMSNo_cursor;
  EXCEPTION
  WHEN OTHERS THEN
            dbms_output.put_line('NEW_OLD_GPMSNo_cursor cursor in exception sto_selcnt set to 1' || sqlcode || sqlerrm);   END;   FETCH NEW_OLD_GPMSNo_cursor INTO
GPMS_Interface.lGPMSTitle,GPMS_Interface.lNewGPMSNo, GPMS_Interface.lOldGPMSNo ;  

  IF NEW_OLD_GPMSNo_cursor%NOTFOUND THEN

                dbms_output.put_line('NEW_OLD_GPMSNo_cursor cursor NOT FOUND sto_selcnt set to 1' || sqlcode || sqlerrm);   END IF;   WHILE ( NEW_OLD_GPMSNo_cursor%FOUND) LOOP   BEGIN
    begin

  	   insert into title_changes(DATE_OF_CHANGE,TITLE,NEW_VALUE,OLD_VALUE,TYPE_OF_CHANGE)
	   		  values(sysdate,GPMS_Interface.lGPMSTitle,GPMS_Interface.lNewGPMSNo,GPMS_Interface.lOldGPMSNo,
			  				'GPMS_No Changed');
	end;																												  			  				    

  END LOOP;   commit;   

  begin

  update title_release a set a.GPMS_NO = ( select distinct b.GPMS_NO from gpms_title b where b.GPMS_TITLE = a.TITLE

                  and a.GPMS_NO <> b.GPMS_NO ) where a.GPMS_TITLE in (select b.GPMS_TITLE from gpms_title b where a.GPMS_NO <> b.GPMS_NO and

                                                                                                                         b.GPMS_TITLE = a.TITLE);

  end;
  commit;  

/***********/
/* PROCESS 7 */
/***********/

begin
update Constituent_Titles a set a.CAD_NO = (select b.CAD_NO from GPMS_Title b where b.GPMS_NO

• a.GPMS_NO) , a.CATALOG_NO = (select b.CATALOG_NO from GPMS_Title b where b.GPMS_NO
• a.GPMS_NO) , a.STUDIO = (select b.STUDIO from GPMS_Title b where b.GPMS_NO
• a.GPMS_NO); end; commit;

/***********/
/* PROCESS 8 */
/***********/

  begin
  open NEW_OLD_TITLE_CT_cursor;
  EXCEPTION
  WHEN OTHERS THEN
            dbms_output.put_line('NEW_OLD_TITLE_CT_cursor in exception sto_selcnt set to 1' || sqlcode || sqlerrm);   END;   FETCH NEW_OLD_TITLE_CT_cursor INTO GPMS_Interface.lNewGPMSTitleCT, GPMS_Interface.lOldTitleCT ;  

  IF NEW_OLD_TITLE_CT_cursor%NOTFOUND THEN

                dbms_output.put_line('NEW_OLD_TITLE_CT_cursor NOT FOUND sto_selcnt set to 1' || sqlcode || sqlerrm);
  END IF;   WHILE ( NEW_OLD_TITLE_CT_cursor%FOUND) LOOP   BEGIN
   begin

  	   insert into title_changes(DATE_OF_CHANGE,TITLE,OLD_VALUE,TYPE_OF_CHANGE)
	   		  values(sysdate,GPMS_Interface.lNewGPMSTitle,GPMS_Interface.lOldTitleCT,
			  				'Title Changed');																												  			  				    
	end	;						   		  	   																				 																			

  END LOOP;     commit;      

begin
update constituent_titles a set a.CONSTITUENT_TITLE = ( select distinct b.gpms_title from gpms_title b where b.GPMS_TITLE <> a.CONSTITUENT_TITLE

                  and a.GPMS_NO = b.GPMS_NO ) where a.GPMS_NO in (select b.GPMS_NO from gpms_title b where a.GPMS_NO = b.GPMS_NO and

                                                                                                                         b.GPMS_TITLE <> a.CONSTITUENT_TITLE);

end;
commit;

/***********/
/* PROCESS 9 */
/***********/

  begin
  open NEW_OLD_GPMSNo_CT_cursor;
  EXCEPTION
  WHEN OTHERS THEN
            dbms_output.put_line('NEW_OLD_GPMSNo_CT_cursor cursor in exception sto_selcnt set to 1' || sqlcode || sqlerrm);   END;   FETCH NEW_OLD_GPMSNo_CT_cursor INTO
GPMS_Interface.lGPMSTitleCT,GPMS_Interface.lNewGPMSNoCT, GPMS_Interface.lOldGPMSNoCT ;  

  IF NEW_OLD_GPMSNo_CT_cursor%NOTFOUND THEN

                dbms_output.put_line('NEW_OLD_GPMSNo_CT_cursor cursor NOT FOUND sto_selcnt set to 1' || sqlcode || sqlerrm);   END IF;   WHILE ( NEW_OLD_GPMSNo_CT_cursor%FOUND) LOOP   BEGIN
    begin

  	   insert into title_changes(DATE_OF_CHANGE,TITLE,NEW_VALUE,OLD_VALUE,TYPE_OF_CHANGE)
	   		  values(sysdate,GPMS_Interface.lGPMSTitleCT,GPMS_Interface.lNewGPMSNoCT,GPMS_Interface.lOldGPMSNoCT,
			  				'GPMS_No Changed');
	end;																												  			  				    

  END LOOP;      commit;

  begin
  update constituent_titles a set a.GPMS_NO = ( select distinct b.GPMS_NO from gpms_title b where b.GPMS_TITLE = a.CONSTITUENT_TITLE

                  and a.GPMS_NO <> b.GPMS_NO ) where a.GPMS_TITLE in (select b.GPMS_TITLE from gpms_title b where a.GPMS_NO <> b.GPMS_NO and

                                                                                                                         b.GPMS_TITLE = a.CONSTITUENT_TITLE );  

  end;
  commit;
 end;
END GPMS_Interface; Received on Mon Aug 06 2001 - 20:25:00 CDT