How can I see my out put while running a script ?

[TekMem]

I am using oracle 8.1
when I run following script

Declare
projectcode          varchar2(20);
ProjName               VARCHAR2(100);
err_num           NUMBER		 := SQLCODE;
err_msg           VARCHAR2(512)  := SQLERRM;
cursor mycursorDOC is
select q.O_PROJECTNAME, trim(q.O_PROJECTCODE) FROM qaqcdev Q  ;
BEGIN
open mycursorDOC;
loop 
FETCH mycursorDOC INTO ProjName,projectcode;
IF(mycursorDOC%FOUND)
THEN
update myproj T set T.O_PROJECTNAME = ProjName
where T.O_DEFAULTSTOR = 31 and trim(T.O_PROJECTname)=trim(projectcode)
and o_projectname like 'dms%';
dbms_output.put_line(projectcode||ProjName);
END IF;
EXIT WHEN mycursorDOC%NOTFOUND;
 end loop;
close mycursorDOC;
EXCEPTION
	WHEN OTHERS
	THEN
		dbms_output.put_line(err_num || ':' || err_msg);
END;
/

some of the fields gets updated whiles other remain the same how do I see the fields while it is updating
Is it dbms_output ?

Thanks.

 

[Dagon]

set serveroutput on size 9999999

begin
..
dbms_output.put_line('whatever you want');
...
END;

 

[Dagon]

Got a bit carried away with the 9s. Maximum size is 1000000:

set serveroutput on size 1000000

 

[TekMem]

1. I tried going in Options -> severoutput -> value Custom -> select on radio button -> on writing 1000000 in the textbox on radio button gets deselected.
does not work ?
will it shows each field while it gets updated?

2. do we have to check line size as well ?

thanks.

 

[KenCunningham]

Have you tried 999999 instead?

 

[TekMem]

On button gets deselected while writing valule(1000000 or 999999) in the text box below it.
and dialog box says on or off should be selected.

Thanks

 

[SantaMufasa]

Whoa, folks...dbms_output.put_line, although it assembles output whilst the code is running, does not actually show you the data output until the PL/SQL block reaches its executional end.

If you wish to see data as it actually materialises, then you can use PL/SQL's utl_file package. Output from that package writes to disk as PL/SQL encounters the commands. Remember, however, that the output from utl_file is always to the server-side machine, not to the client-side machine.

Let us know if I misinterpretted the issues here.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Dagon]

I don't know if the set environment menu option in SQL*Plus works. Personally, I never bother with it: I always set things on the command line. Just type the "set serveroutput on" at the command line and it should work.

It is correct that the output won't be written out until the job completes, although most of the time this is OK. The other option if you want to see output as it's running would be to write to a table using an autonomous transaction.

 

[SantaMufasa]

Agreed, Dagon, but you cannot actually "monitor" output to a table that PL/SQL is writing via the autonomous transaction (unless you COMMIT after each INSERT). But with the utl_file output, you can actually "see" the output as it writes out, before any commits and before the end of the logical code block. That's why I proposed that alternative.

(Note: depending upon the operating system, to monitor utl_file output as it writes, you may need to place inside the write loop, a utl_file CLOSE and a utl_file OPEN with "A" (append). But under the circumstances, that may be a small price to pay for the ability to monitor output results while they are actually writing.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[TekMem]

Thanks Guys !

Trying on command line is a good Idea and also buying low-cost, remote Database Administration services.

But what is the purpose of setting serveroutput on size and why can't I set it on with the value ?

 

[SantaMufasa]

...what is the purpose of setting serveroutput on size and why can't I set it on with the value ?

1) If you do not establish an explicit SIZE for the SERVEROUTPUT buffer, then the default SIZE is 2000 bytes, which is usually too small to accomplish outputting of any decent size.

2) Your syntactical options for establishing a buffer size of 1M are:

(from SQL*Plus): set serveroutput on size <some value to 1000000 maximum>

or

(from PL/SQL): dbms_output.enable(<some value to 1000000 maximum>);

If, as you suggested, you try to specify Dagon's command suggestion without the "SIZE" syntax, then you receive an error:

set serveroutput on 1000000
SP2-0158: unknown SET option "1000000"

(specifying as Dagon suggests):
set serveroutput on size 1000000
(no error message results)

Let us know if this answers your questions

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[TekMem]

Thanks a lot !