PHP oci_fetch_array函数代码示例

 /**
  *
  *@param $id_ciu
  *
  *
  **/
 public function queryByIC($id_ciu)
 {
     $this->conex = DataBase::getInstance();
     $stid = oci_parse($this->conex, "SELECT * FROM TBL_REPRESENTANTEEMPRESAS WHERE CLV_REPRESENTANTE=:id_ciu");
     if (!$stid) {
         $e = oci_error($this->conex);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     // Realizar la lógica de la consulta
     oci_bind_by_name($stid, ':id_ciu', $id_ciu);
     $r = oci_execute($stid);
     if (!$r) {
         $e = oci_error($stid);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     $result = new RepresentanteEmpresa();
     // Obtener los resultados de la consulta
     while ($fila = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
         $it = new ArrayIterator($fila);
         while ($it->valid()) {
             $result->__SET(strtolower($it->key()), $it->current());
             $it->next();
         }
     }
     //Libera los recursos
     oci_free_statement($stid);
     // Cierra la conexión Oracle
     oci_close($this->conex);
     //retorna el resultado de la consulta
     return $result;
 }

 /**
  * Obtiene el SQL de la funcion especificada
  * @return String or false
  */
 protected function getObjectSql()
 {
     if ($this->remote) {
         $sql = "select line, text from all_source where name = UPPER(:v_function_name) and type = :v_object_type order by name, type, line";
     } else {
         $sql = "select line, text from user_source where name = UPPER(:v_function_name) and type = :v_object_type order by name, type, line";
     }
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_function_name", $this->objectName);
     oci_bind_by_name($stmt, ":v_object_type", $this->objectType);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener el SQL del objeto {$this->objectType} '{$this->objectName}' - {$e['message']}");
         return false;
     }
     $sqlResult = '';
     while ($row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS)) {
         $sqlResult .= $row['TEXT'];
     }
     $this->sourceSql = $sqlResult;
     if (empty($sqlResult)) {
         $this->setMensaje("No se pudo obtener el SQL del objeto {$this->objectType} '{$this->objectName}'");
         return false;
     }
     return $this->sourceSql;
 }

function get_Roadnet_xml($r)
{
    $xml = '<?xml version="1.0" encoding="utf-8"?>
		   <roads>';
    $roadid = null;
    $roadname = null;
    while ($row = oci_fetch_array($r, OCI_BOTH)) {
        if ($roadid == null || $roadid != $row['ROAD_ID']) {
            if ($roadid != null) {
                $xml .= "</road>\n";
            }
            $xml .= "<road>\n";
            $xml .= "<roadid>" . $row['ROAD_ID'] . "</roadid>\n";
            if ($row['ROADSTATUS'] != null) {
                $xml .= "<roadstatus>" . $row['ROADSTATUS'] . "</roadstatus>\n";
            } else {
                $xml .= "<roadstatus>" . '0' . "</roadstatus>\n";
            }
        }
        $xml .= "<node>\n";
        $xml .= "<nodeid>" . $row['NODEID'] . "</nodeid>\n";
        $xml .= "<nodx>" . $row['NODEX'] . "</nodx>\n";
        $xml .= "<nody>" . $row['NODEY'] . "</nody>\n";
        $xml .= "<nodeindex>" . $row['NODEINDEX'] . "</nodeindex>\n";
        $xml .= "</node>\n";
        if ($roadid != $row['ROAD_ID']) {
            $roadid = $row['ROAD_ID'];
        }
    }
    $xml .= "</road>\n";
    $xml .= "</roads>";
    return $xml;
}

function fetchAndUpdateFacultyInfo($query, $conn, $nextPage)
{
    $stid = oci_parse($conn, $query);
    var_dump($query);
    oci_execute($stid);
    echo "<form action=\"{$nextPage}?user=FACULTY\">";
    echo "<table border='1'>\n";
    while ($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
        $i = 1;
        foreach ($row as $item) {
            echo "<tr>\n";
            $column_name = oci_field_name($stid, $i);
            echo "<td> {$column_name} </td>\n";
            if ($column_name == "UnityId" || $column_name == "FacultyNo" || $column_name == "Balance" || $column_name == "Type" || $column_name == "Category" || $column_name == "isHeld" || $column_name == "Department") {
                echo "    <td>" . "<input type=\"text\" name=\"{$column_name}\" value=\"{$item}\" readonly>" . "</td>\n";
            } else {
                echo "    <td>" . "<input type=\"text\" name=\"{$column_name}\" value=\"{$item}\" >" . "</td>\n";
            }
            $i++;
            echo "</tr>\n";
        }
    }
    echo "</table>\n";
    echo "<input type=\"submit\" value=\"Update\">";
    echo "</form>";
}

