oci_fetch_all
oci_fetch_assoc
Funções para OCI8
PHP Manual

# oci_fetch_array

(PHP 5, PECL OCI8 >= 1.1.0)

oci_fetch_array — Returns the next row from a query as an associative or numeric array

### Descrição

array oci_fetch_array ( resource `$statement` [, int `$mode` ] )

Returns an array containing the next result-set row of a query. Each array entry corresponds to a column of the row. This function is typically called in a loop until it returns `FALSE`, indicating no more rows exist.

Para detalhes sobre o mapeamento dos tipos de dados realizado pelo driver oci8, veja tipos de dados suportados pelo driver

### Parâmetros

`statement`

  A valid OCI8 statement identifier created by oci_parse() and executed by oci_execute(), or a REF CURSOR statement identifier.

`mode`

  An optional second parameter can be any combination of the following constants:

  oci_fetch_array() Modes
  Constant | Description
  --- | ---
  `OCI_BOTH` | Returns an array with both associative and numeric indices. This is the same as `OCI_ASSOC` + `OCI_NUM` and is the default behavior.
  `OCI_ASSOC` | Returns an associative array.
  `OCI_NUM` | Returns a numeric array.
  `OCI_RETURN_NULLS` | Creates elements for `NULL` fields. The element values will be a PHP `NULL`.
  `OCI_RETURN_LOBS` | Returns the contents of LOBs instead of the LOB descriptors.

  The default `mode` is `OCI_BOTH`.

  Use the addition operator "+" to specify more than one mode at a time.

### Valor Retornado

Returns an array with associative and/or numeric indices. If there are no more rows in the `statement` then `FALSE` is returned.

By default, LOB columns are returned as LOB descriptors.

DATE columns are returned as strings formatted to the current date format. The default format can be changed with Oracle environment variables such as NLS_LANG or by a previously executed ALTER SESSION SET NLS_DATE_FORMAT command.

Oracle's default, non-case sensitive column names will have uppercase associative indices in the result array. Case-sensitive column names will have array indices using the exact column case. Use var_dump() on the result array to verify the appropriate case to use for each query.

### Exemplos

Exemplo #1 oci_fetch_array() with `OCI_BOTH`

`<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT department_id, department_name FROM departments');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_BOTH))) {
// Use the uppercase column names for the associative array indices
echo $row[0] . " and " . $row['DEPARTMENT_ID'] . " are the same<br>\n";
echo $row[1] . " and " . $row['DEPARTMENT_NAME'] . " are the same<br>\n";
}
oci_free_statement($stid);
oci_close($conn);
?>`

Exemplo #2 oci_fetch_array() with `OCI_NUM`

`<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description CLOB);
INSERT INTO mytab (id, description) values (1, 'A very long string');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_NUM))) {
echo $row[0] . "<br>\n";
echo $row[1]->read(11) . "<br>\n"; // this will output first 11 bytes from DESCRIPTION
}
// Output is:
// 1
// A very long
oci_free_statement($stid);
oci_close($conn);
?>`

Exemplo #3 oci_fetch_array() with `OCI_ASSOC`

`<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description CLOB);
INSERT INTO mytab (id, description) values (1, 'A very long string');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC))) {
echo $row['ID'] . "<br>\n";
echo $row['DESCRIPTION']->read(11) . "<br>\n"; // this will output first 11 bytes from DESCRIPTION
}
// Output is:
// 1
// A very long
oci_free_statement($stid);
oci_close($conn);
?>`

Exemplo #4 oci_fetch_array() with `OCI_RETURN_NULLS`

`<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT 1, null FROM dual');
oci_execute($stid);
while (($row = oci_fetch_array ($stid, OCI_ASSOC))) { // Ignore NULLs
var_dump($row);
}
/*
The above code prints:
array(1) {
[1]=>
string(1) "1"
}
*/
$stid = oci_parse($conn, 'SELECT 1, null FROM dual');
oci_execute($stid);
while (($row = oci_fetch_array ($stid, OCI_ASSOC+OCI_RETURN_NULLS))) { // Fetch NULLs
var_dump($row);
}
/*
The above code prints:
array(2) {
[1]=>
string(1) "1"
["NULL"]=>
NULL
}
*/
?>`

Exemplo #5 oci_fetch_array() with `OCI_RETURN_LOBS`

`<?php
/*
Before running, create the table:
CREATE TABLE mytab (id NUMBER, description CLOB);
INSERT INTO mytab (id, description) values (1, 'A very long string');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'SELECT id, description FROM mytab');
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS))) {
echo $row['ID'] . "<br>\n";
echo $row['DESCRIPTION'] . "<br>\n"; // this contains all of DESCRIPTION
}
// Output is:
// 1
// A very long string
oci_free_statement($stid);
oci_close($conn);
?>`

Exemplo #6 oci_fetch_array() with case sensitive column names

`<?php
/*
Before running, create the table:
CREATE TABLE mytab ("Name" VARCHAR2(20), city VARCHAR2(20));
INSERT INTO mytab ("Name", city) values ('Chris', 'Melbourne');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'select * from mytab');
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);
// Because 'Name' was created as a case-sensitive column, that same
// case is used for the array index. However uppercase 'CITY' must
// be used for the case-insensitive column index
print $row['Name'] . "<br>\n"; // prints Chris
print $row['CITY'] . "<br>\n"; // prints Melbourne
oci_free_statement($stid);
oci_close($conn);
?>`

