SqlUtil: DML operations fail with BLOB/binary objects in oracle

[pvanek]

Maybe it's related to BLOB content... when I create simple small BLOB (id 0) it's passed through.

Test case:

%new-style

%requires SqlUtil

/*
--drop table t;
create sequence t_s;

create table t (
    id number not null,
    body blob
);

create unique index t_pk on t (id);

insert into t values (0, hextoraw('133af2'));

insert into t select t_s.nextval, body from service_file_resources;

commit;

create table t1 (
    id number not null,
    body blob
);

create unique index t1_pk on t1 (id);

Result:
hash: (id : 0, body : binary object 0x14b0130 (3 bytes))
hash: (id : 41, body : binary object 0x15fd8e0 (8545 bytes))
unhandled QORE System exception thrown in TID 1 at 2016-05-18 09:55:03.586704 Wed +02:00 (CEST) in Datasource::vexec() (/usr/share/qore-modules/0.8.12/OracleSqlUtil.qm:2709, builtin code)
ORACLE-OCI-ERROR: ORA-01041: ORA-01041: internal error. hostdef extension doesn't exist
call stack:
  5: Datasource::vexec() (/usr/share/qore-modules/0.8.12/OracleSqlUtil.qm:2709, builtin code)
  4: <anonymous closure>() (/usr/share/qore-modules/0.8.12/SqlUtil.qm:10793, user code)
  3: AbstractTable::upsert() (/usr/share/qore-modules/0.8.12/SqlUtil.qm:7614, user code)
  2: call_object_method_args() (/usr/share/qore-modules/0.8.12/SqlUtil.qm:7614, builtin code)
  1: Table::methodGate() (/export/home/pvanek/t.q:46, user code)

*/

Datasource ds("oracle:omq2/omq2@xbox");
on_exit {
    ds.rollback();
}

Table src(ds, "t");
Table tgt(ds, "t1");

#printf("UPSERT start\n");
#tgt.upsertFromIterator(src.getRowIterator());
#printf("UPSERT end\n");

SQLStatement stmt = src.getRowIterator();
while (stmt.next()) {
    printf("%n\n", stmt.getValue());
    tgt.upsert(stmt.getValue());
}

[davidnich]

it looks like there are two issues: 1) currently OracleSqlUtil is binding all values twice due to the way the merge stmt is constructed, and it appears that Oracle can only handle a direct bind of only buffer with > 4000 bytes of type LONG or *LOB 2) the oracle driver is performing direct binds for NT_BINARY (-> BLOB, SQLT_BIN)

changing OracleSqlUtil to bind only once results in the following error when binding binary data > 4000 bytes:

ORA-01461: can bind a LONG value only for insert into a LONG column

(the SQLT_* bind buffer type does not seem to have an effect as long as direct binds are used)

Using a binary value <= 4000 bytes is successful with the single bind stmt with the same data types.

[davidnich]

invisible = bug in unreleased functionality