Problem selecting ROWID from Index Organized Table

[horrendo]

Unlike 'regular' tables, the value returned by the pseudo column ROWID for IOT's is not of fixed length.

I have an IOT with a multi-column primary key. When I try to select rows from the table and include ROWID, I get the following error:

DBD::Oracle::st fetchall_arrayref failed: ORA-01403: no data found (DBD ERROR: ORA-01406 error on field 4 of 4, ora_type 104) [for Statement "SELECT  t.*, t.rowid
FROM    t_iot t
"] at ./t-iot.plx line 60.

I'm not sure how to attach a test case so here's a simple self-contained perl script to reproduce the error:

#!/usr/bin/env perl
use strict;
use warnings;
use DBI;
use DBD::Oracle;
use Time::HiRes qw(gettimeofday);
use POSIX qw(strftime);

use constant { MICRO_TO_HUNDREDTH => 10_000, };

sub log_it {
    my ( $sec, $usec ) = gettimeofday;
    print {*STDERR} sprintf( '%s.%02d ', strftime( '%d-%b-%Y %T', localtime($sec) ), ( $usec / MICRO_TO_HUNDREDTH ) ), @_, "\n";
    return;
}

sub create_table {
    my $dbh = shift;
    local $dbh->{'RaiseError'} = 0;
    local $dbh->{'PrintError'} = 0;
    log_it('Dropping table');
    $dbh->do(q(drop table t_iot));
    log_it('Creating table');
    $dbh->do(<<'END_SQL');
create table t_iot(
    c1      varchar2(30)
    ,c2     timestamp(6)
    ,c3     number
    ,constraint i_iot_pk primary key(c1, c2, c3)
    )
    organization index
        compress 2
END_SQL
    log_it('Populating table');
    $dbh->do(<<'END_SQL');
insert
into    t_iot
values  (rpad('a', 30, 'a'), current_timestamp, 1/81)
END_SQL
    log_it('Setup complete');
    return;
}

sub main {
    log_it('Using DBD::Oracle version ', $DBD::Oracle::VERSION);
    log_it('Connecting');
    my $dbh = DBI->connect( 'dbi:Oracle:', $ENV{'ORAPWD'}, '', );
    log_it('Connected !');

    create_table($dbh);
    log_it('Table created and populated');

    my $sql = <<'END_SQL';
select  t.*
from    t_iot t
END_SQL

    my $sth = $dbh->prepare($sql);
    log_it('Prepared (no rowid)');
    $sth->execute;
    log_it('Executed (no rowid)');
    my $data = $sth->fetchall_arrayref;
    log_it('Fetched ', (scalar @{$data}), ' rows (no rowid)');

    $sql = <<'END_SQL';
select  rowid
from    t_iot
END_SQL

    $sth = $dbh->prepare($sql);
    log_it('Prepared (rowid)');
    $sth->execute;
    log_it('Executed (rowid)');
    $data = $sth->fetchall_arrayref;
    log_it('Fetched ', (scalar @{$data}), ' rows (rowid)');

    $dbh->disconnect;
    log_it('Disconnected');
    return 0;
} ## end sub main

exit main();

My output (connecting to an Oracle 11.2.0.4 DB using the 12.1 instant client on RHEL6) is:

[stbaldwin@audev04 dev][1]☢ ORAPWD=xxx/yyy@aulevdev ./t-iot.plx
10-Jul-2015 11:52:54.90 Using DBD::Oracle version 1.74
10-Jul-2015 11:52:54.90 Connecting
10-Jul-2015 11:52:54.98 Connected !
10-Jul-2015 11:52:54.98 Dropping table
10-Jul-2015 11:52:55.12 Creating table
10-Jul-2015 11:52:55.19 Populating table
10-Jul-2015 11:52:55.20 Setup complete
10-Jul-2015 11:52:55.20 Table created and populated
10-Jul-2015 11:52:55.21 Prepared (no rowid)
10-Jul-2015 11:52:55.21 Executed (no rowid)
10-Jul-2015 11:52:55.21 Fetched 1 rows (no rowid)
10-Jul-2015 11:52:55.21 Prepared (rowid)
10-Jul-2015 11:52:55.21 Executed (rowid)
DBD::Oracle::st fetchall_arrayref failed: ORA-01403: no data found (DBD ERROR: ORA-01406 error on field 1 of 1, ora_type 104) [for Statement "select  rowid
from    t_iot
"] at ./t-iot.plx line 74.
10-Jul-2015 11:52:55.22 Fetched 0 rows (rowid)
DBI::db=HASH(0x1ce0908)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at ./t-iot.plx line 77.
10-Jul-2015 11:52:55.22 Disconnected

Thanks and regards,

Steve

[mjegh]

Thanks for a very thorough description of your issue and a script to reproduce. I have run it and it behaves the same here. I'm away for the next few days but I'll try and look at it when I get back. I'm terribly forgetful so by all means chase me if you hear nothing by end of Monday.

Martin

[mjegh]

BTW, the problem is ORA 1406 - TRUNCATED as you correctly suggested. If you add {ora_verbose=>6} to the end of your connect call you can see the OCI calls and what happened.

Also,

Describe col #1 type=104((UNKNOWN SQL TYPECODE 104)) Described col 1: dbtype 104((UNKNOWN SQL TYPECODE 104)), scale 0, prec 20, nullok 0, name ROWID : dbsize 74, char_used 0, char_size 0, csid 0, csform 0(0), disize 20

[mjegh]

ocidfn.h:#define SQLT_RDD 104

[mjegh]

A quick hack that will fix it for rowid's less than 100 bytes. There is a better fix but more complicated.

diff --git a/oci8.c b/oci8.c
index 46a69e8..3a93a50 100644
--- a/oci8.c
+++ b/oci8.c
@@ -156,6 +156,7 @@ sql_typecode_name(int dbtype) {
                case 97:        return "CHARZ";
                case 100:       return "BINARY FLOAT oracle-endian";
                case 101:       return "BINARY DOUBLE oracle-endian";
+        case 104:   return "ROWID";
                case 106:       return "MLSLABEL";
                case 102:       return "SQLT_CUR        OCI 7 cursor variable";
                case 112:       return "SQLT_CLOB / long";
@@ -3662,7 +3663,7 @@ dbd_describe(SV *h, imp_sth_t *imp_sth)

           case ORA_ROWID:                              /* ROWID        */
           case 104:                            /* ROWID Desc   */
-            fbh->disize = 20;
+            fbh->disize = 100;
             fbh->prec  = fbh->disize;
             break;
           case 108:                             /* some sort of embedded object */

[horrendo]

That's awesome Martin. Thanks for such a quick response.

I've seen ROWID's from IOT's significantly longer than 100 bytes in some of my 'real life' tables. We don't use them a lot so it seems a shame to incur a large overhead for 'regular' ROWID's (which we do select a lot) just for a handful of these outlier cases.

Without knowing anything about the structure or logic of oci8.c, would it make sense to treat them as a separate case in dbd_describe rather than bundling them in with 'regular' ROWID's? That way we could make these type 104's something like 2000 and not affect 'regular' ROWID's.

No rush on this - we're not sweating on it.

Cheers,

Steve

[mjegh]

Steve, that 100 is just a hack. Oracle does return the size of the rowid (it was 74 I think in your case) and that would be the better solution but more complicated. I was working on the get you going first then look at a longer term fix.

[lavv17]

A simple workaround on the sql side - convert the rowid to a string by appending an empty string literal: select rowid||'' from iot_table