RE: OCIDefineObject error

From: Andy Hassall (andy_at_andyh.co.uk)
Date: 06/29/04 

To: "'Jie Zhang'" <jie@best.com>, <dbi-users@perl.org>
Date: Tue, 29 Jun 2004 21:31:38 +0100

Are you sure it's a BLOB?

I notice you're fetching the payload from a queue table; could this queue
table have been set up with an object type? DBD::Oracle 1.15 here has no
problem selecting from a RAW queue, i.e. with a BLOB user_data column, but
fails for an object type as DBD::Oracle doesn't support objects.

e.g.

SQL> create or replace type qobj as object (widget varchar2(10), sprocket
varchar2(10));

Type created

SQL> exec dbms_aqadm.create_queue_table(queue_table => 'QTAB1',
queue_payload_type => 'RAW');

PL/SQL procedure successfully completed

SQL> exec dbms_aqadm.create_queue_table(queue_table => 'QTAB2',
queue_payload_type => 'QOBJ');

PL/SQL procedure successfully completed

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:Oracle:dev92', 'test', 'test',
                       {
                        AutoCommit => 0,
                        RaiseError => 1,
                       })
  or die DBI::errstr;

for (1..2) {
    my $sql = "select user_data from qtab$_";
    print "Preparing `$sql`\n";

    my $sth = $dbh->prepare("select user_data from qtab$_");
    $sth->execute;

    print "OK\n";
}

$dbh->disconnect;

__END__

[andyh@testbox andyh]$ ./qtab.pl
Preparing `select user_data from qtab1`
OK
Preparing `select user_data from qtab2`
Field 1 has an Oracle type (108) which is not explicitly supported at
/usr/lib/perl5/site_perl/5.8.3/i686-linux/DBD/Oracle.pm line 321.
DBD::Oracle::db prepare failed: ERROR OCIDefineObject call needed but not
implemented yet [for Statement "select user_data from qtab2"] at ./qtab.pl
line 17.
DBD::Oracle::db prepare failed: ERROR OCIDefineObject call needed but not
implemented yet [for Statement "select user_data from qtab2"] at ./qtab.pl
line 17.
Issuing rollback() for database handle being DESTROY'd without explicit
disconnect(). 

-- 
Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space> 
> -----Original Message-----
> From: Jie Zhang [mailto:jie@best.com] 
> Sent: 29 June 2004 11:45
> To: dbi-users@perl.org
> Subject: OCIDefineObject error
> 
> Hi,
> 
> I'm trying to retreive a blob field from oracle database.  But I keep 
> getting this error:
> 
> DBD::Oracle::db prepare failed: ERROR OCIDefineObject call needed but 
> not implemented yet [for statement ``select user_data from 
> cmf_balance_qt where state=3 and rownum<3'']) at 
> ./get_blob.pl line 22.
> Can't call method "execute" on an undefined value at 
> ./get_blob.pl line 23.
> 
> user_data is a blob field.
> 
> Here is my code:
> my $query1='select user_data from cmf_balance_qt where state=3 and 
> rownum<3';
> print $query, "\n";
> my $dbh = DBI->connect("DBI:Oracle:$dbname", $username,$pwd) or die 
> "Can't connect to database $dbname: $DBI::errstr";
> 
> $LONG_RAW_TYPE=24; # Oracle type id for blobs
> 
> $dbh->{LongReadLen} = 500000;
> $dbh->{'LongTrun***'} = 1;
> 
> my $sth=$dbh->prepare($query1);
>    $sth->execute;
> 
> Is this an installation problem?  Any help is appreciated.
> 
> thanks,
> 
> Jie