How to write LONG RAW data using OCI and Lob locator?

Question

I want to fix zeoslib bug in writing long raw data bug description:

Memo1.Lines.LoadFromFile('c:\t\ZDbcMetadata.pas'); // file size ~ 170Kb
ZQuery1.SQL.Text := 'insert into t1(id, b) values(10, :p1)';
ZQuery1.Params[0].AsBlob := Memo1.Lines.Text;
ZQuery1.ExecSQL;

Problem is that only first 2000 bytes loaded into table

in OCI zeos code do like this:

  var
    sql: string;
    Handle: POCIStmt;
    ErrorHandle: POCIError;
    conn: IZOracleConnection;
    FPlainDriver: IZOraclePlainDriver;
    BindHandle, buff: Pointer;
    Status,buflen: integer;
    lob: POCILobLocator;
  begin
    sql := 'insert into t1(id, b) values(10, :p1)';
    conn := ZConnection1.DbcConnection as IZOracleConnection;
    FPlainDriver := conn.GetPlainDriver;

    with TFileStream.Create('c:\t\ZDbcMetadata.pas', fmOpenRead or fmShareDenyNone) do
    begin
      buflen := Size;
      GetMem(buff, buflen);
      ReadBuffer(buff^, buflen);
      Free;
    end;

    AllocateOracleStatementHandles(FPlainDriver, conn, Handle, ErrorHandle);
    try
      PrepareOracleStatement(FPlainDriver, sql, Handle, ErrorHandle);

      Status := FPlainDriver.DescriptorAlloc(conn.GetConnectionHandle, lob,
        OCI_DTYPE_LOB, 0, nil);
      CheckOracleError(FPlainDriver, conn.GetErrorHandle,
        Status, lcOther, 'Open Large Object');

      Status := FPlainDriver.LobCreateTemporary(conn.GetContextHandle,
        conn.GetErrorHandle, lob, OCI_DEFAULT, OCI_DEFAULT,
        OCI_TEMP_BLOB, True, OCI_DURATION_SESSION);
      CheckOracleError(FPlainDriver, conn.GetErrorHandle,
        Status, lcOther, 'Create Large Object');

      Status := FPlainDriver.LobOpen(conn.GetContextHandle,
        conn.GetErrorHandle, lob, OCI_LOB_READWRITE);
      CheckOracleError(FPlainDriver, conn.GetErrorHandle,
        Status, lcOther, 'Open Large Object');

      Status := FPlainDriver.LobWrite(conn.GetContextHandle,
        conn.GetErrorHandle, lob, buflen, 1,
        buff, buflen, OCI_ONE_PIECE, nil, nil, 0, SQLCS_IMPLICIT);
      CheckOracleError(FPlainDriver, conn.GetErrorHandle,
        Status, lcOther, 'Write Large Object');

      Status := FPlainDriver.LobClose(conn.GetContextHandle,
        conn.GetErrorHandle, lob);
      CheckOracleError(FPlainDriver, conn.GetErrorHandle,
        Status, lcOther, 'Close Large Object');

      Status := FPlainDriver.BindByPos(Handle, BindHandle,
        ErrorHandle, 1, @lob, SizeOf(POCILobLocator),
        SQLT_BLOB, nil, nil, nil, 0, nil,
        OCI_DEFAULT);
      CheckOracleError(FPlainDriver, ErrorHandle, Status, lcExecute, sql);

      ExecuteOracleStatement(FPlainDriver, conn, sql, Handle, ErrorHandle);

      FreeMem(buff);
      FPlainDriver.DescriptorFree(lob, OCI_DTYPE_LOB);
    finally
      FreeOracleStatementHandles(FPlainDriver, Handle, ErrorHandle);
    end;
    conn.Commit;

is it possible write long raw data using lob locator? how?

PS. if table has BLOB field instead of LONG RAW this code works fine.

I just wrote a direct wrapper to OCI, in Open Source. It is much faster than ZEOS/ZDBC version, and is able to work with BLOB parameters (it will use SQLT_BIN up to 2000 bytes, then SQLT_LVB for bigger BLOB content). See http://blog.synopse.info/post/2011/07/09/SynDBOracle%3A-Open-Source-native-Oracle-access — Arnaud Bouchez, Jul 10 '11 at 07:59

score 0 · Answer 1 · answered Mar 24 '11 at 16:15

LONG RAW has been deprecated since Oracle 8, IIRC. There is no advantage in using a LONG RAW column, and you can easily convert them using the TO_LOB function (if you can, of course). There was exactly a 2000 byte limit when loading a raw column through a raw variable or something alike, and I don't know if LOB locators were ever designed to work with RAW types, I guess they could perform some implicit translation, AFAIK RAW types were used without the LOB interface - and I would not mess things together.

score 0 · Answer 2 · answered Jul 10 '11 at 09:06

You should use SQLT_LVB kind of data instead.

As such:

    ftBlob: begin
      oLength := Length(VData);
      if oLength<2000 then begin
        VDBTYPE := SQLT_BIN;
        oData := pointer(VData);
      end else begin
        VDBTYPE := SQLT_LVB;
        oData := Pointer(PtrInt(VData)-sizeof(Integer));
        Inc(oLength,sizeof(Integer));
      end;
    end;

How to write LONG RAW data using OCI and Lob locator?

2 Answers2