|
|
Re: can i put "set serveroutput on" inside the procedure ? [message #244716 is a reply to message #244527] |
Wed, 13 June 2007 22:20   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
If serveroutput is OFF, then DBMS_OUTPUT is probably disabled, which means that calls to dbms_output.put_line will be effectively ignored.
However you could enable dbms_output from within PL/SQL using DBMS_OUTPUT.ENABLE()
When control returns to SQL*Plus, the output will not display automatically because SERVEROUTPUT is still off.
In order to display it, you could set SERVEROUTPUT ON and then it would display on the next command. Alternatively (but much more work) you could write a pipelined function to dequeue to messages and then extract them with a SELECT statement.
SQL> set serveroutput OFF
SQL>
SQL> begin
2 dbms_output.enable();
3 dbms_output.put_line('Hi');
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> set serveroutput on
SQL> exec null
Hi
PL/SQL procedure successfully completed.
SQL>
or
SQL> CREATE OR REPLACE FUNCTION DUMP_DBMS_OUTPUT
2 RETURN sys.dbms_debug_vc2coll PIPELINED IS
3 ln VARCHAR2(2000);
4 sts NUMBER;
5 BEGIN
6 LOOP
7 dbms_output.get_line(ln, sts);
8 EXIT WHEN sts <> 0;
9 PIPE ROW (ln);
10 END LOOP;
11 END;
12 /
Function created.
SQL>
SQL>
SQL> set serveroutput off
SQL> DECLARE
2 i number;
3 BEGIN
4 dbms_output.enable();
5 FOR i IN 1 .. 10 LOOP
6 dbms_output.put_line('Line ' || i);
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT *
2 FROM table(DUMP_DBMS_OUTPUT())
3 /
COLUMN_VALUE
--------------------------------------------------------------------------------
Line 1
Line 2
Line 3
Line 4
Line 5
Line 6
Line 7
Line 8
Line 9
Line 10
10 rows selected.
SQL>
Ross Leishman
|
|
|
|