SQL & PL/SQL

Hi All,

We have a sequence "Seq1". We are calling the nextval using Function and it works fine in 11g.

The function looks like following...

create function sf_getval(seqname varchar2)

return number

is

ret_val number :=0;

begin

     execute immediate 'select '||seqname||'.nextval from dua' into ret_val;

      return ret_val;

end;

When above function is called from SQLPLUS in following manner gets the nextval on every execution.

select sf_getval('seq1') from dual;

THE PROBLEM:

We are testing these programs on a 12c RAC instance.

In 12c the default new parameters for the sequence are NOKEEP and GLOBAL by default but I don;t think these are really impacting the anything described below.

In this situation,

select sf_getval('seq1') from dual;

gives us the nextval on the first execution but every subsequent execution, we are getting a number a number short of what was displayed on the first execution.

So

--first execution

select sf_getval('seq1') from dual ;

2

select sf_getval('seq1') from dual ;

1

and so on on every execution.

Any idea what must be happening?

Thanks in advance.

Parth