Oracle BLOB throws exceptions for column and row validation

[nj1973]

This has been requested by a large bank so is something we should try to prioritise.

Test Oracle table:

create table dvt_test.tab_blob (id number(6), col_blob blob, col_raw raw(64));
insert into dvt_test.tab_blob values (1,empty_blob(),NULL);
insert into dvt_test.tab_blob values (2,utl_raw.cast_to_raw('some binary data'),utl_raw.cast_to_raw('some binary data'));
insert into dvt_test.tab_blob values (3,NULL,NULL);
commit;

Test PostgreSQL table:

create table dvt_test.tab_blob (id decimal(6), col_blob bytea, col_raw bytea);
insert into dvt_test.tab_blob values (1,null,null);
insert into dvt_test.tab_blob values (2,'some binary data'::bytea,'some binary data'::bytea);
insert into dvt_test.tab_blob values (3,null,null);

Column validation

Counts:

data-validation -v validate column -sc=ora_local -tc=pg_local -tbls=dvt_test.tab_blob --count=col_blob,col_raw

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00932: inconsistent datatypes: expected - got BLOB
[SQL: SELECT count(*) AS count, count(t0.col_blob) AS count__col_blob
FROM dvt_test.tab_blob t0]

Sum/min/max:

data-validation -v validate column -sc=ora_local -tc=pg_local -tbls=dvt_test.tab_blob --min=col_blob,col_raw

# No error but the column is skipped

data-validation -v validate column -sc=ora_local -tc=pg_local -tbls=dvt_test.tab_blob --sum=col_blob,col_raw

# No error but the column is skipped

Row validation

Oracle to Oracle validation:

data-validation -v validate row -sc=ora_local -tc=ora_local -tbls=dvt_test.tab_blob \
--primary-keys=id --hash=col_blob

(SELECT t6.id AS id, t6.col_blob AS col_blob, encode(t6.col_blob, :encode_1) AS cast__col_blob
FROM dvt_test.tab_blob AS t6),
t1 AS
...
    cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: ORA-00904: "ENCODE": invalid identifier

PostgreSQL to PostgreSQL is fine:

data-validation -v validate row -sc=pg_local -tc=pg_local -tbls=dvt_test.tab_blob \
--primary-keys=id --hash=col_blob

# success

Once #899 is complete we should add a BLOB column to the new test table.

[nj1973]

I thought I'd found a custom-query row validation workaround for BLOB validation but we are blocked by #992

[nj1973]

For count validation we could enclose the BLOB column in an expression like this which takes care of empty_blob().

nullif(dbms_lob.getlength(col_blob),0)

But this is not trivial because both BLOB and RAW show up as the binary Ibis type. Therefore we need to be able to look at the Oracle type.

[nj1973]

For min/max/sum we could research using the length like we do for strings. That would work for Oracle and PostgreSQL at least, we would need to check on all engines.

[nj1973]

I propose to leave row validation out of this issue because it is already logged: #773