Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : Oracle Functions : ociparse

ociparse

Alias of oci_parse (PHP 4, PHP 5, PECL oci8:1.0-1.2.4)

Examples ( Source code ) » ociparse

<?php
/* oci_fetch_all example mbritton at verinet dot com (990624) */

$conn oci_connect("scott""tiger");

$stmt oci_parse($conn"select * from emp");

oci_execute($stmt);

$nrows oci_fetch_all($stmt$results);
if (
$nrows 0) {
  echo 
"<table border=\"1\">\n";
  echo 
"<tr>\n";
  foreach (
$results as $key => $val) {
     echo 
"<th>$key</th>\n";
  }
  echo 
"</tr>\n";

  for (
$i 0$i $nrows$i++) {
     echo 
"<tr>\n";
     foreach (
$results as $data) {
        echo 
"<td>$data[$i]</td>\n";
     }
     echo 
"</tr>\n";
  }
  echo 
"</table>\n";
} else {
  echo 
"No data found<br />\n";
}
echo 
"$nrows Records Selected<br />\n";

oci_free_statement($stmt);
oci_close($conn);
?>

Code Examples / Notes » ociparse

27-jan-2006 12:05

one of the most things that is done wrong with oracle is the following.
Cosider:
-------------------------------------------------------------------
$dbh = ocilogon('user', 'pass', 'db');
for ($i = 0; $i<=10; $i++) {
   $sth = ociparse($dbh, 'SELECT * FROM T WHERE x = :x');
   ocibindbyname($sth, ':x', $i, -1);
   ociexecute($sth, OCI_DEFAULT);
   if (ocifetchrow($sth, $row, OCI_ASSOC+OCI_RETURN_NULLS)) {
       var_dump($row);
   }
}
ocilogoff($dbh);
-------------------------------------------------------------------
Problem here is, that you parse the same statement over and over and that'll cost ressources and will introduce many wait events. This problem will increase exponentially with the number of users using your system. That's one of the things besides not using bind variables that will prevent your application from scaling well.
The right approach:
-------------------------------------------------------------------
$dbh = ocilogon('user', 'pass', 'db');
$sth = ociparse($dbh, 'SELECT * FROM T WHERE x = :x');
for ($i = 0; $i<=10; $i++) {
   ocibindbyname($sth, ':x', $i, -1);
   ociexecute($sth, OCI_DEFAULT);
   if (ocifetchrow($sth, $row, OCI_ASSOC+OCI_RETURN_NULLS)) {
       var_dump($row);
   }
}
ocilogoff($dbh);
------------------------------------------------------------------
Now we are parsing the statement once and using it as often as possible.
When your using Oracle, create proper indexes, use bind variables and parse once and execute often. Not doing so will get you into trouble when more than a few users are working with your application simultaneously.


steveg

OCIParse() does return errors, try having a mismatched single quote on an insert, in this case OCIParse returned FALSE, and a call to OCIError($connection) returned the message:  ORA-01756: quoted string not properly terminated
It does seems that OCIParse doesn't catch a whole lot of stuff tho.


chris_maden

It took me a while to work out the semantics of error handling for the Parse/Fetch/Execute seqeunce as well.
OCIParse always returns true, so testing it is a waste of time. Instead, call OCIError, and test the value of the "code" element.
OCINumRows always returns 1, BTW, so you have to loop. There are a number of other postings on this.
&lt;?php
putenv("ORACLE_SID=admin");
putenv("ORACLE_HOME=/home/oracle");
// Kill the internal messages: this is DIY
error_reporting(0);
$conn = OCILogon("scott", "tiger");
$oerr = OCIError();
if ($oerr) {
if ($oerr["code"] == "1017")
echo "Please check your username and password";
else
echo "Error ".$oerr["message"];
exit;
} else {
echo "Hello";
}
// The table name here should be "emp", not "empxx"
$query = sprintf("select * from empxx");
/* Long version */

// Testing the result of the OCIParse call directly doesn't work...
$stmt = OCIParse($conn, $query);
if (!$stmt) {
$oerr = OCIError($stmt);
echo "Fetch Code 1:".$oerr["message"];
exit;
}
// However, if you test this, it performs as you would hope
$oerr = OCIError($stmt);
echo "Fetch Code 2:".$oerr["code"];
if ($oerr["code"]) {
echo "Error:".$oerr["message"];
exit;
}
// Or, you can catch it here (comment out the above block of code)
if (!OCIExecute($stmt)) {
$oerr = OCIError($stmt);
echo "Execute Code:".$oerr["code"];
if ($oerr["code"]) {
echo "Error:".$oerr["message"];
exit;
}
}
OCIFetch($stmt);

