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

Hi

I cannot see anything obviously wrong with this... you could try dbms_output.enable(1000000); as Joe suggests.

If dbms_output is tempramental on NT try changing to use UTL_FILE instead. you need to do a bit extra than when using dbms_output, but not much

create or replace procedure....
...

        fprt utl_file.file_type;
..
begin
....

        fptr:=utl_file.fopen('directory','filename','A');

....

• output like dbms_output utl_file.put_line(fptr,'string as per dbms output'); .... utl_File.fclose(fptr); .... exception

end;

you also need to add the line

utl_file_dir="directory name" to your init.ora and bounce the database

cheers

Pete Finnigan
Pentest Limited

In article <92a0e580.0108080933.4f201c4a_at_posting.google.com>, Manoj Nair <debo_nair_at_yahoo.com> writes
>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_CHAN
>GE)
> values(sysdate,GPMS_Interface.lNewGPMSTitle,GPMS_Inter
>face.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:<nsZ8jiADuT
>c7EwCg_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?
 

-- 
Pete Finnigan