Skip to Main Content
  • Questions
  • about dbms_output.put_line and UTL_FILE.PUT_LINE

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, YL..

Asked: August 05, 2002 - 10:05 pm UTC

Last updated: August 09, 2002 - 8:46 am UTC

Version: 7.3.4

Viewed 1000+ times

You Asked

Dear Tom,

Your website is very useful for those developers that work on
oracle. Please Keep it on.

Now, I have below questions to ask you:

1. can i use utl_file.put_line without modifying the init.ora file?
since I have no rights to access the production server.

2. I want to use spo command in below codes, but it does not
work, can u give me some suggestion on that?

**************************************************
spo c:\abc.txt

DECLARE
l_start integer;
BEGIN
l_start := dbms_utility.get_time;
<<mk_loop>>
while (dbms_utility.get_time-l_start < 1000)
loop
dbms_output.put_line('test');
end loop mk_loop;

END;
spo off
***************************************************

My expect result for abc.txt is that, it contains many
"test" in the file.

3. Your showsql.sql is very useful. Now I have modified the script
which use utl_file to suit my case (I will loop the
My_showsql.sql for about 5 minutes to monitor the server
activities.) But, our company policies do not allow me to modify
the init.ora file. Instead, i believe i can use
dbms_output.put_line to replace the utl_file, but it seems it
does not work and the buffer size is a consideration. Do u have
any suggestion on my coding?

Below is the code of My_showsql.sql:
**************************************************************

declare

file_handle UTL_FILE.FILE_TYPE;
file_directory varchar2(30) := '&1';
file_name varchar2(30) := '&2';
v_mode varchar2(5) := '&3';
error_message varchar2(60);
v_now long;
v_username v$session.username%type;
v_sid v$session.sid%type;
v_serial v$session.serial#%type;
v_process v$session.process%type;
v_status v$session.status%type;
l_str long;
v_user long;
v_program long;
v_last_call_et long;
v_logon_time long;
v_current_time long;
v_sql_text long;
l_str2 long;

CURSOR p_cur IS
select username, sid, serial#, process, status
from v$session
where username is not null;

cursor p_cur2 IS
select username||'('||sid||','||serial#||')' user_name,
s.program,
to_char(round(s.LAST_CALL_ET/60,2)) LAST_CALL_ET,
to_char(s.LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
max(decode(v.piece,0,sql_text,' ')) ||
max(decode(v.piece,1,sql_text,' ')) ||
max(decode(v.piece,2,sql_text,' ')) ||
max(decode(v.piece,3,sql_text,' ')) ||
max(decode(v.piece,4,sql_text,' ')) ||
max(decode(v.piece,5,sql_text,' ')) ||
max(decode(v.piece,6,sql_text,' ')) ||
max(decode(v.piece,7,sql_text,' ')) ||
max(decode(v.piece,8,sql_text,' ')) ||
max(decode(v.piece,9,sql_text,' '))
sql_text
from v$session s, v$sqltext_with_newlines v
where s.status in ('ACTIVE', 'INACTIVE')
and rawtohex(s.sql_address) <> '00'
and s.username is not null
and s.sql_address=v.address
group by username||'('||sid||','||serial#||')',
s.program, s.LAST_CALL_ET,
to_char(s.LOGON_TIME,' Day HH24:MI'),
to_char(sysdate,' Day HH24:MI')
order by s.last_call_et;

BEGIN
file_handle := UTL_FILE.FOPEN(file_directory,file_name, v_mode);

SELECT '* '||rpad(TO_CHAR(SYSDATE, 'MM-DD-YYYY 4:MI:SS'),47,' ')
||'*' into v_now FROM DUAL ;

UTL_FILE.PUT_LINE(file_handle, v_now );
UTL_FILE.PUT_LINE(file_handle, '*****');

UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, 'Current Connections: ');
UTL_FILE.PUT_LINE(file_handle, ' ');

l_str := null;

UTL_FILE.PUT_LINE(file_handle, 'User SID_and_Serial Process Status ');
UTL_FILE.PUT_LINE(file_handle, '-------------------- -------------------- --------------- --------');
open p_cur;
LOOP
fetch p_cur into v_username, v_sid, v_serial, v_process, v_status;
l_str:= rpad(v_username,20,' ')||' '||rpad(v_sid||','||v_serial,20,' ')||' '||rpad(v_process,15,' ')||' '||rpad(v_status,8,' ');
UTL_FILE.PUT_LINE(file_handle, rtrim(l_str,chr(10)) );
exit when (p_cur%notfound);
END LOOP;
close p_cur;

UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, 'User(s) Details: ');
UTL_FILE.PUT_LINE(file_handle, ' ');

l_str2 := null;

