Home oracle How to include in dbms_output routine?

How to include in dbms_output routine?




There is a package and a procedure in it. In it I want to use the command:

dbms_output.put_line ('some text');

Where should you place the set serveroutput on command to enable the output?

Tried inside the body of the procedure, but doesn’t work, I get the error:

Error (5,9): PL / SQL: SQL Statement ignored

Answer 1, authority 100%

SET is a client application command that sets its internal system variable SERVEROUTPUT {ON | OFF} . This command is supported by SQL * Plus, SQL Developer, and a few others, but is not part of the PL / SQL or SQL syntax .
It must be executed before running PL / SQL block:

create or replace procedure proc is
  dbms_output.put_line ('line 1');
  dbms_output.put_line ('line 2');
set serveroutput on
exec proc
line 1
line 2

What SET SERVEROUTPUT ON does “under the hood” is shown in the following example.
Turn off the output by resetting the SERVEROUTPUT OFF variable, and do everything that was done implicitly with SERVEROUTPUT ON , now manually. First, re-enable the output:

set serveroutput off
exec dbms_output.enable ();

Let’s start the procedure:

exec proc
PL / SQL procedure successfully completed.

During the execution of the procedure, each call to dbms_output.put_line ('some text'); will add one line to the internal array of strings, which is stored in the scope of session variables. There will be no output to the console , either at runtime or after completion. It is up to the client to get the contents of the internal array of strings and print it to the console. It looks something like this:

var output varchar2
  lines dbms_output.CHARARR;
  nl int: = 100;
  dbms_output.get_lines (lines, nl);
  for i in 1..nl loop
    : output: =: output || lines (i) || chr (10);
  end loop;
PL / SQL procedure successfully completed.
print output
line 1
line 2

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions