Home » SQL & PL/SQL » SQL & PL/SQL » can i put "set serveroutput on" inside the procedure ?
can i put "set serveroutput on" inside the procedure ? [message #244523] Wed, 13 June 2007 03:13 Go to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
Hi All,


can i put "set serveroutput on" inside the procedure ?

if not what will be the alternate solution for this?

Regards,
Dinesh
Re: can i put "set serveroutput on" inside the procedure ? [message #244527 is a reply to message #244523] Wed, 13 June 2007 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it is a SQL*Plus command not a PL/SQL statement.

What do you want to achieve?

Please always post your Oracle version (4 decimals).

Regards
Michel
Re: can i put "set serveroutput on" inside the procedure ? [message #244716 is a reply to message #244527] Wed, 13 June 2007 22:20 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: can i put "set serveroutput on" inside the procedure ? [message #244740 is a reply to message #244523] Thu, 14 June 2007 00:41 Go to previous message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
Thanx for valuable reply
Previous Topic: materialized view refresh part of a bigger transaction
Next Topic: Trigger Related Questions
Goto Forum:
  


Current Time: Fri Dec 09 13:20:16 CST 2016

Total time taken to generate the page: 0.14948 seconds