DBD::Oracle::db ora_lob_write failed

From: Ron Reidy (Ron.Reidy_at_arraybiopharma.com)
Date: 12/30/04 

Date: Thu, 30 Dec 2004 07:21:09 -0700
To: <dbi-users@perl.org>

All,

I am getting an error when inserting into a CLOB. The error message is:

DBD::Oracle::db ora_lob_write failed: ORA-01031: insufficient privileges

Here is my code (object names have been changed to protect the innocent):

  my $sth = $dbh->prepare(qq{
    INSERT INTO owner.tab
      (report_header, ctime, file_data)
    VALUES
      (
        SYS.XMLType.CREATEXML(:report_header),
        TO_DATE(:ctime, 'DD/MM/YYYY HH24:MI:SS'),
        EMPTY_CLOB()
      )
    RETURNING ad_id INTO :ad_id
  }) || die $DBI::errstr;

  # bind all parameters ...
  $sth->execute;
  $sth->finish;

  my $sth1 = $dbh->prepare(qq{
    SELECT file_data
    FROM owner.tab
    WHERE ad_id = :ad_id
    FOR UPDATE
  }, {ora_auto_lob => 0}) || die $DBI::errstr;
  $sth1->bind_param(':ad_id', $ad_id);
  $sth1->execute;
  my $lob_locator = $sth1->fetchrow;
  $sth1->finish;

  open F, "<$args{FILE}" || die "cannot open ", $args{FILE}, ": $!";
  use constant CHUNK => 8192;
  my $offset = 1;
  my $length = 0;
  my $buffer = '';
  while ($length = read(F, $buffer, CHUNK))
  {
    $dbh->ora_lob_write($lob_locator, $offset, $buffer);
    $offset += $length;
  }

  close F;

I have INSERT and SELECT privileges granted through a role on the "tab" to the user executing the code. I have also tried granting the privileges directly to the user, but the error persists.

Any hints/help are very appreciated. 

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended 
to be for the use of the individual or entity named above. If you are not the 
intended recipient, please be aware that any disclosure, copying, distribution 
or use of the contents of this information is prohibited. Please notify the
sender  of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.