oci_fetch_assoc(): ORA-01002: fetch out of sequence

[ylynfatt]

I am getting a PHP Warning for a query I am using for an Oracle Database. It works fine when I run the SQL query in SQL Developer, however it gives the error when viewing the page in the browser (through PHP and CodeIgniter).

A PHP Error was encountered
Severity: Warning
Message: oci_fetch_assoc(): ORA-01002: fetch out of sequence
Filename: oci8/oci8_result.php
Line Number: 155

I checked the following thread on the Forums (http://codeigniter.com/forums/viewthread/208846/) and it seems one solution is to put the @ character in front of oci_fetch_assoc($id) on line 155 of the oci8_result.php file.

So the function would now be

/**
 * Result - associative array
 *
 * Returns the result set as an array
 *
 * @access  protected
 * @return  array
 */
protected function _fetch_assoc()
{
    $id = ($this->curs_id) ? $this->curs_id : $this->stmt_id;
    return @oci_fetch_assoc($id);
}

Not sure why this warning is being caused or what other solution there is.

As some added information. I am running my application on Ubuntu 10.04.3 LTS with PHP 5.3.2-1ubuntu4.17 and the Oracle database is version 10g.

[narfbg]

Could you show the query (or most likely a series of queries) that triggers the warning?

[ylynfatt]

I was using the CI Active Record and ran the following:

$query = $this->db->get_where('TABLENAME', array('ID' => $id));
return $query->result();

Which generates the following:

SELECT * FROM TABLENAME WHERE "ID" = '12345678'

[narfbg]

It's unlikely that this single query is the cause of the warning being issued. See this link for more info: http://www.dba-oracle.com/t_ora_01002_fetch_out_of_sequence.htm

... I'd rather like to find the real cause and fix it.

[ylynfatt]

@narfbg I have other methods in my model with queries being run before and after, but when I was trying to troubleshoot I commented out the queries and added them back one by one and this was the query that generated the error.

[narfbg]

OK, can you show the queries that are executed prior to this one? And do you use cursors and/or prepared statements?

[ylynfatt]

Here are the queries. I'm not using any cursors that I know of, unless the CI Active Record is creating them.

      $query = $this->db->get_where('BIO_VW', array('EMPLID' => $id));
      return $query->result();

      $sql = "SELECT TRUNC(MONTHS_BETWEEN (CURRENT_DATE, '{$dob}') / 12) || ' Years ' || MOD (TRUNC (MONTHS_BETWEEN (CURRENT_DATE, '{$dob}')), 12) || ' Months ' AS AGE FROM DUAL";
      $query = $this->db->query($sql);
      return $query->row()->AGE;

      $this->db->order_by("MON_YEAR", "DESC");
      $query = $this->db->get_where('QUAL_VW', array('EMPLID' => $id));
      return $query->result();  

      $this->db->order_by("YR_ACQUIRED", "DESC");
      $query = $this->db->get_where('COMPTCY_VW', array('EMPLID' => $id));
      return $query->result();

      $query = $this->db->get_where('LANG_VW', array('EMPLID' => $id));
      return $query->result();  

      $this->db->order_by("DT_ISSUED", "DESC");
      $query = $this->db->get_where('HON_VW', array('EMPLID' => $id));
      return $query->result(); 

      $this->db->order_by("DT_ISSUED", "DESC");
      $query = $this->db->get_where('LIC_VW', array('EMPLID' => $id));
      return $query->result();    

      $this->db->order_by("DT_ISSUED", "DESC");
      $query = $this->db->get_where('MEM_VW', array('EMPLID' => $id));
      return $query->result();   

      $this->db->order_by("FROM_DATE", "DESC");
      $query = $this->db->get_where('OUTREACH', array('EMPLID' => $id));
      return $query->result(); 

      $this->db->order_by("MON_YEAR", "DESC");
      $query = $this->db->get_where('PRIOR_EX_VW', array('EMPLID' => $id));
      return $query->result();    

      $sql = "SELECT * FROM job_vw a, xlattable_vw b WHERE a.EMPLID = '{$id}' AND b.fieldname = 'JOB_INDICATOR' and
a.job_indicator = b.fieldvalue and b.effdt = (select max(b1.effdt) from xlattable_vw b1 where b1.fieldname = b.fieldname and b1.fieldvalue = b.fieldvalue and b1.effdt <= sysdate) ORDER BY a.ASOFDATE DESC";
      $query = $this->db->query($sql);
      return $query->result(); 

      $this->db->select("EMPLID, COMMENT_DT, dbms_lob.substr(COMMENTS, 3000, 1) AS COMMENTS", FALSE);
      $this->db->order_by("COMMENT_DT", "DESC");
      $query = $this->db->get_where('COMMENTS_VW', array('EMPLID' => $id));
      return $query->result();

[SrVinicius]

This solved my problem, jst tks