Sending SQL to Oracle

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.