Home » SQL & PL/SQL » SQL & PL/SQL » How to handle error in 5th counting on cursor
How to handle error in 5th counting on cursor [message #611182] Sat, 29 March 2014 02:43 Go to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
Suppose I have 10 records whenever cursor reach in 5th no. then we have to generate error, please let me know that how to cursor work for 10 records.
Re: How to handle error in 5th counting on cursor [message #611185 is a reply to message #611182] Sat, 29 March 2014 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not clear.
You have some options:
- enclose between BEGIN EXCEPTION END the statements you want to handle the error
- use LOG ERRORS option on DML
- ...

If you want more specific answer you have to post more specific question.
For instance, a sample of code representing your issue.
And of course, the options you have depend on your Oracle version.

[Updated on: Sat, 29 March 2014 03:36]

Report message to a moderator

Re: How to handle error in 5th counting on cursor [message #611187 is a reply to message #611182] Sat, 29 March 2014 03:37 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
It depends on

- how you read and open the cursor (inside a for loop or inside other kind of loop such as loop ... exit when ... end loop, etc.)

- how you define the meaning of the order in your cursor query for determining appropriately the 5th record

Just an example (which is not necessarily what you're looking for, as you don't any give detail & test case)

SQL> <<bk>>
  2  declare
  3    cursor empCur return hr.employees%rowtype is
  4      select
  5        t1.*
  6      from
  7        hr.employees t1
  8      where
  9        t1.department_id = 100
 10      order by
 11        t1.employee_id;
 12  --
 13    empRec hr.employees%rowtype;
 14  --
 15  begin
 16    open bk.empCur;
 17    loop
 18      fetch bk.empCur into bk.empRec;
 19      exit when (bk.empCur%notfound) or (bk.empCur%rowcount = 5);
 20      sys.dbms_output.put_line('employee_id = ' || to_char(bk.empRec.employee_id));
 21    end loop;
 22    close bk.empCur;
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
employee_id = 108
employee_id = 109
employee_id = 110
employee_id = 111

PL/SQL procedure successfully completed.

SQL> 


In the case of your example when %rowcount = 5 you can raise some exception.
Re: How to handle error in 5th counting on cursor [message #611188 is a reply to message #611187] Sat, 29 March 2014 03:56 Go to previous messageGo to next message
aaditya321
Messages: 225
Registered: January 2014
Location: Delhi
Senior Member
My output like should be:

employee_id = 1
employee_id = 2
employee_id = 3
employee_id = 4
employee_id = 5 generate issue
employee_id = 6
employee_id = 7
employee_id = 8
employee_id = 9
employee_id = 10
Re: How to handle error in 5th counting on cursor [message #611190 is a reply to message #611188] Sat, 29 March 2014 04:00 Go to previous message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Read Michel's comment carefully about the test case and clarity of the problem description.
Previous Topic: Union??? or Merge of tables
Next Topic: ORA-00904: "CNT": invalid identifier error
Goto Forum:
  


Current Time: Thu Apr 25 18:17:08 CDT 2024