Home » SQL & PL/SQL » SQL & PL/SQL » output in pl/sql
output in pl/sql [message #242889] Tue, 05 June 2007 07:39 Go to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Hi all,

The following is an learning exercise for me, so no hurry in the answer.
declare
tn varchar(100);
cursor c1 is select table_name from dba_tables where owner='EPPACCEPT' and rownum<100;
begin
  dbms_output.enable(100000);
  open c1;
  loop
    fetch c1 into tn;
    exit when c1%notfound;
    dbms_stats.gather_table_stats('EPPACCEPT', tn, cascade=>true, method_opt=>'for all indexed columns size auto');
    dbms_output.put_line(tn||' done');
  end loop;
end;
/


This code selects all tables in a schema. Then for every table in the cursor a gather stats will be executed.

This all works fine.

However.... dbms_output show the output as one buffer when oracle finishes this code (documented behaviour).
What I want...when statistics for a table have been gathered I want to see it immediatley. In that way I can see whether something happens, and how far the process is.

From documentation I understand that dbms_output will not solve this.

Can someone point me to some documentation or packages which may can help me solve my issue.

Best regards,

Martijn Bos
Re: output in pl/sql [message #242891 is a reply to message #242889] Tue, 05 June 2007 07:42 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
above is on oracle 9.2.0.7 (64 Bits) on AIX
Re: output in pl/sql [message #242894 is a reply to message #242889] Tue, 05 June 2007 07:48 Go to previous messageGo to next message
aciolac
Messages: 242
Registered: February 2006
Senior Member
1)As i know, it is impossible to see output of database till the programs end.
2)To see what statistics are already gathered, user the next select:
select table_name from dba_tables where owner='EPPACCEPT'
order by last_analyzed desc
Re: output in pl/sql [message #242899 is a reply to message #242894] Tue, 05 June 2007 08:04 Go to previous messageGo to next message
techno
Messages: 44
Registered: October 2003
Member

You can use DBMS_PIPE supplied package to acheive this but not in the same session. You can pack the message after each table's statistics collection and the same can be read in another session.

Regards

Techno
Re: output in pl/sql [message #242902 is a reply to message #242889] Tue, 05 June 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a pipelined function instead:
create or replace type stringArray as table of varchar2(255)
/
create or replace function analyze return stringArray PIPELINED
as
  pragma autonomous_transaction;
  tn varchar(100);
  cursor c1 is select table_name from dba_tables where owner='EPPACCEPT' and rownum<100;
begin
  open c1;
  loop
    fetch c1 into tn;
    exit when c1%notfound;
    dbms_stats.gather_table_stats('EPPACCEPT', tn, cascade=>true, 
                                  method_opt=>'for all indexed columns size auto');
    pipe row(tn||' done');
  end loop;
  return;
end;
/
set arraysize 1
select * from TABLE(analyze());

Regards
Michel
Re: output in pl/sql [message #242903 is a reply to message #242894] Tue, 05 June 2007 08:07 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Thanks for your reply.

at 1)
I was allready afraid someone was going to say that Smile

at 2)
Yep. I understand what you say. For this case you can indeed query a view to see the progress. But I want to know how I can do it in the code "as it runs". As I said it's a learning experience to me. So it might very well be impossible.
Re: output in pl/sql [message #242907 is a reply to message #242899] Tue, 05 June 2007 08:12 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Thanks,

Never used that package before (I'm not a developer. I'm a development interested dba), so I have something to explore.

I indeed think that it's not quite what I hoped for. But seeing the replies I think the exact thing that I want in not posible
Re: output in pl/sql [message #242909 is a reply to message #242902] Tue, 05 June 2007 08:16 Go to previous message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Thanks,

I think I see what you are doing.

I'm going to try it right now to figure out the pipelined function.

Yet again another thing I have not seen before Embarassed
Previous Topic: Error with a function
Next Topic: Host String,Username,Password
Goto Forum:
  


Current Time: Mon Dec 05 03:11:16 CST 2016

Total time taken to generate the page: 0.05048 seconds