ORA-01722 error while trying to use TOracleDirectPathLoader

[sblackmore]

I am attempting to use your direct path loader to load a numeric value into a number column.

I am loading data from an ASCII file into a string array, defining the loader columns ( all with dataType := dpString) , preparing the loader, performing all the "setDatas" (pointing the data to the elements of the string array) all with no problem.

However when I call the loaders Load method I get an ora-01722 error ("Invalid Number"). My error dialogue shows that the "suspect" data value is 81.

Any Ideas ?

I have looked at your supplied demo program at it uses a dataType of dpInteger. I particularly want to use dpString (as your documentation suggests) because I want the flexibility. Do you have any demos or examples using this technique ?

Many thanks in anticipation

 

[Marco Kalter]

I don't have an example. Can you show me the Delphi code that does the SetData calls?

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

 

[sblackmore]

Here is the relevant procedure :-

procedure TMainForm.loadDataBlock ();
Var R,F :Integer;
lsName,lsTemp :string;
lpDataVal

ointer;
Begin
// loaders columns collection starts at zero
// gDirLoadBuf starts at one
// first "point" the data loader columns to the memory array
For R := 0 to giCurAryInd-1 do begin
For F := 1 to gMaxFldNo do begin
lsTemp := gDirLoadBuf[F,R];
lpDataVal := @gDirLoadBuf[F,R];
dpLoader.Columns[F-1].SetData(R,lpDataVal,length(lsTemp));
end;
end ;
// Now load the data into the database
begin
try
dpLoader.Load(giCurAryInd);
except
on E:EOracleError do
begin
ShowMessage(E.Message + #13#10 +
'Row = ' + IntToStr(dpLoader.LastRow) + #13#10 +
'Col = ' + IntToStr(dpLoader.LastColumn)+ #13#10 +
'data = ' + gDirLoadBuf[dpLoader.LastColumn +1 ,dpLoader.LastRow]);
dpLoader.Abort;
raise;
end;
end;
end;
glInsertCount := glInsertCount +giCurAryInd ;
end;
//=======================================

Hope this helps

Steve

 

[Marco Kalter]

Thanks. Can you also show me the declaration of gDirLoadBuf?

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

 

[sblackmore]

Here are the relevant declarations plus the procedure that prepares the loader (in case you need it).

Steve

==============================================
Type DataRecType = record
fldName :string;
dataType :string ;
value :string ; // Used by single record insert mode
varArray :variant; // Used by PL/SQL Array insert mode
end;

// Global variables
gDirLoadBuf :array of array of string;
gFldBuf : array [1..999] of DataRecType;
gMaxFldNo :integer ;
giCurAryInd :integer;

procedure TMainForm.initDataLoader;
Var C :Integer;
Begin
setLength(gDirLoadBuf,(gMaxFldNo+1),64);
dpLoader.TableName := (cmbTargetTableOwner.Text +'.'+cmbTargetTableName.Text);
dpLoader.Columns.Clear;
// loop sets up the loader columns
For C := 0 to gMaxFldNo-1 do begin
dpLoader.Columns.Add(gFldBuf[C+1].fldName);
dpLoader.Columns[C].DataType :=dpString;

if gFldBuf[C+1].dataType = 'DATE' then
dpLoader.Columns[C].DateFormat := grdFieldMapping.Cells[5,C+1] ;

dpLoader.Columns[C].DataSize := getColumnLength(
cmbTargetTableOwner.Text
,cmbTargetTableName.Text
, gFldBuf[C+1].fldName );
end;
dpLoader.Prepare;
End;

 

[Marco Kalter]

I see that gDirLoadBuf is an array of strings. Therefore you must pass the address of the first character of each string to SetData. This little modification might do the trick:

lpDataVal := @gDirLoadBuf[F,R][1];

I hope this helps.

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

[This message has been edited by mkalter (edited 24 October 2003).]

 

[sblackmore]

Thanks that works a treat !

Steve