Re: why is this SP not compiling at last line?

Manoj Nair wrote:
>
> 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;

Given that we don't have all the tables required, the easiest way would be for you to comment out (almost) all the code, and compile and slowly uncomment code back in and see when things go wrong..

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."