Thread

I’ve got an interesting issue going on here when grabbing the nextval from a sequence in Oracle.  The problem is that the nextval increments by 3 each time, not 1.  The increment parameter is set to 1 in Oracle, and if I do the same select from the command line, it returns the nextval incremented by only 1.  So, my question is, why and/or how is this being incremented by 3 when I run the query through CI?

$sql = "select testlog.schedule_seq.nextval from dual";
$query = $this->db->query($sql);
$row = $query->row();
echo $row->NEXTVAL;

Howdi, and welcome to the CI forums.

I know next to nothing about Oracle, but I’m assuming that nextval is meant to return one row?

It might be worth doing a num_rows() call in there, before you access the row, to make sure that you did in fact only get one row.  $query->row() of course will work fine with multi-row results, it’ll just blissfully return the first row only.

It’s only returning one row.  That nextval query in Oracle is the equivalent of an auto_increment field in MySQL (since Oracle doesn’t have an auto_increment feature 😖 ).  Somehow this query is being run 3 times.  It’s not inside any kind of loop or anything either.

Okay, the next thing I’d try (because it’s easy) is to turn on the Profiling Library - which will report, on your browser, the exact database calls being made.  This should give you a pointer as to whether it’s a weirdness with the Oracle/CI interaction (we don’t get many Oracle users around these ‘ere parts) or at the other end.  I’m guessing you can crank up some Oracle profiling tools to see if it’s getting the same request three times?

Does it need to be placed anywhere special within the controller?  I’m not getting any output…

function index() {
    
        $this->output->enable_profiler(TRUE);
        $data['title'] = "Scheduler";
        $data['query'] = $this->approve_model->getDetails();
        $data['table'] = $this->approve_model->getPendingTable($data['query']);
        $this->load->view('approve_view',$data);
    }

No, anywhere should be okay.  I normally put it in my constructor, just so I can easily comment / de-comment it during development.

Do you mean you get no output at all or just no profiler output - should be trailing at the end of your web page - there’s quite a bit of it, so it’s hard to miss I guess.

Sorry, I should’ve been a little more specific.  No profiler output.  There is output on the page though. 

I’ll see if I can fire up something from the Oracle side tomorrow when I’m back at work to see how many queries are coming in. 

I’m intrigued by this profiler though so I’d like to get it displaying properly if possible.

Disregard.  I got the profiler working.  It’s only running one “nextval” query so I’ll check things out from the Oracle side tomorrow.

Ok, I’d say this is definitely some sort of bug within CI.  On the Oracle side, the query shows up like this:

select testlog.schedule_seq.nextval from dualselect testlog.schedule_seq.nextval from dualselect testlog.schedule_seq.nextval from dual

I wrote my own connection/query using the base oci php functions and the number only increments by 1 each time.

Well .. it’s time to file that bug report.  As I mentioned, we don’t see a lot of Oracle users, and I guess the same goes for the EL guys.

You could try running the query twice - it’d be fascinating it you got a result of 4 incs rather than 6.  😊  (I’m having a Joseph Heller flashback now.)

And you have stuck an echo “BOB SAYS HI” or similar in there, to really be sure that that code block only gets hit the once, right?

So this morning I was playing around and somehow it started incrementing by 4 instead of 3.  It’s back to 3 now and I’m not sure if I did anything to cause it to jump to 4, but…kinda creepy.

Here’s the test code I’ve been using:

echo "Before!
";
$sql = 'select testlog.schedule_seq.nextval from dual';
echo $sql."
";
$query = $this->db->query($sql);
echo "After!
";
$row = $query->row();
echo "nextval: ".$row->NEXTVAL."
";

And the output:

Before!
select testlog.schedule_seq.nextval from dual
After!
nextval: 169

And then a refresh of the page:

Before!
select testlog.schedule_seq.nextval from dual
After!
nextval: 172

Here’s the php/oci8 code I used:

$conn = oci_connect('xxxxx', 'xxxxx', '//xxx.xxx.xxx.xxx/xxx');
$query = 'select testlog.schedule_seq.nextval from dual';

$stid = oci_parse($conn, $query);
$r = oci_execute($stid, OCI_DEFAULT);

while ($row = oci_fetch_row($stid)) {
  foreach($row as $item) {
      echo $item."
";
  }
}

oci_close($conn);

which returns this:

177

and after a refresh, this:

178

I’m new to CodeIgniter and I am testing it out. At my work, we only use Oracle for DB backend. I too had this issue with oracle sequences, so I modified the insert statement in the oci8_driver.php file to handle nextval. (As far as I know, only the insert statement would have the need to access a sequence.)  😝

/**
     * Insert statement
     *
     * Generates a platform-specific insert string from the supplied data
     *
     * @access  public
     * @param   string  the table name
     * @param   array   the insert keys
     * @param   array   the insert values
     * @return  string
     */
    function _insert($table, $keys, $values)
    {
        //Messy fix for inserting sequences
        $seq = FALSE;
        foreach($values as $insertKey)
        {
            if (substr_count(strtoupper($insertKey), "NEXTVAL") > 0)
            {
                $seq = TRUE;
            }
        }
        
        if ($seq)
        {
            $sqlnextval="";
            foreach($values as $insertKey)
            {
                if (strlen($insertKey) > 8)
                {
                    if (strtoupper(substr($insertKey,-8,7))=="NEXTVAL")
                    {
                        $nextval = str_replace("'", "",$insertKey);
                        $sqlnextval="$sqlnextval,$nextval";
                    }else{
                        $sqlnextval="$sqlnextval,$insertKey";
                    }
                }
            }
            $sqlnextval = substr($sqlnextval,1,strlen($sqlnextval));
            return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES ($sqlnextval)";
        }else{
            return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
        }
    }

I’m new to php so I thought to post this code to see if it can be cleaned up. I originally come from a Java/WebLogic/Oracle background.

I was having the same problem—calling something like:

$q = $this->db->query('SELECT somesequence.NEXTVAL FROM dual');
$result = $q->row();          // Increments three times
$result = $q->result();       // Increments three times
$result = $q->row_array();    // Increments two times
$result = $q->result_array(); // Increments two times

Looking deeper, I found that the function num_rows() in CI_DB_oci8_result (oci8_result.php) is calling ociexecute.  This seems to be what’s causing the extra DB queries that leads to the sequence getting incremented more than it should.

To remedy this, I altered the function to read as:

function num_rows()
{
    if ( $this->num_rows )      return $this->num_rows;
    if ( $this->result_array )  return count($this->result_array);
        
    return count($this->result_array());
    ...
}

In addition to that, I had to overwrite CI_DB_result’s result_object() and row_object() functions for the oci8 driver.

This probably won’t cover all the use cases, but it seems to be working for my application.

Sorry to bump a 6 month old thread, but I am experiencing the same thing, increments by 3.

I did not see anything in the bug tracker for this. 
Does anyone have a complete fix for this? derp alteration of the function didnt work for me, and didnt list what else he did to fix it.

I think its must be bug , we should need to throw it in dustbin, that’s just waste of time !