Exemplo #7 oci_fetch_array() with columns having duplicate names

`<?php
/*
Before running, create the tables:
CREATE TABLE mycity (id NUMBER, name VARCHAR2(20));
INSERT INTO mycity (id, name) values (1, 'Melbourne');
CREATE TABLE mycountry (id NUMBER, name VARCHAR2(20));
INSERT INTO mycountry (id, name) values (1, 'Australia');
COMMIT;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$sql = 'SELECT mycity.name, mycountry.name
FROM mycity, mycountry
WHERE mycity.id = mycountry.id';
$stid = oci_parse($conn, $sql);
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC);
var_dump($row);
// Output only contains one "NAME" entry:
// array(1) {
// ["NAME"]=>
// string(9) "Australia"
// }
// To query a repeated column name, use an SQL column alias like "AS ctnm":
$sql = 'SELECT mycity.name AS ctnm, mycountry.name
FROM mycity, mycountry
WHERE mycity.id = mycountry.id';
$stid = oci_parse($conn, $sql);
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC);
var_dump($row);
// Output now contains both columns selected:
// array(2) {
// ["CTNM"]=>
// string(9) "Melbourne"
// ["NAME"]=>
// string(9) "Australia"
// }
oci_free_statement($stid);
oci_close($conn);
?>`

Exemplo #8 oci_fetch_array() with DATE columns

`<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Set the date format for this connection.
// For performance reasons, consider changing the format
// in a trigger or with environment variables instead
$stid = oci_parse($conn, "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'");
oci_execute($stid);
$stid = oci_parse($conn, 'SELECT hire_date FROM employees WHERE employee_id = 188');
oci_execute($stid);
$row = oci_fetch_array($stid, OCI_ASSOC);
echo $row['HIRE_DATE'] . "<br>\n"; // prints 1997-06-14
oci_free_statement($stid);
oci_close($conn);
?>`

Exemplo #9 oci_fetch_array() with REF CURSOR

`<?php
/*
Create the PL/SQL stored procedure as:
CREATE OR REPLACE PROCEDURE myproc(p1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN p1 FOR SELECT * FROM all_objects WHERE ROWNUM < 5000;
END;
*/
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$stid = oci_parse($conn, 'BEGIN myproc(:rc); END;');
$refcur = oci_new_cursor($conn);
oci_bind_by_name($stid, ':rc', $refcur, -1, OCI_B_CURSOR);
oci_execute($stid);
// Execute the returned REF CURSOR and fetch from it like a statement identifier
oci_execute($refcur);
echo "<table border='1'>\n";
while ($row = oci_fetch_array($refcur, OCI_ASSOC+OCI_RETURN_NULLS)) {
echo "<tr>\n";
foreach ($row as $item) {
echo " <td>".($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;")."</td>\n";
}
echo "</tr>\n";
}
echo "</table>\n";
oci_free_statement($refcur);
oci_free_statement($stid);
oci_close($conn);
?>`

Exemplo #10 oci_fetch_array() with a LIMIT-like query

`<?php
$conn = oci_connect('hr', 'welcome', 'localhost/XE');
if (!$conn) {
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// This is the query you want to execute
$sql = 'SELECT city, postal_code FROM locations ORDER BY city';
// This nested query selects a subset of rows from $sql.
// In production environments, be careful to avoid SQL Injection
// issues with concatenated SQL statements
$limit_sql =
'select *
from ( select a.*, rownum as rnum
from (' . $sql . ') a
where rownum < :FIRST_ROW + :NUM_ROWS )
where rnum >= :FIRST_ROW';
$first = 1; // start with the first row
$num = 5; // return 5 rows
$stid = oci_parse($conn, $limit_sql);
oci_bind_by_name($stid, ':FIRST_ROW', $first);
oci_bind_by_name($stid, ':NUM_ROWS', $num);
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC))) {
echo $row['CITY'] . " " . $row['POSTAL_CODE'] . "<br>\n";
}
// Output is:
// Beijing 190518x
// Bern 3095x
// Bombay 490231x
// Geneva 1730x
// Hiroshima 6823x
oci_free_statement($stid);
oci_close($conn);
?>`

### Notas

> Nota: Associative array indices need to be in uppercase for standard Oracle columns that were created with case insensitive names.

> Nota: For queries returning a large number of rows, performance can be significantly improved by increasing oci8.default_prefetch or using oci_set_prefetch().

> Nota: The function oci_fetch_array() is insignificantly slower than oci_fetch_assoc() or oci_fetch_row(), but is more flexible.

### Veja Também

- oci_fetch() - Fetches the next row from a query into internal buffers
- oci_fetch_all() - Fetches multiple rows from a query into a two-dimensional array
- oci_fetch_assoc() - Returns the next row from a query as an associative array
- oci_fetch_object() - Returns the next row from a query as an object
- oci_fetch_row() - Returns the next row from a query as a numeric array
- oci_set_prefetch() - Sets number of rows to be prefetched by queries

oci_fetch_all
oci_fetch_assoc
Funções para OCI8
PHP Manual