Home » SQL & PL/SQL » SQL & PL/SQL » exit when cursor%notfound is giving buffer overflow if not being used in anonymous block (merged)
exit when cursor%notfound is giving buffer overflow if not being used in anonymous block (merged) [message #679463] Tue, 03 March 2020 04:47 Go to next message
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;
/
exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679465 is a reply to message #679463] Tue, 03 March 2020 04:48 Go to previous messageGo to next message
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;
/

[Updated on: Tue, 03 March 2020 04:50]

Report message to a moderator

Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679466 is a reply to message #679465] Tue, 03 March 2020 05:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
With the type of loop you've used EXIT isn't really optional (it is but leaving it out is a bad idea in 99.99999% of cases).

There are three types of LOOP:
Basic
WHILE
FOR

WHILE loops while a condition is true
FOR loops through a specified set of data and stops at the end.
Basic loops until you specifically tell it stop - with an EXIT condition, or until an error occurs.

You've used Basic.
You should have used FOR.
I suggest you read up on all three.

Also - Explicit cursors (ones which are declared in the declare section and opened with an OPEN statement) never raise no_data_found, so that exception handler is pointless.

[Updated on: Tue, 03 March 2020 05:34]

Report message to a moderator

Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679467 is a reply to message #679466] Tue, 03 March 2020 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also, with the query you've got, if a dept has multiple employees with designation SE that dept will be printed multiple times - probably not what you want.
You should use an IN or EXISTS clause to check employees rather than JOIN to it.

Also read up on JOIN syntax.
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679470 is a reply to message #679467] Wed, 04 March 2020 07:59 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
you can really cleanup your code by doing the following
DECLARE
    Lc_flag   VARCHAR2 (1);
BEGIN
    Lc_flag := 'N';

    FOR Pnt IN (  SELECT D.Dept_name
                    FROM Departments D, Employees E
                   WHERE D.Dept_id = E.D_id AND E.Designation = 'SE'
                GROUP BY D.Dept_name
                ORDER BY D.Dept_name)
    LOOP
        Lc_flag := 'Y';
        DBMS_OUTPUT.Put_line (Pnt.Dept_name);
    END LOOP;

    IF Lc_flag = 'N'
    THEN
        DBMS_OUTPUT.Put_line ('No record found');
    END IF;
END;
/
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679471 is a reply to message #679470] Wed, 04 March 2020 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

PL/SQL knows BOOLEAN datatype, it is a case to use it:
DECLARE
    Lc_flag BOOLEAN := FALSE;
BEGIN
    FOR Pnt IN (  SELECT D.Dept_name
                    FROM Departments D, Employees E
                   WHERE D.Dept_id = E.D_id AND E.Designation = 'SE'
                GROUP BY D.Dept_name
                ORDER BY D.Dept_name)
    LOOP
        Lc_flag := TRUE;
        DBMS_OUTPUT.Put_line (Pnt.Dept_name);
    END LOOP;

    IF NOT Lc_flag
    THEN
        DBMS_OUTPUT.Put_line ('No record found');
    END IF;
END;
/
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679481 is a reply to message #679471] Wed, 04 March 2020 23:59 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
many many thanks to you guys:
--msg for cookiemaster
(1) why explicit curosrs never raise no_data_found? if yes, then what should we put in exception or how do we deal will exception in this case?
(2) and yes, in my ques, many departments (total 16) will have 'SE' and i want to print all 16.
so it's mandatory to use filter or any other method but i need all 16.

--msg for BillB & MichelCadot
if you use group by the it will not return all 16 departments.
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679482 is a reply to message #679481] Thu, 05 March 2020 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Our answers were not about your question (which is not clear) but how to write good PL/SQL code.
You learn coding, learn how to good coding.

cookiemonster told you what to do for your question.

For your information, PL/SQL User's Guide and Reference.

Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679483 is a reply to message #679482] Thu, 05 March 2020 03:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) They just don't raise no_data_found by design. You don't need an exception handler here at all and the examples above show ways of handling no data being found.
2) You've misunderstood. I'm saying your query will report the same department multiple times - once per employee in the department with SE.
If I run your query with the example data above and an extra order by I get:
DEPT_NAME
--------------------
Finance
Finance
Finance
Finance
HR
IT
IT
Marketing
Marketing
Marketing
Production
Production
Production
Production
Sales
Sales
I would have thought you'd want:
DEPT_NAME
--------------------
Finance
HR
IT
Marketing
Production
Sales
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679484 is a reply to message #679483] Thu, 05 March 2020 03:14 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
--cookiemaster

Yeah yeah you are correct, to avoid multiple times department names, we need to group it.
And regarding the exception in my query, if yo say no_data_found will not happen then how do i handle any error if happens in my code? i must write the exception (although i write WHEN OTHERS if no_data_found is to be avoided).
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679487 is a reply to message #679484] Thu, 05 March 2020 03:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
No you don't need to group it.
As I said earlier: "You should use an IN or EXISTS clause to check employees rather than JOIN to it."

e.g.
select d.dept_name
from departments d
WHERE d.dept_id IN (SELECT e.d_id 
                    FROM employees e
                    where e.designation = 'SE'
                   )
