Re: SERVEROUTPUT Status

From: Igor Laletin <ilaletin_at_usa.net>
Date: 10 Feb 2004 16:27:48 -0800
Message-ID: <f9226414.0402101627.768d0466@posting.google.com>

ben_p33_at_hotmail.com (ben p) wrote in message news:<fdac5af3.0402090817.50f41c2a_at_posting.google.com>...
> Hello there,
> does anybody know a way I can check the value of serveroutput from
> within a block ? In sqlplus you can do 'show serveroutput' but i cant
> find a way of getting this info in some plsql. Im trying to write a
> function to work out if serveroutput is on / off.
> Cheers
> Ben

Ben,

First, I don't really understand why you would need it. I can think about three possible reasons:
1. You want to be sure output is enabled.

Just user dbms_output.enable.

2. You use this info to execute different parts of your code.

It doesn't work. Serveroutput is sqlplus system variable and may be out of sync with reality:

SQL> show serveroutput
serveroutput OFF
SQL> exec dbms_output.enable;

PL/SQL procedure successfully completed.

SQL> show serveroutput
serveroutput OFF

The only right way is to get it from the package itself but it's not possible (see Pete's message).

3. You have something else in mind.

AFAIK serveroutput (or any other system variable) can be only accessed via set/show commands. You need to put its value in substitution variable or bind variable to be able to access it in pl/sql block.

Put the following in, say, get_serverout_state.sql:

rem -----------------------------------

set feedback off
set termout off

spool serveroutput.sql

prompt define so_line
prompt column so_line new_value so_line noprint

prompt select '
show serveroutput
prompt ' so_line from dual
prompt /

spool off

variable so_line   varchar2(80)
variable so_state  varchar2(3)
variable so_size   number
variable so_format varchar2(20)

@@serveroutput.sql

exec :so_line := '&so_line';

declare

l_state_str varchar2(100) := 'serveroutput '; -- note space at the end of the string

	l_size_str    varchar2(100) := 'size ';
	l_format_str  varchar2(100) := 'format ';

	function get_value ( p_line IN varchar2, p_name IN varchar2 ) return
varchar2 is
		l_len         integer := length(p_line);	-- there is CR at the end
of the line
		l_pos1        integer;
		l_pos2        integer;
	begin
		l_pos1 := instr( p_line, p_name);
		if l_pos1 = 0 then
			return NULL;
		end if;
		l_pos1 := l_pos1 + length(p_name);
		l_pos2 := instr( p_line, ' ', l_pos1 );
		if l_pos2 = 0 then
			l_pos2 := l_len;
		end if;
		return substr(p_line, l_pos1, l_pos2 - l_pos1);
	end get_value;

begin
	:so_state  := get_value(:so_line, l_state_str);
	:so_size   := to_number(get_value(:so_line, l_size_str));
	:so_format :=  get_value(:so_line, l_format_str);

end;
/

set feedback 1
set termout on

rem -----------------------------------

This script defines four bind varibles with info about sqlplus serveroutput setting:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> show serveroutput
serveroutput ON size 50000 format WORD_WRAPPED SQL> @get_serverout_state
SQL> print

SO_STATE

SO_SIZE

50000

SO_FORMAT

WORD_WRAPPED

SQL> set serveroutput off
SQL> @get_serverout_state
SQL> print

SO_STATE

OFF SO_SIZE

<null>

SO_FORMAT

<null>

SQL> Regards,
Igor Received on Tue Feb 10 2004 - 18:27:48 CST