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



PHP : Function Reference : Oracle Functions : ociexecute

ociexecute

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


Code Examples / Notes » ociexecute

egypt

Whereas MySQL doesn't care what kind of quotes are around a LIKE clause, ociexecute gives the error:
   ociexecute(): OCIStmtExecute: ORA-00904: "NM": invalid identifier
for the following.
<?php
$sql  = "SELECT * FROM addresses "
     . "WHERE state LIKE \"NM\"";  // error!
$stmt = ociparse($conn, $sql);
ociexecute($stmt);
?>
it's fine if you just use single quotes:
   . "WHERE state LIKE 'NM'";
but i think it's interesting that ociparse doesn't say anything


robra

To catch what an error was about, I use a piece of PL/SQl instead of just a SELECT. For instance:
$query="BEGIN
                       SELECT user_id INTO :v_user_id FROM rw_users
                       WHERE gebruikersnaam = LOWER(:p_alias);
                       :ERROR := 0;
                           EXCEPTION
                               when others then
                               :ERROR := 1;
                               :SQLCODE := SQLCODE;
                               :SQLERRM := SUBSTR(SQLERRM,0,300);
                       END;   ";
               $stmt = OCIParse($conn, $query);
               /* Output parameters */
               OCIBindByName($stmt,":ERROR",&$insert_error,32);
               OCIBindByName($stmt,":SQLCODE",&$SQLCODE,32);
               OCIBindByName($stmt,":SQLERRM",&$SQLERRM,300);
               OCIBindByName($stmt,":v_user_id",&$v_user_id,32);
               /*  Input parameters */
               OCIBindByName($stmt,":p_alias", &$p_alias, strlen($p_alias)+2);
               $exec_result = OCIExecute($stmt,OCI_DEFAULT);
Now if the select causes an exception, it is caught and the error message is stored.


flo

Termin.php
<?php
 session_start();
 if(!session_is_registered("db"))
 {
   $_SESSION["user"] = $_POST["user"];
   $_SESSION["pass"] = $_POST["pass"];
   $_SESSION["db"] = $_POST["db"];
 }
 $logdata[0] = $_SESSION["user"];
 $logdata[1] = $_SESSION["pass"];
 $logdata[2] = $_SESSION["db"];
 $conn = OCILogon($logdata[0],$logdata[1],$logdata[2]);
 
 $sql = "Select T.TEID, TEDATUM, TEUhrzeit, TEORT, TEBeschreibung, KLID as GRID, KLBezeichnung as Bezeichnung From TTermine T, TGruppetermin G, TKLASSE K Where T.TEID=G.TEID AND GRID=K.KLID";
 $stmt = OCIParse($conn, $sql);
 OCIExecute($stmt);
 $rows = OCIFetchstatement($stmt,$results);
 
 $keys = array_keys($results);
 $table =  "<table>\n <TR>\n";
 foreach($keys as $key)
 {
     $table .= "  <TH>$key</TH>\n";
 }
 $table .= " </TR>\n";
 for($i=0;$i<$rows;$i++)
 {
     $table .= " <TR>";
     foreach($results as $spalte)
     {
         $data = $spalte[$i];
         $table .= "  <TD>$data</TD>";      
     }
     $table .=" </TR>";
 }
 $sql = "Select T.TEID, TEDATUM, TEUhrzeit, TEORT, TEBeschreibung, BIID as GRID, BIBezeichnung as Bezeichnung From TTermine T, TGruppetermin G, TBILDUNGSGANG B Where T.TEID=G.TEID AND GRID=B.BIID";
 $stmt = OCIParse($conn, $sql);
 OCIExecute($stmt);
 $rows = OCIFetchstatement($stmt,$results);
 for($i=0;$i<$rows;$i++)
 {
     $table .= " <TR>";
     foreach($results as $spalte)
     {
         $data = $spalte[$i];
         $table .= "  <TD>$data</TD>";      
     }
     $table .=" </TR>";
 }
 $sql = "Select T.TEID, TEDATUM, TEUhrzeit, TEORT, TEBeschreibung, BEID as GRID, BEBezeichnung as Bezeichnung From TTermine T, TGruppetermin G, TBERUFSFELD B Where T.TEID=G.TEID AND GRID=B.BEID";
 $stmt = OCIParse($conn, $sql);
 OCIExecute($stmt);
 $rows = OCIFetchstatement($stmt,$results);
 for($i=0;$i<$rows;$i++)
 {
     $table .= " <TR>";
     foreach($results as $spalte)
     {
         $data = $spalte[$i];
         $table .= "  <TD>$data</TD>";      
     }
     $table .=" </TR>";
 }
 $table .="</TABLE>";
 /***************************/
 //echo "$table";
 /***************************/
 $sql = "Select BIID From TBILDUNGSGANG";
 $stmt = OCIParse($conn, $sql);
 OCIExecute($stmt);
 $rows = OCIFetchstatement($stmt,$results);
 $biid = $results[BIID];
 $select = "<SELECT NAME=\"biid\">\n";
 foreach($biid as $data)
 {
     $select .= " <OPTION VALUE=\"$data\">$data</OPTION>\n";
 }
 $select .= "</select>";
 //echo $select;
 
 echo <<< HTML
 <HTML>
 <HEAD>
   <TITLE>TERMIN.PHP</TITLE>
 </HEAD>
 <BODY>
   <H1>Willkommen bei Termin.PHP</H1>
   Hier sind alle termine