UTL_FILE.PUT_LINE(file_handle, 'User Program Idle Time Logon Time Current Time SQL Statement ');
UTL_FILE.PUT_LINE(file_handle, '-------------------- -------------------- ------------ -------------------- -------------------- ----------------------------------------------------------------');
open p_cur2;
LOOP
fetch p_cur2 into v_user, v_program, v_last_call_et, v_logon_time, v_current_time, v_sql_text;
l_str2:= rpad(v_user,20,' ')||' '||rpad(v_program,20,' ')||' '||rpad(v_last_call_et,12,' ')||' '||rpad(rtrim(v_logon_time,chr(10)),20,' ')||' '||rpad(v_current_time,20,' ')||' '||v_sql_text;
UTL_FILE.PUT_LINE(file_handle, rtrim(l_str2,chr(10)) );
exit when (p_cur2%notfound);
END LOOP;
close p_cur2;

UTL_FILE.PUT_LINE(file_handle, ' ');

UTL_FILE.FCLOSE(file_handle);

exception
when utl_file.invalid_mode then ......barbarbar...
******************************************************************

I hope you can understand what i am asking and Thank you Very much for your help.

Regards,
Lok

and Tom said...

1) only if you are HAPPY with the current utl_file_dir init.ora setting. Otherwise, if you need to add a path and you are using a version of oracle BEFORE 9i Release 2 -- you will need to not only modify the init.ora (or spfile in 9i) you will need to restart the database.

In 9i Release 2, UTL_FILE uses a "directory" object instead of the utl_file_dir init.ora parameter and these directory objects are created when needed, without touching the parameter files, without restarting.

2) you seem to be missing "set serveroutput on size 1000000". without that, dbms_output goes into the great bit bucket in the sky.

3) umm, that'll collect quite a bit of data don't you think? What you could do would be to set up some database tables and using dbms_job, schedule a stored procedure that inserts into these tables relevant information that you want to collect (don't run a report every 5 minutes, gather some data every 5 minutes). Then, report on the data whenever you want, however you want.

My solution -- don't utl_file every 5 minutes, stuff the data into a table somewhere and report on that.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good solution, Tom.

Lok, August 06, 2002 - 9:19 pm UTC

Thank you very much, Tom. You have given me another possible solution. And your idea is very nice.

But, my boss would like to have a script being run only, without affecting the production setting and creating objects in the database. He said running a script is the
least affective solution.

Tom Kyte
August 07, 2002 - 10:26 am UTC

I agree that running a script is the LEAST effective solution. (believe you made a typo there but it is a true freudian slip!)

Fine, don't use dbms_job, run a script -- use dbms_output and spool the results.

Just follow the TOM and Try to convince ur Boss

Sikandar Hayat Awan, August 06, 2002 - 11:02 pm UTC

I am also inserting performance data into tables for history and the scripts are scheduled. In ur case if u don't have access to init.ora but u must have priviliges of creating tables. Just create a tables and insert data into it and after some time show the results to your boss if he is still not happy with it then read the followings,

1- Boss is always right.
2- If Boss is not right then see rule no 1.

Dear TOM please update this site as on some places u r still on vacation as at the moment on my right side vertical banner.

Robert, August 07, 2002 - 9:51 am UTC

>>please update this site as on some places u r
>>still on vacation as at
>>the moment on my right side vertical banner.

Check your keyboard, looks like some keys are taking a vacation


Pl. Read

Sikandar Hayat Awan, August 07, 2002 - 12:01 pm UTC

Dear Robert my keyboard is working fine but really the text is there and pasting of text is as below,

"I'm on vacation, I won't be responding to anything until after August 4th, 2002

This forum is not intended for additional questions. If you would like to ask additional questions, please go to Ask Tom. "


Tom Kyte
August 07, 2002 - 3:12 pm UTC

What he meant by the keyboard being "not working" is your use of "u" and "r" for real words. (it is a pet peeve of mine, makes it very very hard to read text like that. maybe it works fine on a cell phone where you have 20 characters/line and one or two lines but... in the real world the words are YOU and ARE and YOUR and so on...)

I've taken the "on vacation thing" down from the review section.

another problem

yl.chan, August 08, 2002 - 11:10 pm UTC

Dear Tom, 

I have added "set serveroutput on" in the script, but 
error still occur.

Here is my script (my_script.sql):

set serveroutput on size 999999
spo c:\abcabc.txt
DECLARE
  l_start integer;
  l_no    number;
BEGIN
 l_start := dbms_utility.get_time;
 l_no    := 1000;
  <<mk_loop>>
  while (dbms_utility.get_time-l_start < l_no)  
  loop
    dbms_output.put_line('test : ' );
  end loop mk_loop;
END;
spo off

And below is the error message:
SQL>  @c:\my_script.sql
spo off
*
ERROR at line 27:
ORA-06550: line 27, column 1:
PLS-00103: Encountered the symbol "SPO"

Thank you!

Regards,
Lok 

Tom Kyte
August 09, 2002 - 8:46 am UTC

Oh, simple:

dbms_output.put_line('test : ' );
end loop mk_loop;
END;
/
spo off


you were missing the "/" used to submit the plsql block to the database.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library