DBMS_OUTPUT.PUT_LINE not printing

DBMS_OUTPUT.PUT_LINE not printing – The dbms_output.put_line command is used to print the output of PL/SQL code in the console window in oracle sql developer and sql plus. The dbms_output.put_line command will not be working or printing in the sql developer or sqlpqus console window by default. The PL/SQL code execute and print PL/SQL procedure successfully completed. but no output.

The oracle server output command should be used to enable the dbms_output.put_line command for printing the PL/SQL code in the console window. If the server output is enabled, the dbms_output.put_line command will print the output of PL/SQL code in the console window.

The dbms_output.put_line command in PL/SQL is used to print the output in the console window to debug the sql command outputs. It’s impossible to debug PL/SQL code if the dbms_output.put_line command doesn’t work. In an Oracle database, the dbms_output.put_line command works as a logger.

The failure of the dbms output.put line command to print can be caused by a number of reasons. When using the dbms output.put line command, either the server output is not enabled or there is a bug in the code. Until executing the PL/SQL command, make sure that the server output is turned on. If there is a code error, it should be fixed.



Problem

If the dbms_output.put_line command is used to print a value in the console window, the output is not printed in the console window. The example below illustrates the command dbms_output.put_line used in the PL/SQL code that is not printed in the console window.

begin
    DBMS_OUTPUT.PUT_LINE('yawin');
end;

Output

PL/SQL procedure successfully completed.


Solution 1

The server output is disabled by default in the oracle database. If the server output is enabled in the database and if you run the same PL/SQL code in the oracle database, the output will be printed in the console window. The output of PL/SQL code should be redirected to the console window using the server output command.

set serveroutput on;

OR

set serveroutput on size 30000;

The set serveroutput on/off command is used to allow or disable the output to print in the console window. In the Oracle database, the server output is turned off by default. Until executing the PL/SQL code, make sure the server output is set to “on.” The following example illustrates how to set the server output and print it in the console window.

set serveroutput on size 30000;
begin
    DBMS_OUTPUT.PUT_LINE('yawin');
end;

Output

yawin
PL/SQL procedure successfully completed.


Solution 2

Most tools don’t set up a buffer for dbms output to write to by default, and they don’t want to read from it after code runs. The majority of tools, on the other hand, are capable of doing so. To open the DBMS Output window in SQL Developer, go to View -> DBMS Output, then press the green plus icon to enable DBMS Output.

In other Oracle editors, look for solutions that are similar to these. If you enable the option, you can see the output of the dbms output. put line command.



Solution 3

If you are printing a variable with a value, it should be printed. If you use the dbms_output. put_line command to print a variable and the variable that has no value, you cannot see the output in the console window. The dbms_output. put_line command is used to print the variable, but it has no print value.

declare
 name varchar(10);
begin
    dbms_output.put_line(name);
end;

Output

PL/SQL procedure successfully completed.

To address this problem, verify that the dbms_output.put_line command is working. To ensure that the dbms_output. put_line command is working, add a constant to it. If the variable has no value, the output will only print the constant string, leaving the variable value blank.

Solution

declare
 name varchar(10);
begin
    dbms_output.put_line('name ' || name);
end;

Output

name 
PL/SQL procedure successfully completed.


Solution 4

The variables can not be quoted if you use quotes in the dbms_output.put_line command. When variables are enclosed in quotes, the variable names are written instead of the variable value. In this case, the variable’s quotes can be removed.

declare
 name varchar(10);
begin
    name:='yawin';
    dbms_output.put_line('name');
end;

Output

name
PL/SQL procedure successfully completed.

The variable ‘name’ is declared as varchar(10) and given the value ‘Yawin’ in the example above. The dbms output.put line command is used to print the name. The value of the variable isn’t writing. Instead, the name of the variable is shown in the console window.

The variable ‘name’ is added to the dbms_output.put_line command without the quotes around it in the example below. In the sql plus or sql developer console window, the dbms_output.put_line command prints the value of the variable.

Solution

declare
 name varchar(10);
begin
    name:='yawin';
    dbms_output.put_line(name);
end;

Output

yawin
PL/SQL procedure successfully completed.



Leave a Reply