Sending SQL to Oracle

 oci_execute() 

This section again uses the quotes table, which also includes an identity column; however, this is a bit more complicated to implement with Oracle. Refer to the script quotes.oracle.sql in the download archive for more information.

Sending SQL to Oracle (oci_execute.php; excerpt)

 <?php   if ($db = @oci_connect('scott', 'tiger', 'orcl'))     {     require_once 'stripFormSlashes.inc.php';     $sql = 'INSERT INTO quotes (quote, author, year)       VALUES (:quote, :author, :year)';     $stmt = oci_parse($db, $sql);     oci_bind_by_name($stmt, ':quote',       $_POST['quote']);     oci_bind_by_name($stmt, ':author',       $_POST['author']);     oci_bind_by_name($stmt, ':year',       intval($_POST['year']));     oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);     echo 'Quote saved.';     oci_close($db);   } else {     echo 'Connection failed.';   } ?> 

To send SQL to Oracle, two steps are required. First, a call to oci_parse() parses an SQL string and returns a resource that can then be executed using oci_execute(). The second parameter of oci_execute() is quite important. Several constants are allowed, but most of the time, OCI_DEFAULT is used. Despite the name, that's not the default value, but means "no autocommit." On the other hand, OCI_COMMIT_ON_SUCCESS commits the pending transaction when no error has occurred. And this is, indeed, the default value.

Unfortunately, there is no such thing as oci_escape_string() to escape special characters for use in an SQL statement. Therefore, prepared statements are a mustbut are also very easy to implement. For this, the SQL statement must contain placeholders that start with a colon:

 $sql = 'INSERT INTO quotes (quote, author, year)   VALUES (:quote, :author, :year)'; 

Then, these placeholders have to be filled with values. For this, oci_bind_by_name() must be used:

 oci_bind_by_name($stmt, ':quote', $_POST['quote']); 

The preceding code sends some form data to the database. No worries about special characters because oci_bind_by_name takes care of that.

NOTE

When you are using OCI_DEFAULT as the commit mode, the changes must be written to the database using oci_commit($db); oci_rollback($db) performs a rollback.

TIP

By the way, if you want to retrieve the autovalue of the most recent INSERT operation, you have to do it within a transaction and execute SELECT quotes_id.CURVAL AS id FROM DUAL, where quotes_id is the name of the sequence you are using.