how to show the dbms_output.put_line block which is there in if / end if condition

Question

SET SERVEROUTPUT ON;
accept x char prompt 'Please enter y/n'

DECLARE
    user_input VARCHAR2(10);
BEGIN
    user_input := '&x';
    IF user_input = 'y' THEN
        dbms_output.put_line('USER_INPUT is: ' || user_input);
    END IF;
    dbms_output.put_line('excluded accounts as of now:- 123,1234');
END;

I want to see the dbms_output.put_line('USER_INPUT is: ' || user_input) in serveroutput.

result

USER_INPUT is: y

Answer 1

That's what your code does. Though, you can't avoid the 2nd dbms_output.put_line as it isn't constrained by any condition.

Tool you should use is SQL*Plus or SQL Developer; I'm not sure other tools will understand accept part of that code.

I'm entering y:

SQL> SET SERVEROUTPUT ON;
SQL> accept x char prompt 'Please enter y/n '
Please enter y/n y
SQL>

PL/SQL block displays it:

SQL> DECLARE
  2    user_input VARCHAR2(10);
  3  BEGIN
  4    user_input := '&x';
  5    IF user_input = 'y' THEN
  6       dbms_output.put_line('USER_INPUT is: ' || user_input);
  7    END IF;
  8    dbms_output.put_line('excluded accounts as of now:- 123,1234');
  9  END;
 10  /
old   4:   user_input := '&x';
new   4:   user_input := 'y';
USER_INPUT is: y
excluded accounts as of now:- 123,1234

PL/SQL procedure successfully completed.

SQL>