EllisLab text mark
Advanced Search
     
Oracle Nextval issue
Posted: 02 April 2009 06:42 PM
Joined: 2009-04-02
8 posts

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
 
Posted: 02 April 2009 07:22 PM   [ # 1 ]   [ Rating: 0 ]
Avatar
Joined: 2008-06-04
2101 posts

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.

 
Posted: 02 April 2009 07:31 PM   [ # 2 ]   [ Rating: 0 ]
Joined: 2009-04-02
8 posts

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 confused ).  Somehow this query is being run 3 times.  It’s not inside any kind of loop or anything either.

 
Posted: 02 April 2009 07:42 PM   [ # 3 ]   [ Rating: 0 ]
Avatar
Joined: 2008-06-04
2101 posts

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?

 
Posted: 02 April 2009 08:44 PM   [ # 4 ]   [ Rating: 0 ]
Joined: 2009-04-02
8 posts

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);
    
 
Posted: 02 April 2009 08:56 PM   [ # 5 ]   [ Rating: 0 ]
Avatar
Joined: 2008-06-04
2101 posts

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.

 
Posted: 02 April 2009 09:27 PM   [ # 6 ]   [ Rating: 0 ]
Joined: 2009-04-02
8 posts

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.

 
Posted: 02 April 2009 09:37 PM   [ # 7 ]   [ Rating: 0 ]
Joined: 2009-04-02
8 posts

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

 
Posted: 03 April 2009 01:27 AM   [ # 8 ]   [ Rating: 0 ]
Joined: 2009-04-02
8 posts

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.

 
Posted: 03 April 2009 07:41 AM   [ # 9 ]   [ Rating: 0 ]
Avatar
Joined: 2008-06-04
2101 posts

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.  smile  (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?

 
Posted: 03 April 2009 11:32 AM   [ # 10 ]   [ Rating: 0 ]
Joined: 2009-04-02
8 posts

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!<br>";
$sql 'select testlog.schedule_seq.nextval from dual';
echo 
$sql."<br>";
$query $this->db->query($sql);
echo 
"After!<br>";
$row $query->row();
echo 
"nextval: ".$row->NEXTVAL."<br>"

And the output:

Before!
select testlog.schedule_seq.nextval from dual
After
!
nextval169 

And then a refresh of the page:

Before!
select testlog.schedule_seq.nextval from dual
After
!
nextval172 

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($stidOCI_DEFAULT);

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

oci_close
($conn); 

which returns this:

177 

and after a refresh, this:

178 
 
Posted: 15 October 2009 12:52 PM   [ # 11 ]   [ Rating: 0 ]
Joined: 2009-10-15
3 posts

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.)  tongue rolleye

/**
     * 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.

 
Posted: 20 October 2009 07:02 PM   [ # 12 ]   [ Rating: 0 ]
Joined: 2009-10-20
2 posts

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.

 
Posted: 07 March 2010 07:20 PM   [ # 13 ]   [ Rating: 0 ]
Joined: 2010-03-05
1 posts

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.

 
Posted: 30 October 2011 08:07 PM   [ # 14 ]   [ Rating: 0 ]
Joined: 2011-10-30
1 posts

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

 
Posted: 13 May 2013 07:43 AM   [ # 15 ]   [ Rating: 0 ]
Joined: 2013-03-05
1 posts

I’m new to CodeIgniter. I try add this line to file oci8_result.php.

if(!array_key_exists('NEXTVAL',$this->result_array()[0])) 

in function num_rows()

public function num_rows()
 
{
  
if ($this->num_rows === && count($this->result_array()) > 0)
  
{
   $this
->num_rows count($this->result_array());

   if(!
array_key_exists('NEXTVAL',$this->result_array()[0]))
   @
oci_execute($this->stmt_id);

   if (
$this->curs_id)
   
{
    
@oci_execute($this->curs_id);
   
}
  }

  
return $this->num_rows;
 

I hope this will help.
I use CI 2.1.3 and this work for me.