Re: ORA-01704: string literal too long AND PHP/PEAR

[Christopher Jones <Christopher.Jones@oracle.com>]

Roger Spears wrote:


> I'm using PHP/PEAR to execute my queries on an oracle table. I'm trying 
> to store very long pieces of text into a CLOB field. Currently I'm 
> getting a string too long error.

> I've tried:
> OCIBindByName($sql,":fieldName",&$myVariable, -1);
>
> But I'm not sure I'm using it correctly with PEAR.  In my code, I have 
> my sql statement, then I do the OCIBindByName() call, then I do my insert.
> But, this gets me:
> ocibindbyname(): supplied argument is not a valid OCI8-Statement resource


Roger,

PEAR DB is not particularly LOB friendly.

Below is a quick CLOB example using PHP's OCI8 API.

If you need to use PEAR DB because of existing code commitments, there
is a hack that might work.  In PEAR DB, the PHP OCI8 connection can be
retrieved and used to call PHP OCI8 functions directly.  Accessing
PEAR's internals is not something to be done lightly.  It may cause
short term problems and not be portable in the long term.  I do not
recommend it.

Using this method, my sample script would be coded something like:

  require_once("DB.php");

  $db = DB::connect("oci8://scott:tiger@mydb");

  // Delete any existing CLOB
  $stid = $db->prepare('DELETE FROM MYCLOBTAB');
  $db->execute($stid);

  // Now upload lob
  $conn = $db->connection;  // Bad!!
  $lob = OCINewDescriptor($conn, OCI_D_LOB);
  ... Code similar to that below ...

  // Now query the uploaded CLOB and display it
  ... More PEAR DB calls using $db->whatever()

An alternative is to use a package like ADOdb.  See
http://phplens.com/lens/adodb/docs-adodb.htm

Chris


-----

<?php

//
// Sample form to upload and insert data into an ORACLE CLOB column
// using PHP's Oracle 8 API.
//
// Based on http://www.php.net/manual/en/function.ocinewdescriptor.php
// modified to work on CLOBs and using register_globals = Off.
//
// Before running this script, execute these statements in SQL*Plus:
//   drop table myclobtab;
//   create table myclobtab (c1 number, c2 clob);
//
// Make sure php.ini's value for upload_max_filesize is large enough
// for the largest lob to be uploaded.
//
// Tested with PHP 4.3.3 against Oracle 9.2.0.4
//

if (!isset($_FILES['lob_upload'])) {
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST" enctype="multipart/form-data">
Upload file: <input type="file" name="lob_upload">
<input type="submit" value="Upload">
</form>

<?php
}
else {

  $myid = 1; // should really be a unique id e.g. a sequence number

  $conn = OCILogon('scott', 'tiger', 'mydb');

  // Delete any existing CLOB so the query at the bottom
  // displays the new data

  $query = 'DELETE FROM MYCLOBTAB';
  $stmt = OCIParse($conn, $query);
  OCIExecute($stmt, OCI_COMMIT_ON_SUCCESS);
  OCIFreeStatement($stmt);

  // Insert the CLOB from PHP's tempory upload area

  $lob = OCINewDescriptor($conn, OCI_D_LOB);
  $stmt = OCIParse($conn, 'INSERT INTO MYCLOBTAB (C1, C2) VALUES('.
                   $myid . ', EMPTY_CLOB()) RETURNING C2 INTO :C2');
  OCIBindByName($stmt, ':C2', $lob, -1, OCI_B_CLOB);
  OCIExecute($stmt, OCI_DEFAULT);

  // The function $lob->savefile(...) reads from the uploaded file.
  // If the data was already in a PHP variable $myv, the
  // $lob->save($myv) function could be used instead.
  if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) {
    OCICommit($conn);
    echo "Clob successfully uploaded\n";
  }
  else {
    echo "Couldn't upload Clob\n";
  }
  $lob->free();
  OCIFreeStatement($stmt);

  // Now query the uploaded CLOB and display it

  $query = 'SELECT C2 FROM MYCLOBTAB WHERE C1 = '.$myid;

  $stmt = OCIParse ($conn, $query);
  OCIExecute($stmt, OCI_DEFAULT);
  OciFetchInto($stmt, $arr, OCI_ASSOC);
  $result = $arr['C2']->load();

  echo '<pre>';
  echo $result;
  echo '</pre>';

  OCIFreeStatement($stmt);

  OCILogoff($conn);
}
?>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php