OraFAQ Forum: SQL & PL/SQL » exit when cursor%notfound is giving buffer overflow if not being used in anonymous block (merged)

Home » SQL & PL/SQL » SQL & PL/SQL » exit when cursor%notfound is giving buffer overflow if not being used in anonymous block (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

exit when cursor%notfound is giving buffer overflow if not being used in anonymous block (merged) [message #679463]

Tue, 03 March 2020 04:47

sfksuperman
Messages: 20
Registered: February 2020

Junior Member

Hi, I was solving one question(mentioned below) and in the code, when i comment out the EXIT WHEN DEPTNAME_CUR%NOTFOUND;

line inside the loop, then the output gets printed unlimited times randomly and finally it gives "buffer overflow" error. SCREENSHOT ATTACHED
But when i uncomment that line, results are okay. Please take a look at my question and tell me why is it important to write that EXIT line in the code and why it produces too many unwanted lines. Can't we skip that line or is it not optional?

Write a plsql anonymous block to print the names of departments which has employee
having the designation as 'SE'.
If no record in the Department table fulfilling the given conditions found,
code should print the message "No record found".

--drop table departments cascade constraints purge;
--drop table employees cascade constraints purge;
create table DEPARTMENTS
(
  dept_id             NUMBER(5) primary key,
  dept_name         VARCHAR2(20),
  employee_strength NUMBER(4) not null
);

insert into departments (DEPT_ID, DEPT_NAME, EMPLOYEE_STRENGTH) values (100, 'IT', 76);
insert into departments (DEPT_ID, DEPT_NAME, EMPLOYEE_STRENGTH) values (101, 'HR', 15);
insert into departments (DEPT_ID, DEPT_NAME, EMPLOYEE_STRENGTH) values (102, 'Finance', 41);
insert into departments (DEPT_ID, DEPT_NAME, EMPLOYEE_STRENGTH) values (103, 'Marketing', 36);
insert into departments (DEPT_ID, DEPT_NAME, EMPLOYEE_STRENGTH) values (104, 'RMG', 88);
insert into departments (DEPT_ID, DEPT_NAME, EMPLOYEE_STRENGTH) values (105, 'Production', 58);
insert into departments (DEPT_ID, DEPT_NAME, EMPLOYEE_STRENGTH) values (106, 'Sales', 92);

create table EMPLOYEES
(
  e_id        NUMBER(5) primary key,
  e_name      VARCHAR2(20),
  designation VARCHAR2(20),
  d_id       NUMBER(5)
);
alter table EMPLOYEES
  add constraint FK_DE_ID foreign key (D_ID)
  references DEPARTMENTS (DEPT_ID);

insert into employees values(0,'Siri','SE',102);
insert into employees values(1,'Lata','ASE',102);
insert into employees values(2,'Manu','SE',100);
insert into employees values(3,'Aisha','Manager',101);
insert into employees values(4,'Mukesh','ASE',103);
insert into employees values(5,'Ramya','Trainee',100);
insert into employees values(6,'Jack','Consultant',103);
insert into employees values(7,'Manu','SE',102);
insert into employees values(8,'Ram','Consultant',101);
insert into employees values(9,'Manu','Trainee',101);
insert into employees values(10,'Nitin','Consultant',102);
insert into employees values(11,'Mukesh','Manager',106);
insert into employees values(12,'Amy','ASE',106);
insert into employees values(13,'Lata','SE',106);
insert into employees values(14,'Rahul','SE',105);
insert into employees values(15,'Asha','Trainee',105);
insert into employees values(16,'Rahul','Trainee',102);
insert into employees values(17,'Siri','SE',105);
insert into employees values(18,'Ravi','Trainee',105);
insert into employees values(19,'Shobha','Manager',101);
insert into employees values(20,'Asha','Manager',105);
insert into employees values(21,'Jack','Consultant',104);
insert into employees values(22,'Shobha','SE',103);
insert into employees values(23,'Aman','Consultant',106);
insert into employees values(24,'Hari','Trainee',102);
insert into employees values(25,'Asha','SE',100);
insert into employees values(26,'Manu','Manager',102);
insert into employees values(27,'Shubham','Consultant',100);
insert into employees values(28,'Amy','Manager',105);
insert into employees values(29,'Ram','ASE',101);
insert into employees values(30,'Aliya','SE',106);
insert into employees values(31,'Mohan','SE',103);
insert into employees values(32,'Shobha','SE',101);
insert into employees values(33,'Shubham','SE',105);
insert into employees values(34,'Aman','Manager',105);
insert into employees values(35,'Ram','SE',105);
insert into employees values(36,'Mohan','Trainee',100);
insert into employees values(37,'Aman','SE',102);
insert into employees values(38,'Manu','ASE',105);
insert into employees values(39,'Gita','SE',103);
insert into employees values(40,'Karan','ASE',100);
insert into employees values(41,'Manu','ASE',105);
insert into employees values(42,'Aisha','Consultant',100);
insert into employees values(43,'Shubham','SE',102);
insert into employees values(44,'Maya','Trainee',103);
insert into employees values(45,'Karan','ASE',100);
insert into employees values(46,'Maya','Trainee',100);
insert into employees values(47,'Amy','Consultant',101);
insert into employees values(48,'Rahul','ASE',100);
insert into employees values(49,'Nitin','Consultant',101);

declare
  cursor deptname_cur is
    select d.dept_name
      from departments d, employees e
     where d.dept_id = e.d_id
       and e.designation = 'SE';
  fetch_deptname departments.dept_name%type;
begin
  open deptname_cur;
  loop
    fetch deptname_cur
      into fetch_deptname;
    EXIT WHEN DEPTNAME_CUR%NOTFOUND; ---THIS LINE IS VVI, otherwise the loop will continue to print and buffer will overflow.
    dbms_output.put_line(fetch_deptname);
  end loop;
  close deptname_cur;
exception
  when no_data_found then
    dbms_output.put_line('No record found');
end;
/

Attachment: exit when cursor%notfound.png
(Size: 48.80KB, Downloaded 1404 times)