As for errors:
What do you want to happen if an error occurs?
If the answer is "I don't know" then you shouldn't be writing exception handlers.

You should almost never write exception WHEN OTHERS. My production PL/SQL code has about two of them.
The idea that procedures should handle all possible errors is dumb - what happens if the error is one that indicates there's something catastrophically wrong with the DB?
Handle errors you expect and can do something with, let all other errors propagate to the client.

Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679488 is a reply to message #679487] Thu, 05 March 2020 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And it's cookiemonster, not cookiemaster.
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679491 is a reply to message #679488] Thu, 05 March 2020 04:00 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
---cookiemonster

oh sorry for mis-typed name.
Yeah the other way is to use IN operator using sub-query.
also, for exception part, i want to show the error code in the output whenever it fails during execution. For this, it doesn't require us to put sqlcode or sqlerrm in exception part by defining WHEN OTHERS. We just skip the exception and that's it?
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679493 is a reply to message #679491] Thu, 05 March 2020 04:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes, if you don't handle the exception in the procedure it'll automatically be passed back to the caller.
Then depending on the client you may need to write some code in there to log/display the error, but nothing you do in the DB layer will make that any easier.
PL/SQL Developer, sqlplus and all similar DB tools display errors from the DB automatically.
The point where you may have to write code is if you're developing your own client in java/C/whatever.
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679494 is a reply to message #679493] Thu, 05 March 2020 04:26 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
okay. but if i leave out the exception handling then my procedure will always fail. But what if i want my procedure to execute without failing and then displaying the error which happened inside the core? then i must use
EXCEPTION WHEN OTHES THEN DBMS_OUTPUT.PUT_LINE(sqlcode);
so that procedure runs well and show me error in output.

[Updated on: Thu, 05 March 2020 04:27]

Report message to a moderator

Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679495 is a reply to message #679494] Thu, 05 March 2020 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
No.
Just No.
This "but if i leave out the exception handling then my procedure will always fail." makes zero sense. Procedures do not need exception handlers, they are optional. If you can't get it to work without then you're doing something wrong, but without seeing the code I can't imagine what.

And that particular exception handler should never be written.
If you use dbms_output to report errors then you are relying on the client to read the dbms_output buffer afterwards. Some clients do that automatically, some don't (sqlplus doesn't) and some don't even know it exists.
But all clients understand oracle errors.
Look:
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 5 10:42:50 2020

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

Last Successful login time: Thu Mar 05 2020 10:40:15 +00:00

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL> DECLARE

  n NUMBER;

BEGIN

  n := 'A';

EXCEPTION WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE(sqlcode);

END;
  2    3    4    5    6    7    8    9   10   11   12   13   14
 15  /

PL/SQL procedure successfully completed.

SQL>
Sqlplus reports that completing sucessfully with no error, despite that fact you can't set a number variable to A.
Now turn on dbms_output:
SQL> set serveroutput on
SQL> DECLARE

  n NUMBER;

BEGIN

  n := 'A';

EXCEPTION WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE(sqlcode);

END;
  2    3    4    5    6    7    8    9   10   11   12   13   14
 15  /
-6502

PL/SQL procedure successfully completed.

SQL>
It still says it completed sucessfully but it does show the sql code.
Now lets get rid of the exception handler:
SQL> set serveroutput off
SQL> DECLARE

  n NUMBER;

BEGIN

  n := 'A';

END;
  2    3    4    5    6    7    8    9   10
 11  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 7
It doesn't tell me it completed successfully and I get the full error stack. That's what you want for all unexpected errors.
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679496 is a reply to message #679495] Thu, 05 March 2020 04:50 Go to previous messageGo to next message
sfksuperman
Messages: 20
Registered: February 2020
Junior Member
YEAH but when u used EXCEPTION, you get "-6502" error there. So, one can easily understand that this "-6502" is not the desired output. NO? but you r right that if one doesn't know what to do when an error occurs then one should not use exception.
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679501 is a reply to message #679496] Thu, 05 March 2020 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
-6502 is almost useless.
Firstly because there is no guarantee you'll see it at all.
That's really important.

Then - Yes you can look up the error.
But what caused it?
Ok it's obvious in my simple example, but what if the block is 100 lines long?
What if it calls 5 procedures?
What if those procedures call other procedures?
The full error stack, which you get if you leave out the exception handler, will tell you exactly which line, in which package/procedure/function the error occurred on. That's really important if you want to fix it.

Also there's the matter of code flow.

When an error occurs, code execution goes to the exception handler, if there isn't one it goes to the callers exception handler, if there isn't one there either it goes up the call stack until it finds an exception handler or hits the client.

Say you've got a procedure A, that calls procedures B and C.
In almost all cases if an error occurs in B you do not want C to run.
But if you put that exception handler in B then C will run unless you write code to stop it.
Re: exit when cursor%notfound is giving buffer overflow if not being used in anonymous block [message #679503 is a reply to message #679494] Thu, 05 March 2020 10:19 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read WHEN OTHERS.

Previous Topic: Select statement inside IF statement for comparing a given string with records in table
Next Topic: Analytic Function: How to Partition Hierarchical Data
Goto Forum:
  


Current Time: Thu Mar 28 05:27:40 CDT 2024