Forum Stats

  • 3,769,237 Users
  • 2,252,936 Discussions
  • 7,874,955 Comments

Discussions

Is pl/sql having on error case?

fsze88hoho
fsze88hoho Member Posts: 373
edited Sep 1, 2008 1:28AM in SQL & PL/SQL
HI,
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;
BEGIN
nextinvoiceid :=0;
select invoice_seq.currval +1 into nextinvoiceid from dual;
EXCEPTION
WHEN no_data_found THEN
SELECT min_value into nextinvoiceid FROM user_sequences WHERE sequence_name = 'invoice_seq';
RETURN nextinvoiceid;
END nextinvoicenumFunc;
Tagged:

Answers

  • Gurjas
    Gurjas Member Posts: 1,190
    edited Sep 1, 2008 1:25AM
    Why using
    select invoice_seq.currval +1 into nextinvoiceid from dual;
    use
    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

    Edited:
    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.

    Regards
    Singh

    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|http://forums.oracle.com/forums/message.jspa?messageID=2732487#2732487], 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.

    Justin
This discussion has been closed.