Why the output is printing twice while executing loop in plsql?

Question

set serveroutput on;
Declare
    d_name varchar(25);
    cursor dep_cur is
    select distinct department_name from department;
BEGIN 
    dbms_output.put_line('Department Names are :');
    open dep_cur;
    
    LOOP
        fetch dep_cur
            into d_name;
              
        exit when dep_cur%notfound;    
        dbms_output.put_line(d_name);  
    END LOOP;
    close dep_cur;
END;
/

output:

Department Names are :                                                          
ADMIN                                                                           
TESTING                                                                         
DEVELOPMENT                                                                     
Department Names are :                                                          
ADMIN                                                                           
TESTING                                                                         
DEVELOPMENT

Expected output:

Department Names are :     
ADMIN
DEVELOPMENT

Your example in the question [working as expected](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=95a1dde84b7be40eff911a3aba72e54e) — 0xdb, Mar 25 '21 at 08:50

Alexander Gusev · Accepted Answer · 2021-03-25T08:48:51.883

1

This is because you have an anonymous BEGIN-END block with a semicolon and forward slash at the end. In this case the slash executes the code block once again.

Useful resources:

Read more about what is the purpose of slash in Oracle documentation
Read more about when to use semicolon or slash in this thread

edited Mar 25 '21 at 08:48

answered Mar 25 '21 at 07:27

Alexander Gusev

295
1
9

it worked after removing slash but removing semicolon and using only slash was giving an error – Ishmeet Kaur Mar 25 '21 at 07:37
Ok, thanks. Updated my answer by removing that sentence :) – Alexander Gusev Mar 25 '21 at 08:49

Why the output is printing twice while executing loop in plsql?

1 Answers1