TOracleDirectPathLoader and Dates

[BobGeezer]

Hi All,

Using the loader, we want to load a row with integers, strings and dates/times/timestamps.

Strings and integers are OK. In desperation, we're trying to assign the date values as strings. Is there a simple example of this? Is there any example of dates in direct path loader? The DirectPath project shows only integers and strings; it omits to demonstrate the date or timestamp types.

The field in question is called INSERTDATE TIMESTAMP(3) nullable.

We get error:
ORA-26093: input data column size (19) exceeds the maximum input size (8)

The 19 is the length of the date/time string we supply. Presumably, the 8 is some Oracle date storage type (or maybe a real?)

We have set the column .Datatype to dpString and the .DateFormat to 'DD/MM/YYYY HH24:MI:SS' and the value passed in is '24/06/2002 16:36:15' (without the quotes) so all looks correct, but still this error arises.

 

[Marco Kalter]

Timestamps are not the same as dates. You will have to pass the timestamp values in the format specified in NLS_TIMESTAMP_FORMAT. To force an application specific format, execute an "alter session set nls_timestamp_format = [format]" command.

 

[BobGeezer]

Hi Marco or anyone who's used dates and times,

I've tried loads of things with direct path timestamps and got nowhere. I then discovered that if we accept the limitation of "no milliseconds" then a "date" column will suffice.

However, with ODPL, I find there is no date type (only dpString, dpInteger, dpFloat, dpBinary) so I'm inserting the date/time as a dpString once again.

This gives error:
ORA-26093: input data column size (20) exceeds the maximum input size (8)

This happens when I'm tring to set the date to '17 Oct 2006 17:06:23' using date format 'dd mmm yyyy hh24:mi:ss'. Where am I going wrong please?

s1 := FormatDateTime(
'dd mmm yyyy hh:mm:ss',
qryDataSrc.Fields[Col].AsDateTime
);
odplLoader.Columns[Col].dateformat
:= 'dd mmm yyyy hh24:mi:ss';

I then make a permanent copy of s1's value so that it is still there for the .Load() method. I then call SetDate as follows:

odplLoader.Columns[Col].SetData(
Row, @strg.str[1], length(s1)
);

...where strg.str is the permanent copy...

Please, please, please can you find a single working Delphi example of direct path with dates and times stored in one field?

 

[Marco Kalter]

As stated before, date settings do not apply for timestamps.

Did you set the NLS_TIMESTAMP_FORMAT as suggested?

 

[BobGeezer]

Hi Marco,

Yes, I've tried every possible combination of date formats and every possible NLS_TIMESTAMP_FORMAT and NLS_DATE_FORMAT I can think of including simply using 'DD-MM-YY' ignoring the time and I can't get it to work.

I've given up and I'm now trying to "fit in with Oracle" and use truncated dates: 24-06-02

..but now I get a new error message saying:

ORA-26041: DATETIME/INTERVAL datatype conversion error

Please, has anyone an example of how to direct load dates - even without times? I'm past caring now. This has stalled me for a whole week. It really can't be that hard can it?

Do you want example code?

 

[Marco Kalter]

It turns out that the Direct Path Loader does not use the session's NLS_TIMESTAMP_FORMAT, but always uses the value in the registry. This can cause confusion of course, and I wonder if this might actually be an Oracle bug.

Anyway, here is a working code example:

procedure LoadTimeStamps;
type
  TSRecord = record
    id: integer;
    value: string;
  end;
const
  NumRows = 2;
  TimeStamps: array[0..NumRows - 1] of TSRecord =
  (
    (id:12; value: '19-OCT-2006 10:55:23.123456'),
    (id:23; value: '20-OCT-2006 11:28:11.314159')
  );
var
  Session: TOracleSession;
  Loader: TOracleDirectPathLoader;
  Column1, Column2: TDirectPathColumn;
  ts: Integer;
begin
  // Create a session and logon
  Session := TOracleSession.Create(nil);
  Session.LogonUsername := 'scott';
  Session.LogonPassword := 'tiger';
  Session.LogonDatabase := 'chicago';
  Session.LogOn;
  // Create the loader and setup the columns
  Loader := TOracleDirectPathLoader.Create(nil);
  Loader.Session := Session;
  Loader.TableName := 'TS_LOADTABLE';
  Loader.GetDefaultColumns(False);
  Column1 := Loader.ColumnByName('id');
  Column1.DataType := dpInteger;
  Column2 := Loader.ColumnByName('value');
  // Set the timestamp size so that the string format fits
  Column1.DataType := dpString;
  Column2.DataSize := 40;
  // Load the records
  Loader.Prepare;
  for ts := 0 to NumRows - 1 do
  begin
    Column1.SetData(ts, @TimeStamps[ts].id, 0);
    Column2.SetData(ts, @TimeStamps[ts].value[1], Length(TimeStamps[ts].value));
  end;
  Loader.Load(NumRows);
  // Done
  Loader.Finish;
  Session.Commit;
  Loader.Free;
  Session.Free;
end;

This code assumes that the NLS_TIMESTAMP_FORMAT inthe registry matches the values (e.g. DD-MON-YYYY HH24:MI:SSXFF). It also assumes that you have a table like this:

CREATE TABLE TS_LOADTABLE
(
  ID    NUMBER(4),
  VALUE TIMESTAMP
)

 

[BobGeezer]

Thank you Marco, you are truly a hero!