Oracle SEQUENCE.Currval problem in CodeIgniter

Question

I have a sequence named WCOMP_SEQ in oracle to generate auto increment column ON WCOMP table. When I insert a row to WCOMP table in SQLPlus, the row inserted and I can get the auto increment value using

SELECT WCOMP_SEQ.currval FROM dual

But when I ran insert a row using Database Class in CodeIgniter, the row inserted but when I ran the query above to get auto increment value I got Exception:

Exception: Undefined Index currval in E:...

How to fix this?

Answer 1

In order to get currval on the sequence you will need to have at least one reference to the corresponding nextval for the sequence in the current user session. This is what causes it to set the currval value which would belong to the session.

If you are using it outside, it defeats the purpose which value could it return if there were other sessions active.

Answer 2

There is a way to get the value automatically assigned to a column: it is the RETURNING clause.

So, here is my sequence:

SQL> select emp_seq.currval from dual
  2  /

   CURRVAL
----------
      8140

SQL>

I'm going to use it in an INSERT statement:

SQL> var seqval number
SQL> insert into emp
  2  (empno, ename, deptno, sal, job)
  3  values
  4      (emp_seq.nextval, 'JELLEMA', 50, 4575, 'PAINTER')
  5  returning empno into :seqval
  6  /

1 row created.

SQL>

I returned the EMPNO into a SQL*Plus variable which I can print, and it has the same value as CURRVAL:

SQL> print :seqval

    SEQVAL
----------
      8141

SQL> select emp_seq.currval from dual
  2  /

   CURRVAL
----------
      8141

SQL>

Your next question is, "does CodeIgniter support the RETURNING sysntax?" I have no idea, but I suspect it does not. Most non-Oracle frameworks don't.

There is always the option to wrap the INSERT statement in a stored procedure, but that's an architectural decision whoch many people dislike.

Answer 3

You can not fetch the SEQUENCE current value without issuing NEXTVAL (see here). So, if you do not want to increment the sequence value (by using NEXTVAL), you should instead query USER_SEQUENCES.

Something like this:

select Sequence_Name
, Last_Number 
from user_sequences
where sequence_name = 'WCOMP_SEQ'
/
SEQUENCE_NAME   LAST_NUMBER
-------------   -----------  
WCOMP_SEQ                20

Hope this helps.