From: debo_nair@yahoo.com (Manoj Nair)
Newsgroups: comp.databases.oracle.server
Subject: Re: Simple question..I am running a stored procedure from SQL PLUS
Date: 8 Aug 2001 10:33:06 -0700
Organization: http://groups.google.com/
Lines: 127
Message-ID: <92a0e580.0108080933.4f201c4a@posting.google.com>
References: <92a0e580.0108071547.1fc737a4@posting.google.com> <99ab87b0.0108080450.6de41c62@posting.google.com> <nsZ8jiADuTc7EwCg@peterfinnigan.demon.co.uk>
NNTP-Posting-Host: 64.14.248.62
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 997291987 31431 127.0.0.1 (8 Aug 2001 17:33:07 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2001 17:33:07 GMT


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@peterfinnigan.demon.co.uk> wrote in message news:<nsZ8jiADuTc7EwCg@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@posting.google.com>, Tom Best
> <tombest@firstusa.com> writes
> >I hope you mean you did:
> >
> >   set serveroutput on
> >
> >right?
> >
> >debo_nair@yahoo.com (Manoj Nair) wrote in message news:<92a0e580.0108071547.1fc7
> >37a4@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?