CODEWITHSUNDEEP
sqloracle-databaseplsqllogic
J Ben
J Ben

Reputation: 129

dbms_output.put_line not working in this simple nested if

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE

v_idno          oilcust1.custid%TYPE :=&input_idno;
v_tank          oilcust1.tankcapacity%TYPE;
v_name          oilcust1.custname%TYPE;
v_state         oilcust1.custstate%TYPE;
v_city          oilcust1.custcity%TYPE;
BEGIN
        SELECT custid, custname, tankcapacity, custstate, custcity INTO v_idno, v_name, v_tank, v_state, v_city
                FROM oilcust1
                WHERE custid = v_idno;
        IF v_state = 'NC' THEN
           IF v_tank > 500 THEN
                IF v_city = 'Asheville' THEN
                                DBMS_OUTPUT.PUT_LINE(v_name || ' is a in an immediate city
                                and holds an above average tank.');
                ELSE
                                DBMS_OUTPUT.PUT_LINE(v_name || ' is not of priority at this time.');
                END IF;
            END IF;
         END IF;

END;
/
SET VERIFY ON
SET SERVEROUTPUT OFF

What am I doing wrong? It's working on other programs so I'm having a tough time pinpointing exactly what's blocking it to work. The procedure runs successfully too. Any thoughts? Thank you for any help. I'm sure I'm just missing something very obvious.

When I run it:

SQL> @iftest
Enter value for input_idno: 1111

PL/SQL procedure successfully completed.

No DBMS line.

Upvotes: 0

Views: 884

Answers (1)

Utsav
Utsav

Reputation: 8103

I tried to replicate the same and problem is only because of set serveroutput on for the session. See the code below and try to replicate your problem using a Minimal, Complete, and Verifiable example

File: p1.sql

declare
v_empno integer :=&input_empno;
v_ename varchar2(10);
begin
select ename into v_ename from emp where empno = v_empno;
 if v_empno > 7700 then
    if v_ename is not null then
        dbms_output.put_line(v_ename);

     else
        dbms_output.put_line('empno doesn''t exist');
    end if;
 else
 dbms_output.put_line('please enter empno < 7700');
 end if;
end;
/

Execution without set serveroutput on;

C:\Users\Utsav>sqlplus ***/***@localhost

SQL*Plus: Release 11.2.0.2.0 Production on Sat May 13 12:21:35 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> @C:\ora\p1.sql
Enter value for input_empno: 7782
old   2: v_empno integer :=&input_empno;
new   2: v_empno integer :=7782;

PL/SQL procedure successfully completed.

After serveroutput on

SQL> set serveroutput on;
SQL> @C:\ora\p1.sql
Enter value for input_empno: 7782
old   2: v_empno integer :=&input_empno;
new   2: v_empno integer :=7782;
CLARK

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions