Forum Stats

  • 3,782,436 Users
  • 2,254,645 Discussions


Is pl/sql having on error case?

fsze88hoho Member Posts: 373
edited Sep 1, 2008 1:28AM in SQL & PL/SQL
I want to know is pl/sql having on error case to handling sql error?
following function will give me an error if invoice_seq havn't select nextval once.
any on error case instead of exception of following function?

function nextinvoicenumFunc RETURN NUMBER AS
nextinvoiceid number;
nextinvoiceid :=0;
select invoice_seq.currval +1 into nextinvoiceid from dual;
WHEN no_data_found THEN
SELECT min_value into nextinvoiceid FROM user_sequences WHERE sequence_name = 'invoice_seq';
RETURN nextinvoiceid;
END nextinvoicenumFunc;


  • Gurjas
    Gurjas Member Posts: 1,190
    edited Sep 1, 2008 1:25AM
    Why using
    select invoice_seq.currval +1 into nextinvoiceid from dual;
    select invoice_seq.nextval into nextinvoiceid from dual;
    Will you a nextval of the sequence if the next value is greater then maxvalue then will give following error.
    ORA-08004: sequence invoice_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

    If you function is to get next value of the invoice id then you don't even need that you can directly use the select with nextval or currval of the sequence.


    Edited by: Singh on Sep 1, 2008 10:53 AM
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    1) As discussed in [your related thread in the Database - General forum|], having this function at all is probably a bad idea because it looks like you are trying to eliminate gaps.

    2) The currval of a sequence is unique to that session. If in your session sequence.currval returns 10, for example, it is entirely possible that other sessions have been given the sequence values 11, 12, and 13 already. So sequence.currval+1 isn't guaranteed to return a unique value.

    3) Using the min_value of the sequence if it has never been used in the current session is incorrect because it will always give you the same value. And that value has probably already been used by another invoice.

    4) Specifically addressing your question about the error-- your exception handler would have to catch the actual exception thrown. If CURRVAL is undefined in the current session, your SELECT statement would return an ORA-08002 error. Not an ORA-01403 (no_data_found) error. So you would have to declare and catch the ORA-08002 error. For the reasons addressed above, though, it wouldn't make sense to catch this sort of exception.

This discussion has been closed.