Re: Simple question..I am running a stored procedure from SQL PLUS

From: Manoj Nair <debo_nair_at_yahoo.com>
Date: 8 Aug 2001 10:33:06 -0700
Message-ID: <92a0e580.0108080933.4f201c4a@posting.google.com>

YES GUYS I MEANT I USED SET SERVEROUTPUT ON and also SET SERVEROUTPUT ON SIZE 1000000. BUT IT STILL DOESN'T WORK....I TRIED TO SET ALL OTHER PARAMETERS IN SET TO ON BUT NO LUCK....YESTERDAY IT WORKED JUST ONCE WHEN I DID A SET SERVEROUTPUT ON . Like Nuno mentions it looks like serveroutput is turned off arbitrarily...I am using NT 4.0 ....

code is here

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.CONSTITUENT_TITLE from constituent_titles a, gpms_title b where b.GPMS_TITLE <> a.CONSTITUENT_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.CONSTITUENT_TITLE = b.GPMS_TITLE;

BEGIN
  begin

        dbms_output.put_line('start process 5' );   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;   dbms_output.put_line('after opeing cursor and exception');   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 ');   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;   END LOOP;
  commit;
  close NEW_OLD_TITLE_cursor;

  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;
 END GPMS_Interface;
Pete Finnigan <pete_at_peterfinnigan.demon.co.uk> wrote in message news:<nsZ8jiADuTc7EwCg_at_peterfinnigan.demon.co.uk>...

> Hi
> 
> No i meant what i typed, its worth setting the default buffer size to
> 1000000, the maximum, otherwise it remains at the default size of 20000
> bytes.
> 
> You can do either syntax, but if your output exceeeds the minimum it
> will just stop.
> 
> cheers
> 
> Pete
> Pentest Limited
> 
> In article <99ab87b0.0108080450.6de41c62_at_posting.google.com>, Tom Best
> <tombest_at_firstusa.com> writes
> >I hope you mean you did:
> >
> >   set serveroutput on
> >
> >right?
> >
> >debo_nair_at_yahoo.com (Manoj Nair) wrote in message news:<92a0e580.0108071547.1fc7
> >37a4_at_posting.google.com>...
> >> so i do a SQL> EXEC my_sp..I have dbms_output statements in code and i
> >> have also set serverout on ..but it doesn't print the messages what am
> >> i missing?

Received on Wed Aug 08 2001 - 12:33:06 CDT