/**
 * Function takes in an sql statement and returns data as an array. When calling, always alias
 * resuts returned from stored function as 'dataArray'
 */
function executeQuery($SQLstatement, $parserFunction = "defaultFunction")
{
    $conn = $GLOBALS['oracle_connection'];
    if (!$conn) {
        return array('error' => oci_error());
    }
    $preparedStatement = oci_parse($conn, $SQLstatement);
    //Prepare statement
    $success = oci_execute($preparedStatement);
    //execute preparedStatement
    if (!$success) {
        return array('error' => oci_error($preparedStatement));
    }
    $arrayOfDataReturned = array();
    //Array containing all data returned from result set
    $currentRecord;
    //temp user for each result set
    while ($functionResults = oci_fetch_array($preparedStatement, OCI_ASSOC)) {
        //Get first class in result set
        /**Calls variable function; SEE: http://www.php.net/manual/en/functions.variable-functions.php**/
        $currentRecord = $parserFunction($functionResults);
        //Convert information array to class
        array_push($arrayOfDataReturned, $currentRecord);
        //push created object into all classes array
        //echo($allStudentClasses[0]->term + "<br />");
    }
    oci_free_statement($preparedStatement);
    return $arrayOfDataReturned;
}

function get_list($data)
{
    $results = array();
    $games = array();
    $conn = oci_connect('malz', '1Qaz2wsx', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=db1.chpc.ndsu.nodak.edu)(Port=1521)))(CONNECT_DATA=(SID=cs)))');
    //Select customer with last name from field
    $userQuery = 'select userName from Account where listId = :data';
    $listQuery = 'select * from ListGame, Game where listId = :data and ListGame.gameId = Game.gameId';
    $stid = oci_parse($conn, $userQuery);
    $stid2 = oci_parse($conn, $listQuery);
    oci_bind_by_name($stid, ':data', $data);
    oci_bind_by_name($stid2, ':data', $data);
    oci_execute($stid, OCI_DEFAULT);
    //iterate through each row
    while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
        $results[] = $row;
    }
    oci_execute($stid2, OCI_DEFAULT);
    while ($row = oci_fetch_array($stid2, OCI_ASSOC)) {
        $games[] = $row;
    }
    $results[] = $games;
    echo json_encode($results);
    oci_free_statement($stid);
    oci_free_statement($stid2);
    oci_close($conn);
}

 /**
  *
  *@param $id_ciu
  *
  *
  **/
 public function getById($id_jefe)
 {
     $this->conex = DataBase::getInstance();
     //Consulta SQL
     $consulta = "SELECT * FROM FISC_JEFE_OFICINA WHERE \n\t\t\t\t\t\tID_JEFE=:id_jefe";
     $stid = oci_parse($this->conex, $consulta);
     if (!$stid) {
         $e = oci_error($this->conex);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     // Realizar la lógica de la consulta
     oci_bind_by_name($stid, ':id_jefe', $id_jefe);
     $r = oci_execute($stid);
     if (!$r) {
         $e = oci_error($stid);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     $result = array();
     // Obtener los resultados de la consulta
     while ($fila = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
         $it = new ArrayIterator($fila);
         $alm = new Denuncia();
         while ($it->valid()) {
             $alm->__SET(strtolower($it->key()), $it->current());
             $it->next();
         }
         $result[] = $alm;
     }
     //Libera los recursos
     oci_free_statement($stid);
     // Cierra la conexión Oracle
     oci_close($this->conex);
     //retorna el resultado de la consulta
     return $result;
 }

function have_right_on($right, $url)
{
    $rights = oci_parse($GLOBALS['conn'], "SELECT {$right} " . "  FROM vu_userrights " . " WHERE user_id = {$_SESSION['user_id']} " . "\t\tAND\tcompany_ref_id = {$_SESSION['company_id']} " . "   AND url = '{$url}' ");
    oci_execute($rights);
    $row = oci_fetch_array($rights);
    return $row[0] === 'Yes';
}

 public function fetch($limit = -1)
 {
     if ($this->___n > 0) {
         $this->___limit++;
         if ($this->___limit == $limit) {
             $this->___limit = -1;
             return false;
         }
         if (!$this->___is_oci_) {
             foreach ($this->___result as $result) {
                 foreach ($result as $name => $value) {
                     if (!is_int($name)) {
                         $name = strtolower($name);
                         $this[$name] = $this->getfieldvalue($value);
                     }
                 }
                 return true;
             }
             return false;
         } else {
             while ($result = oci_fetch_array($this->___result)) {
                 foreach ($result as $name => $value) {
                     if (!is_int($name)) {
                         $name = strtolower($name);
                         $this[$name] = $this->getfieldvalue($value);
                     }
                 }
                 return true;
             }
             oci_free_statement($this->___result);
             return false;
         }
     }
 }

function cargarArray($sentencia)
{
    include dirname(__FILE__) . '/conectar_ORACLE.php';
    $array = array();
    $sentenciaExec = oci_parse($c, $sentencia);
    oci_execute($sentenciaExec);
    $error = 0;
    $k = 0;
    $ncols = oci_num_fields($sentenciaExec);
    for ($i = 1; $i <= $ncols; ++$i) {
        $colname = oci_field_name($sentenciaExec, $i);
        $array[0][$k] = $colname;
        $k++;
    }
    $cont = 0;
    $j = 1;
    $k = 0;
    while ($row = oci_fetch_array($sentenciaExec, OCI_BOTH + OCI_RETURN_NULLS)) {
        while ($cont < $ncols) {
            $array[$j][$cont] = $row[$cont];
            $cont++;
        }
        $cont = 0;
        $k = 0;
        $j++;
    }
    if (oci_num_rows($sentenciaExec) == 0) {
        oci_free_statement($sentenciaExec);
        return false;
    } else {
        oci_free_statement($sentenciaExec);
        return $array;
    }
}

 public function buscarPorProcedimiento()
 {
     $sql = "SELECT job, to_char(last_date, 'DD/MM/YYYY') last_date, last_sec, to_char(next_date, 'DD/MM/YYYY') next_date, next_sec, interval, failures, what from user_jobs WHERE UPPER(what) LIKE UPPER('%' || :v_procedure_name || '%')";
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_procedure_name", $this->objectName);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener los datos del job que ejecuta el proceso '{$this->objectName}' de la tabla user_jobs - {$e['message']}");
         $this->setEstado(false);
     }
     $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
     if (empty($row)) {
         $this->setEstado(false);
     }
     $this->jobId = $row['JOB'];
     $this->lastDate = $row['LAST_DATE'];
     $this->lastSec = $row['LAST_SEC'];
     $this->nextDate = $row['NEXT_DATE'];
     $this->nextSec = $row['NEXT_SEC'];
     $this->interval = $row['INTERVAL'];
     $this->failures = $row['FAILURES'];
     $this->jobSql = $row['WHAT'];
     $this->setEstado(true);
     return $this->getEstado();
 }

 protected function getDetalle()
 {
     $sql = "SELECT synonym_name, table_owner, table_name, db_link FROM user_synonyms WHERE synonym_name = UPPER(:v_synonym_name)";
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_synonym_name", $this->objectName);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener los datos del sinónimo '{$this->objectName}' de la tabla user_synonyms - {$e['message']}");
         $this->setEstado(false);
         return false;
     }
     $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
     if (empty($row)) {
         $sqlPublic = "SELECT * FROM all_synonyms WHERE synonym_name = UPPER(:v_synonym_name) AND owner = 'PUBLIC'";
         $stmt2 = oci_parse($this->getConnection(), $sqlPublic);
         oci_bind_by_name($stmt2, ":v_synonym_name", $this->objectName);
         if (!@oci_execute($stmt2)) {
             $e = oci_error($stmt2);
             $this->setMensaje("Error al obtener los datos del sinónimo '{$this->objectName}' de la tabla all_synonyms - {$e['message']}");
             $this->setEstado(false);
             return false;
         }
         $row = oci_fetch_array($stmt2, OCI_ASSOC | OCI_RETURN_NULLS);
         if (empty($row)) {
             $this->setMensaje("No se encontró el sinónimo '{$this->objectName}' en la tabla user_synonyms");
             $this->setEstado(false);
             return false;
         }
     }
     $this->tableOwner = $row['TABLE_OWNER'];
     $this->tableName = $row['TABLE_NAME'];
     $this->dbLinkName = $row['DB_LINK'];
     $this->setEstado(true);
     return true;
 }

 public function getById($id)
 {
     $this->conex = DataBase::getInstance();
     $stid = oci_parse($this->conex, "SELECT *\n\t\t\tFROM FISC_CIUDADANO WHERE ID_CIUDADANO=:id");
     if (!$stid) {
         $e = oci_error($this->conex);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     // Realizar la lógica de la consulta
     oci_bind_by_name($stid, ':id', $id);
     $r = oci_execute($stid);
     if (!$r) {
         $e = oci_error($stid);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     // Obtener los resultados de la consulta
     $alm = new FiscCiudadano();
     while ($fila = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
         $it = new ArrayIterator($fila);
         while ($it->valid()) {
             $alm->__SET(strtolower($it->key()), $it->current());
             $it->next();
         }
     }
     //Libera los recursos
     oci_free_statement($stid);
     // Cierra la conexión Oracle
     oci_close($this->conex);
     //retorna el resultado de la consulta
     return $alm;
 }

    public function fetch_db_flash_cache_detail($conn_db) {
        global $error;

        $sql = "select name, value from v\$parameter where name like 'db_flash_cache_%'";
        $state_id = oci_parse($conn_db, $sql);
        $result = oci_execute($state_id);
        if ($result == FALSE) {
            $error->set_msg("Failed SQL = '$sql'");
            return(ERROR);
        }
        while ($row = oci_fetch_array($state_id, OCI_BOTH)) {
            if ($row['NAME'] == 'db_flash_cache_file') {
                if (isset($row['VALUE'])) { 
                    $db_flash_cache_file = $row['VALUE'];
                } else {
                    $db_flash_cache_file = '';
                }
            }
            if ($row['NAME'] == 'db_flash_cache_size') {
                if (isset($row['VALUE'])) { 
                    $db_flash_cache_size = $row['VALUE'];
                } else {
                    $db_flash_cache_size = '0';
                }
            }
        }
        if (!empty($db_flash_cache_file)) {
            $db_flash_cache_enable = TRUE;
        } else {
            $db_flash_cache_enable = FALSE;
        }
        $array_db_flash_cache_detail = array('enable' => $db_flash_cache_enable, 'file' => $db_flash_cache_file, 'size' => $db_flash_cache_size);
        return($array_db_flash_cache_detail);
    }

 public function getDetalle()
 {
     $sql = "SELECT trigger_name, trigger_type, triggering_event, table_name, status, description, trigger_body FROM user_triggers WHERE trigger_name = UPPER(:v_trigger_name)";
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_trigger_name", $this->objectName);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener los datos del trigger '{$this->objectName}' de la tabla user_triggers - {$e['message']}");
         return false;
     }
     $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
     if (empty($row)) {
         $this->setMensaje("No se pudo encontrar el trigger especificado en la tabla user_triggers");
         return false;
     }
     $this->triggerType = $row['TRIGGER_TYPE'];
     $this->triggeringEvent = $row['TRIGGERING_EVENT'];
     $this->affectedTable = $row['TABLE_NAME'];
     $this->triggerStatus = $row['STATUS'];
     $this->description = $row['DESCRIPTION'];
     $this->triggerSql = $row['TRIGGER_BODY'];
     if ($this->triggerStatus != 'ENABLED') {
         $this->setMensaje("El trigger se encuentra inhabilitado");
         return false;
     }
     return true;
 }