Direct Loader & ORA-01009: missing mandatory parameter

Hugh.Jones · May 29, 2008

Hi all.

Seem to have hit a snag and I am really stuck ....

I am porting from Interbase to Oracle using the direct path loader. Some, though not all, tables with blobs fail with ORA-01009. I have tried all sorts of combinations of buffer size and extra .DataSize to no avail. In my sample code below I double up for good measure.

My Code Excepts on the Last Line (.Prepare()) of the following

Code:

// MAX_BUF_SIZE       = 8192000;

var
  i : integer;
begin
  // Initialise the Loader
  FOraPathLoader             := TOracleDirectPathLoader.Create(Self);
  FOraPathLoader.DateFormat  := ORACLE_DATE_FORMAT;
  FOraPathLoader.BufferSize  := MAX_BUF_SIZE;
  FOraPathLoader.Session     := FOraSession;
  FOraPathLoader.TableName   := ToOraName(AIBTable.TableName);
  FOraPathLoader.GetDefaultColumns(FALSE);
  for i := 0 to AIBTable.Fields.Count - 1 do begin
    if AIBTable.Fields[i].DataType in [ftVarBytes, ftBlob, ftMemo, ftGraphic, ftFmtMemo] then begin
      FIBBlobLenQuery.Sql.Text := Format(BLOB_LEN_SQL,[AIBTable.Fields[i].FieldName, AIBTable.TableName]);
      try // Calculate the largest blob size
        FIBBlobLenQuery.Open;
        FOraPathLoader.Columns[i].DataSize := FIBBlobLenQuery.Fields[0].AsInteger * 2;
        if Assigned(OnMessage) then begin
          OnMessage(self, Format('DataSize = %d',[FOraPathLoader.Columns[i].DataSize]));
          OnMessage(self, Format('Max(BlobSize) = %d',[FIBBlobLenQuery.Fields[0].AsInteger]));
        end;
      finally
        FIBBlobLenQuery.Close;
      end;
    end;
  end;
  FOraPathLoader.Prepare;
end;

My Logs Show :

[May 29 12:08:16:936] ----------------------------------------------------------------
[May 29 12:08:21:733] Creating UPS_UPLOAD_RESPONSES
[May 29 12:08:22:811] UPS_UPLOAD_RESPONSES.FILEDATA is a Blob of Type 1
[May 29 12:08:22:936] Loading UPS_UPLOAD_RESPONSES
[May 29 12:08:23:233] DataSize = 20412
[May 29 12:08:23:248] Max(BlobSize) = 10206
[May 29 12:08:33:983] Server Error : ORA-01009: missing mandatory parameter

------------------------------
Oracle Table is

CREATE TABLE UPS_UPLOAD_RESPONSES
(
UPLOADID NUMBER(15),
INSERTTIME DATE,
INSERTDAY DATE,
PICKUPNO NUMBER(15),
ERRCODE NUMBER(10),
FILEDATA CLOB,
FNAME VARCHAR2(200 BYTE)
)

Interbase Table is :

CREATE TABLE UPS_UPLOAD_RESPONSES (
UPLOADID DOUBLE PRECISION NOT NULL,
INSERTTIME TIMESTAMP NOT NULL,
INSERTDAY TIMESTAMP NOT NULL,
PICKUPNO DOUBLE PRECISION,
ERRCODE INTEGER,
FILEDATA BLOB SUB_TYPE 1 SEGMENT SIZE 50,
FNAME VARCHAR(200) NOT NULL
)

Hugh.Jones · May 30, 2008

This is getting very strange indeed, please if anyone can help with any suggestion - however wacky - I am beginning to lose faith.

(thinking out loud - Is it possible I have a Threading issue ?)

Consider the following fragment - (Same as last example but I have removed the *2 Datasize)

Code:

FIBBlobLenQuery.Open;
        FIBBlobLenQuery.First;
        FOraPathLoader.Columns[i].DataSize := FIBBlobLenQuery.Fields[0].AsInteger;
        if Assigned(OnMessage) then begin
          OnMessage(self, Format('DataSize = %d',[FOraPathLoader.Columns[i].DataSize]));
          OnMessage(self, Format('Max(BlobSize) = %d',[FIBBlobLenQuery.Fields[0].AsInteger]));
        end;

The 2 OnMessage calls result in the following lines in my log

[May 29 16:50:04:186] DataSize = 8
[May 29 16:50:04:326] Max(BlobSize) = 2109

Followed by the dreaded

[May 29 16:50:04:326] Server Error : ORA-01009: missing mandatory parameter

Setting the Datasize property clearly has not worked. Unless I have missed something Datasize and Max(Blobsize) should be the same.

Worse - It SOMETIMES works.

[May 30 08:57:11:592] ALERTS_ACTION_TYPES.ALERTTEXT is a Blob of Type 1
[May 30 08:57:11:811] Loading ALERTS_ACTION_TYPES
[May 30 08:57:12:170] DataSize = 2109
[May 30 08:57:12:170] Max(BlobSize) = 2109
[May 30 08:57:14:701] 39 Rows

Worser - it sometimes fails, even though apparently the Datasize property has been correctly set. (The following example was done with * 2 logic)

[May 30 06:09:45:936] Creating SCANDATA
[May 30 06:09:46:061] SCANDATA.SCANTEXT is a Blob of Type 1
[May 30 06:09:46:264] Loading SCANDATA
[May 30 06:15:41:233] DataSize = 2902
[May 30 06:15:41:451] Max(BlobSize) = 1451
[May 30 06:15:42:248] Server Error : ORA-01009: missing mandatory parameter