$ncols = OCINumCols($stmt);
$nrows = OCIRowCount($stmt);
printf("Result size is $ncols cols by $nrows rows.
");
for ($i=1; $i<=$ncols; $i++) {
printf("col[%s] = %s type[%d] = %s size[%d] = %s
",
$i, OCIColumnName($stmt, $i),
$i, OCIColumnType($stmt, $i),
$i, OCIColumnSize($stmt, $i));
}
$j=1;
do {
for ($i=1; $i<=$ncols; $i++) {
$col = OCIResult($stmt, $i);
printf("val[%d, %d] = %s * ", $j, $i, $col);  
}  
printf("
");  
$j++;
} while (OCIFetch($stmt));
?>


mwd

if you're using "complex" statements e.g such having calls to build in oracle functions in the select list (as in example below), I did not find any other way as using the "AS <Name>" clause to being able to output the functions outcome using ociresult
example:
ociparse($conn,"select EMPNO, LPAD(' ', 2*(LEVEL-1)) || ENAME AS COMPLETE_FANTASY_NAME, JOB, HIREDATE from scott.emp start with job='MANAGER' connect by PRIOR EMPNO = MGR");
echo ociresult $stmt,"COMPLETE_FANTASY_NAME")." ";
BTW: I also found out by TAE that "COMPLETE_FANATASY_NAME" might not be "complete fantasy" as it has to be all capital letters.


ian

Hey, I did get OCIPARSE to return false. It happened during a session where the previous update had failed.
The SQL for my query was valid, but OCIPARSE returned false to tell me that the query could not be executed because of the previous failure.


kurt

For those that are having trouble with error checking, i have noticed on a lot of sites that people are trying to check the statement handle for error messages with OCIParse. Since the statement handle ($sth) is not created yet, you need to check the database handle ($dbh) for any errors with OCIParse. For example:
instead of:
$stmt = OCIParse($conn, $query);
if (!$stmt) {
  $oerr = OCIError($stmt);
  echo "Fetch Code 1:".$oerr["message"];
  exit;
}
use:
$stmt = OCIParse($conn, $query);
if (!$stmt) {
  $oerr = OCIError($conn);
  echo "Fetch Code 1:".$oerr["message"];
  exit;
}
Hope this helps someone.


badr

connecting to oracle database, get the data and insert it into a dynamic drop down box
<?php
 $conn = oci_connect("$username", "$password", 'ora10g');
 if (!$conn) {
  $e = oci_error();
  print htmlentities($e['message']);
  exit;
 }
?>
<SELECT name="name">
                   
                   <?php
$query2 = "SELECT EMP_NAME, EMP_CODE FROM HR_EMP where APP_USER='$suserid' ";
$statement2 = oci_parse ($conn, $query2);
oci_execute ($statement2);
while ($row = oci_fetch_array ($statement2, OCI_NUM)) {
  ?>
                 <option value="<?  echo $row[1]; ?>"> <? echo $row[0] ?> </option>
                   <? }
?>
               </select>


webmaster

$sth = OCIParse ( $dbh, "begin sp_newaddress( :address_id, '$firstname', '$lastname', '$company', '$address1', '$address2', '$city', '$state', '$postalcode', '$country', :error_code );end;" );
This calls stored procedure sp_newaddress, with :address_id being an in/out variable and :error_code being an out variable.  Then you do the binding:
  OCIBindByName ( $sth, ":address_id", $addr_id, 10 );
  OCIBindByName ( $sth, ":error_code", $errorcode, 10 );
  OCIExecute ( $sth );
Hope this helps!


Change Language


Follow Navioo On Twitter
oci_bind_array_by_name
oci_bind_by_name
oci_cancel
oci_close
OCI-Collection->append
OCI-Collection->assign
OCI-Collection->assignElem
OCI-Collection->free
OCI-Collection->getElem
OCI-Collection->max
OCI-Collection->size
OCI-Collection->trim
oci_commit
oci_connect
oci_define_by_name
oci_error
oci_execute
oci_fetch_all
oci_fetch_array
oci_fetch_assoc
oci_fetch_object
oci_fetch_row
oci_fetch
oci_field_is_null
oci_field_name
oci_field_precision
oci_field_scale
oci_field_size
oci_field_type_raw
oci_field_type
oci_free_statement
oci_internal_debug
OCI-Lob->append
OCI-Lob->close
oci_lob_copy
OCI-Lob->eof
OCI-Lob->erase
OCI-Lob->export
OCI-Lob->flush
OCI-Lob->free
OCI-Lob->getBuffering
OCI-Lob->import
oci_lob_is_equal
OCI-Lob->load
OCI-Lob->read
OCI-Lob->rewind
OCI-Lob->save
OCI-Lob->saveFile
OCI-Lob->seek
OCI-Lob->setBuffering
OCI-Lob->size
OCI-Lob->tell
OCI-Lob->truncate
OCI-Lob->write
OCI-Lob->writeTemporary
OCI-Lob->writeToFile
oci_new_collection
oci_new_connect
oci_new_cursor
oci_new_descriptor
oci_num_fields
oci_num_rows
oci_parse
oci_password_change
oci_pconnect
oci_result
oci_rollback
oci_server_version
oci_set_prefetch
oci_statement_type
ocibindbyname
ocicancel
ocicloselob
ocicollappend
ocicollassign
ocicollassignelem
ocicollgetelem
ocicollmax
ocicollsize
ocicolltrim
ocicolumnisnull
ocicolumnname
ocicolumnprecision
ocicolumnscale
ocicolumnsize
ocicolumntype
ocicolumntyperaw
ocicommit
ocidefinebyname
ocierror
ociexecute
ocifetch
ocifetchinto
ocifetchstatement
ocifreecollection
ocifreecursor
ocifreedesc
ocifreestatement
ociinternaldebug
ociloadlob
ocilogoff
ocilogon
ocinewcollection
ocinewcursor
ocinewdescriptor
ocinlogon
ocinumcols
ociparse
ociplogon
ociresult
ocirollback
ocirowcount
ocisavelob
ocisavelobfile
ociserverversion
ocisetprefetch
ocistatementtype
ociwritelobtofile
ociwritetemporarylob
eXTReMe Tracker