PHP : Function Reference : Oracle Functions : ocifetchinto PHP Examples Tutorials References

gid

With oracle 9i, setting the NLS_DATE_FORMAT variable under Linux didn't want to work with command line php. I tried export before running php, and putenv inside of php. So I used the ALTER SESSION command instead to make the date fields return the date AND time by default. Running the query "ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'" worked like a charm. And it remains effective throughout entire oracle session.

maxwell_smart

When using OCI_RETURN_LOBS to get a BFILE (stored with a DIRECTORY) the user needs READ on the DIRECTORY. (GRANT READ on DIRECTORY <directory name> TO <user>;) Otherwise, you'll get a cryptic error. Warning: OCILobFileOpen: ORA-22285: non-existent directory or file for FILEOPEN operation in ... on line ... The user that CREATEs the DIRECTORY is automatically GRANTed READ WITH THE GRANT OPTION.

kboyer

When using ocifetchinto, be careful with the resulting array. If the array variable is previously populated before calling ocifetchinto, and the the ocifetchinto command returns no (0) rows, the array is not overwritten. This can make you think that you actually got rows returned when you actually didn't.

php

There might be a better way. <?php $conn = OCILogon('user', 'secret', 'DB'); $th = 0; // Table Header $query = 'select * from PAYMENT'; $stid = OCIParse($conn, $query); OCIExecute($stid); echo "<table>\n\n"; while (OCIFetchInto($stid, $row, OCI_ASSOC)) { if (!$th) { $keys = (array_keys($row)); echo "\n<tr>\n"; foreach ($keys as $k) {echo "<th>" . $k . "</th>\n";} echo "</tr>\n"; $th = 1; // Table header done ! } echo "\n<tr>\n"; foreach ($keys as $k) {echo "<td>" . $row[$k] . "</td>\n";} echo "</tr>\n"; $count = $count + 1; } echo "</table>\n\n"; echo "<h3>" . $count . " records</h3>"; OCILogoff($conn); ?>

guillaume cocatre-zilgien

The use of ocifetchinto() with the OCI_ASSOC parameter is not suitable for queries with aliased columns: the columns' real names, *not* their aliases, are used as keys in the associative array, causing some values to be overwritten. <?php $sql = "select a.id, b.id from ref a, library b where a.id = b.id"; // ...parsed and executed... ocifetchinto($stmt, $row, OCI_ASSOC); /* print_r($row) will return only one value instead of two: Array ( [ID] => 948 ) */ ?>

test1

The returned associative array should have the keys in all uppercase. Otherwise, you will get only null values.

mckay salisbury

test1 said that it has to be in all caps, but that isn't the case. Oracle is case sensitive, but it has weird case sensitivity behavior. oracle takes all identifiers, and "To_Upper"s them, unless they are in double quotes, which it treats as exact case (Single quotes are used for string delimiters) then a table with the name "BLAH" having columns "Blah1", "BLAH2", and "BLAH3" could be accessed by the SQL statement select "Blah1", blah2, "BLAH3" from bLaH; which is the same as select "Blah1", "BLAH2", bLaH3 from blah; (note that there is only one way to reference something that is stored in mixed or lower case, i.e. in its proper case, with strings around it so if a table is created with mixed case and double quotes, then that's how they'll come out of the orifetchinto statement and they will not be all upper case, but most of the time Oracle DBAs (especially dba's recently transferred from a different DBMS) tend to leave the double quotes off all the time, and it appears as if oracle is case insensitive.

russ

In response to an easy way to return an object instead of an array, simply take the array returned, for example: ocifetchinto($stmt, $myarray, OCI_ASSOC); $myobject = (object)$myarray; It will automatically cast into stdClass, and assign a member for each key in $myarray. You can cast like this with anything, including from object back to array.

cjbj

In Oracle's terms a column alias is a temporary name assigned to a column. In "SELECT EMP.ENAME EX1 FROM EMP" the column name is "ENAME" and the column alias is "EX1". Associative arrays can be used with duplicate columns if the columns have aliases. In the example below the aliases id1 and id2 have been added. <?php $sql = "select a.id id1, b.id id2 from ref a, library b where a.id = b.id"; // ...parsed and executed... ocifetchinto($stmt, $row, OCI_ASSOC); Array ( [ID2] => 948 [ID1] => 948 ) */ ?>

jafar78

Hi, the following is a simple connect and select which creates a single elment array. One can also create a multiDementional array out of this. I hope this can help. <?php $DB_USER="DB user name"; $DB_PASSWD="DB passwd"; $DB="TNS name of your DB"; $connection = OCILogon ($DB_USER, $DB_PASSWD, $DB); if ($connection == false){ echo OCIError($connection)." "; exit(); } $ColName="your col name"; $TableName="your table name"; $YourOrderby="your order by col"; $query = "SELECT $ColName from $TableName ORDER BY $yourOrderby"; $cursor = ociparse($connection, $query); ociexecute($cursor); $Rrows = ocifetchstatement($cursor, $results); echo "Found: $Rrows results \n"; $myarray = array (); for ($i = 0; $i < $Rrows; $i++) { $myarray[$i]= $results["$ColName"][$i]; }; //This will print all the elements in the array //or you can print one element=$myarray[0] for ($j=0; $j < $Rrows; $j++){ echo "myarrayElement$j=$myarray[$j] "; }

xerxes

Hi all.... Following an eariler message about having similar functionality to "mysql_fetch_object", here is a snip of code i frequently use to return an array of all rows from a query....each element of the array is an object, where the properties for the object are the columns selected from the query. Note that this function reqs that you pass in a connection handler.... It can be modified to return just a single row, quite easily. function executeSQL($SQL, $oConn) { $cur = OCIParse($oConn, $SQL); if (OCIExecute($cur, OCI_DEFAULT)) { $iCounter = 0; $aGeneric = Array(); $TempClass = new stdClass (); while(OCIFetchInto($cur, $res, OCI_RETURN_NULLS + OCI_ASSOC)) { foreach ($res as $sKey => $sVal) { $TempClass->{$sKey} = $sVal; } $aGeneric[$iCounter] = $TempClass; $iCounter++; } } else { return (false); } return ($aGeneric); } hth! -Xerxes

dan

By default Oracle 8i (and I guess 9i) return date type columns in the format "DD MON YY", which can be a problem if you want the time. You can convert these columns in your SELECT statements with the TO_CHAR function, but it is much nicer to make Oracle return ISO 8601 complient dates! Try setting an environment var for NLS_DATE_FORMAT before you connect to your DB. The following has worked for me in Apache & IIS on Windows... but it might work on *NIX? $datetime = 'YYYY-MM-DD HH24:MI:SS'; putenv("NLS_DATE_FORMAT=$datetime"); You can also set this up in the Windows registry: Key: "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0" Make a new String value called "NLS_DATE_FORMAT" with a value of "YYYY-MM-DD HH24:MI:SS" Hope that this helps someone else.

ocifetchinto

Fetches the next row into an array (deprecated) (PHP 4, PHP 5, PECL oci8:1.0-1.2.4)
int ocifetchinto ( resource statement, array &result [, int mode] )

Code Examples / Notes » ocifetchinto

Change Language

ocifetchinto

Fetches the next row into an array (deprecated) (PHP 4, PHP 5, PECL oci8:1.0-1.2.4) int ocifetchinto ( resource statement, array &result [, int mode] )

Code Examples / Notes » ocifetchinto

Change Language

Fetches the next row into an array (deprecated) (PHP 4, PHP 5, PECL oci8:1.0-1.2.4)
int ocifetchinto ( resource statement, array &result [, int mode] )