Marco Kalter · May 30, 2008

Which Direct Oracle Access version are you using? Depending on the version you may need to specify the owner. For example:

Code:

FOraPathLoader.TableOwner := 'SCOTT.' + ToOraName(AIBTable.TableName);

The owner is the "missing mandatory parameter" here.

Hugh.Jones · May 30, 2008

I will experiment with that, but I dont think it would explain why some tables are working and others are not. (I am logging in as the table owner.)

I have identified (and fixed) the problem. What I have found is that the FOraPathLoader.Prepare() does not guarantee the field order. You will see from my code that I have made the assumption throughout that each IBTable.Fields[n] corresponds to each FOraPathLoader.Columns[n], but this is not the case. The reason the "Datasize :=" appears to fail is because I am not always looking at the field I think I am.

I feel a little foolish because it is basic relational theory that field order is not guaranteed, but it means that your Users' guide example is incorrect too.

Now then - My fix was to use ColumnByName(), but this is a great pity because it is a performance hit on each and every field. If you can suggest another way, I would be most grateful.

An idea is forming, as I write, to create a pair of sorted arrays which can be populated once for each table ... that would do it ...

I hope these code fragments are useful ....

Code:

// -----------------------------------------------------------------------------
procedure TETracLoader.SetData(const AIBTable : TIBTable; const ARow : Integer);
var
  ThisField : TField;
  ThisCol : TDirectPathColumn;
  PBuf: PDataBuffer;
  Size: integer;
  Col : Integer;
  DataRecPtr : PDataRec;
  TempString : String;

begin
  for Col := 0 to AIBTable.FieldCount - 1 do begin
    Size := 0;
    PBuf := nil;
    ThisField := AIBTable.Fields[Col];
    ThisCol := FOraPathLoader.ColumnByName(ToOraField(ThisField.FieldName));
    if not ThisField.IsNull then begin
      case ThisCol.DataType of
        // Character or string field
        dpString        : begin
          TempString := ThisField.AsString;
          Size := Length(ThisField.AsString);
          PBuf := AllocMem(Size);
          Move(PString(@TempString[1])^,PBuf^,Size);
        end;
        // Integers
        dpInteger	: begin
          Size := SizeOf(Integer);
          PBuf := AllocMem(Size);
          PInteger(PBuf)^ := ThisField.AsInteger;
        end;
        // Floating Point numbers
        dpFloat	: begin
          Size := SizeOf(Double);
          PBuf := AllocMem(Size);
          PDouble(PBuf)^ := ThisField.AsFloat;
        end;
        // Blobs 'n' Longs
        dpBinary	: begin // todo :
          TempString := ThisField.AsString;
          Size := Length(ThisField.AsString);
          PBuf := AllocMem(Size);
          Move(PString(@TempString[1])^,PBuf^,Size);
        end;
      else
        raise Exception.CreateFmt('SetData Could Not Find Field %s',[QuotedStr(ToOraField(ThisField.FieldName))]);
      end;
    end;

    if Assigned(PBuf) then begin
      New(DataRecPtr); // remember these to FreeMem() L8tr
      DataRecPtr.Size := Size;
      DataRecPtr.PBuf := PBuf;
      FAllocatedBuffers.Add(DataRecPtr);
    end;
    ThisCol.SetData(ARow, PBuf, Size);
  end;
end;

Code:

// -----------------------------------------------------------------------------
procedure TETracLoader.InitLoader(const AIBTable : TIBTable);
var
  i : integer;
  ThisCol : TDirectPathColumn;
begin
  // Initialise the Loader
  FOraPathLoader            := TOracleDirectPathLoader.Create(nil);
  FOraPathLoader.DateFormat := ORACLE_DATE_FORMAT;
  FOraPathLoader.BufferSize := MAX_BUF_SIZE;
  FOraPathLoader.Session    := FOraSession;
  FOraPathLoader.TableName  := ToOraName(AIBTable.TableName);
  FOraPathLoader.GetDefaultColumns(FALSE);
  for i := 0 to AIBTable.Fields.Count - 1 do begin
    if AIBTable.Fields[i].DataType in [ftVarBytes, ftBlob, ftMemo, ftGraphic, ftFmtMemo] then begin
      FIBBlobLenQuery.Sql.Text := Format(BLOB_LEN_SQL,[AIBTable.Fields[i].FieldName, AIBTable.TableName]);
      try // Calculate the largest blob size
        FIBBlobLenQuery.Open;
        FIBBlobLenQuery.First;
        ThisCol := FOraPathLoader.ColumnByName(ToOraField(AIBTable.Fields[i].FieldName));
        ThisCol.DataSize := FIBBlobLenQuery.Fields[0].AsInteger;
        if Assigned(OnMessage) then begin
          OnMessage(self, Format('DataSize = %d',[ThisCol.DataSize]));
          OnMessage(self, Format('Max(BlobSize) = %d',[FIBBlobLenQuery.Fields[0].AsInteger]));
        end;
      finally
        FIBBlobLenQuery.Close;
      end;
    end;
  end;
  FOraPathLoader.Prepare;
end;

Direct Loader & ORA-01009: missing mandatory parameter

Hugh.Jones

Member²

Hugh.Jones

Member²

Marco Kalter

Administrator

Hugh.Jones

Member²