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.
If statement corresponds to a PL/SQL block
returning Oracle Database 12c Implicit Result Sets, then rows from
all sets are consecutively fetched. If
statement is returned
by oci_get_implicit_resultset(), then only the
subset of rows for one child query are returned.
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.
반환값
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.
The table name is not included in the array index. If your query
contains two different columns with the same name,
use OCI_NUM or add a column alias to the query
to ensure name uniqueness, see example #7. Otherwise only one
column will be returned via PHP.
$stid = oci_parse($conn, 'SELECT department_id, department_name FROM departments'); oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_BOTH)) != false) { // 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);
?>
Example #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; */
$stid = oci_parse($conn, 'SELECT id, description FROM mytab'); oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_NUM)) != false) { 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);
?>
Example #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; */
/* Before running, create the table: CREATE TABLE mytab (id NUMBER, description CLOB); INSERT INTO mytab (id, description) values (1, 'A very long string'); COMMIT; */
$stid = oci_parse($conn, 'SELECT id, description FROM mytab'); oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_LOBS)) != false) { echo $row['ID'] . "<br>\n"; echo $row['DESCRIPTION'] . "<br>\n"; // this contains all of DESCRIPTION // In a loop, freeing the large variable before the 2nd fetch reduces PHP's peak memory usage unset($row); }
// Output is: // 1 // A very long string
oci_free_statement($stid); oci_close($conn);
?>
Example #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; */
// 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);
?>
Example #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; */
// 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);
// 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);
?>
Example #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; */
// Find the version of the database preg_match('/Release ([0-9]+)\./', oci_server_version($conn), $matches); $oracleversion = $matches[1];
// This is the query you want to "page" through $sql = 'SELECT city, postal_code FROM locations ORDER BY city';
if ($oracleversion >= 12) { // Make use of Oracle 12c OFFSET / FETCH NEXT syntax $sql = $sql . ' OFFSET :offset ROWS FETCH NEXT :numrows ROWS ONLY'; } else { // Older Oracle versions need a nested query selecting a subset // from $sql. Or, if the SQL statement is known at development // time, consider using a row_number() function instead of this // nested solution. In production environments, be careful to // avoid SQL Injection issues with concatenation. $sql = "SELECT * FROM (SELECT a.*, ROWNUM AS my_rnum FROM ($sql) a WHERE ROWNUM <= :offset + :numrows) WHERE my_rnum > :offset"; }
// Requires OCI8 2.0 and Oracle Database 12c // Also see oci_get_implicit_resultset() $sql = 'DECLARE c1 SYS_REFCURSOR; BEGIN OPEN c1 FOR SELECT city, postal_code FROM locations WHERE ROWNUM < 4 ORDER BY city; DBMS_SQL.RETURN_RESULT(c1); OPEN c1 FOR SELECT country_id FROM locations WHERE ROWNUM < 4 ORDER BY city; DBMS_SQL.RETURN_RESULT(c1); END;';