Direct Path Loader.DateFormat

[Hugh.Jones]

Hi, all.

I have been trying, without success, to get the Oracle Loader to load Timestamp fields from Interbase.

It does not look to me as though the Loader's DateFormat makes any difference, but I guess I am missing something.

Oracle shows NLS_DATE_FORMAT as
DD-MON-RR

Oracle Loader DateFormat property ( at design time ) is :
DD-MON-YYYY

And my Delphi Short Date Format is :
ShortDateFormat := 'DD-MMM-YYYY'

I loaded a small Interbase table with the values "Today", "Yesterday" and "Tomorrow" and the Loader reports.

ORA-26093: input data column size (11) exceeds the maximum input size (9)

This suggests to me that the 9 char nls_date_format is still in force.

I stepped through the code and found that the design time setting is lost - the current value is '', so I set the value at run time. This had no effect on the behaviour described.

Now, I can change the Delphi Short Date Format variable to suit the nls parameter easily enough, but I have not yet tackled the time portion of the date; If I insert "Now" into the IB Table, I am back to the same issue.

Would you advise please ? Have I missed something ?

 

[Hugh.Jones]

My Code ----

begin
for Col := 0 to AIBTable.FieldCount - 1 do begin
Size := 0;
PBuf := nil;
ThisField := AIBTable.Fields[Col];
if not ThisField.IsNull then begin
case OracleDirectPathLoader1.Columns[Col].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;
end;
end;

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

 

[Marco Kalter]

We'll look into this. As alternative you can try the DateFormat property at the column level, or set the NLS_DATE_FORMAT with time fraction. For example:

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'

 

[Hugh.Jones]

I tried setting it at column level, and yes, the property value is retained, but it makes no difference.

I have managed to get round the issue by issuing an ALTER SESSION - it is the NLS_TIMESTAMP_FORMAT parameter that works in this case.

I can send you my source code if you want.
let me know if there are any version numbers you need.

Blobs next

Cheers.
Hugh