$table
   

Sie k&ouml;nnen sich Termine von Klassen bestimmter Bildungsga&auml;nge ansehen. Einfach ausw&auml;hlen.
   <form action="$PHP_SELF" method="post">
   $select
<input type="submit">
   </form>
HTML;
 $biid = $_POST["biid"];
 if(isset($biid))
 {
     $sql = "Select klid, KlBezeichnung, T.TEID, TEDATUM, TEUHRZEIT, TEORT, TEBeschreibung from Tklasse K, TGruppetermin G, TTermine T where Grid=BIID AND T.TEID=G.TEID AND BIID='$biid'";
     $stmt = OCIParse($conn, $sql);
     OCIExecute($stmt);
     $rows = OCIFetchstatement($stmt,$results);
     $keys = array_keys($results);
     $table =  "<table>\n <TR>\n";
     foreach($keys as $key)
     {
         $table .= "  <TH>$key</TH>\n";
     }
     $table .= " </TR>\n";
     for($i=0;$i<$rows;$i++)
     {
         $table .= " <TR>";
         foreach($results as $spalte)
         {
             $data = $spalte[$i];
             $table .= "  <TD>$data</TD>";      
         }
         $table .=" </TR>";
     }
     if($rows>0)
     {
         echo $table;
     }
 }
 echo "</BODY>\n</HTML>";
?>


jicurito

regarding egypt note on double quotes, the reason for that behaviour is that Oracle treats things with double quotes as identifiers on a given statement... using single quotes won't provoque mistakes...

kobbe

If you use OCI_DEFAULT and OCIRollback, don't forget that a simple SELECT-statement works with an commit that has also an effect on all previously statements.

los olvidados

Every time you call ociexecute(), the setting for autocommit may change.  If you don't want autocommit on, be sure to use OCI_DEFAULT for every call to ociexecute.

alexander dot zimmer

Addition to the posting of cthrall@rocketmail.com (from 06-Sep-2000):
It's true, you can't call OCIBindByName in a loop and bind to an associative array like this:
 foreach ($array as $key => $val)
 {
   OCIBindByName($state, $key, $val, -1);
 }
BUT: You can do it this way:
 foreach ($array as $key => $val)
 {
   OCIBindByName($state, $key, $array[$key], -1);
 }
Background: the command OCIBindByName BINDS a PHP variable to a parameter, not just its current value. Thus, it is up to you to make sure that the variable has the correct value at the very time when OCIExecute is called, not just when OCIBindByName was called! The PHP variable gets evaluated at the point of OCIExecute and not earlier.
This is somewhat more legible than cthrall's solution (but it surely works, too).


jgrosland



There seems to be some interesting behavior when using OCI_DEFAULT as the second parameter to ociexecute...  once the autocommit behavior is turned off for a connection, it remains off for the life of that connection.  


This tends to be a problem when using OCIPLogon, as a SQL statement may remain outstanding over the life of many webpages.


This is most problematic when you're doing an update or insert, and a table remains locked while that connection is waiting for a commit/rollback.  Be sure to explicitly commit or rollback if you use OCI_DEFAULT at any point.


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