Inserting BLOBs fast?

[DavidH]

Is it possible to speed up inserting of BLOBs using Array DML or DirectPathLoader?

And if it's possible, how do I do it? (Also tell if there are size limits)

 

[DavidH]

Well, I just had to RTFM...

So I tried Direct Path Loading.

Using Oracle 8.1.6 and this table:

create table turer (I integer primary key, J blob not null, K integer,
L integer not null, M integer not null)

Running this code:

DPL1.TableName := 'turer';
DPL1.GetDefaultColumns(False);
DPL1.Prepare;

...the Prepare statement gives me an ORA-01009. Why?

 

[Marco Kalter]

The BLOB column will be defined as a dpBinary column. Therefore you need to specify the maximum data size. After you have done this, you will get another error, because the BLOB column must be the last column definition. If you modify your code like this, it should work:

DPL1.TableName := 'turer';
DPL1.GetDefaultColumns(False);
DPL1.ColumnByName('J').DataSize := 1000;
DPL1.ColumnByName('J').Index := DPL1.Columns.Count - 1;
DPL1.Prepare;

The data size is just an example of course. We will make the GetDefaultColumns procedure more intelligent so that it will automatically define LOB's as the last column.

------------------
Marco Kalter
Allround Automations

 

[DavidH]

Thank you. Now I got the first table going... However, the second one does not work, it throws me an ORA-26081 (at DPL1.Load)

My table definition:

create table hplavg (A integer primary key,B blob, C blob)

I've tried both to
DPL1.ColumnByName('C').Free; (after loading default columns)

...and to insert both blobs at the same time, but neither works. I've also tried to add the columns manually.

[This message has been edited by DavidH (edited 02 October 2000).]

 

[DavidH]

Is it possible to insert BLOBs and/or Long raws using Array DML?

The code below tells me that "only onedimensional arrays are allowed".

// Initialize query
SQL.Text := 'update C set A = :A where B = :B';
DeclareVariable(':A',otLongRaw);
DeclareVariable(':B',otInteger);

// Initialize array
AArr := VarArrayCreate([0,1],varVariant);
AArr[0] := VarArrayCreate([0,5],varByte);
AArr[1] := VarArrayCreate([0,8],varByte);
BArr := VarArrayCreate([0,1],varVariant);
BArr[0] := 8;
BArr[1] := 4711;

// Bind variables and execute query
SetVariable(':B',BArr);
SetVariable(':A',AArr); // Error here
Execute;

//Table definition: create Table C (B Integer primary key, A BLOB)