Insert into a CLOB field

[Richard Coplan]

I am having problems with Delphi 7 and Oracle 10g using DOA 4.0.7.1
I am getting an invalid handle error when trying to write to a CLOB field. I created a test app to try and pinpoint the problem. Please can you advise, what is wrong with the following code:

procedure TForm1.DoInsertBLOB(aFileName: string);
var
  LOB: TLOBLocator;
  aStr: string;
  aStrList: TStringList;
  buffer: PChar;
begin
  aStrList := TStringList.Create;
  LOB := TLOBLocator.Create(OracleSession1, otCLOB);
  try
    OracleQuery1.SQL.Clear;
    OracleQuery1.SQL.Add('INSERT INTO RPCTEST(DESCRIPTION, CHARDATA)');
    OracleQuery1.SQL.Add(' VALUES (:ADESC, EMPTY_CLOB())');
    OracleQuery1.SQL.Add(' returning CHARDATA into :CHARDATA');
    OracleQuery1.DeclareVariable('ADESC', otString);
    OracleQuery1.DeclareVariable('CHARDATA', otClob);
    OracleQuery1.SetComplexVariable('CHARDATA', LOB);
    OracleQuery1.SetVariable(':ADESC', 'Hello');
    aStrList.LoadFromFile(aFileName);
    aStr := aStrList.Text;
//    buffer := PChar(aStr);
//    LOB.Write(buffer^, Length(aStr) + 1);
    LOB.AsString := aStr;
    OracleQuery1.Execute;
  finally
    aStrList.Free;
    LOB.Free;
  end;
end;

I have tried both the above methods of writing the string to the LOB but both methods fails with the Invalid handle error.

 

[Marco Kalter]

For standard LOB Locators you can only write the data after inserting the record:

procedure TForm1.DoInsertBLOB(aFileName: string);
var
  LOB: TLOBLocator;
  aStr: string;
  aStrList: TStringList;
  buffer: PChar;
begin
  aStrList := TStringList.Create;
  LOB := TLOBLocator.Create(OracleSession1, otCLOB);
  try
    OracleQuery1.SQL.Clear;
    OracleQuery1.SQL.Add('INSERT INTO RPCTEST(DESCRIPTION, CHARDATA)');
    OracleQuery1.SQL.Add(' VALUES (:ADESC, EMPTY_CLOB())');
    OracleQuery1.SQL.Add(' returning CHARDATA into :CHARDATA');
    OracleQuery1.DeclareVariable('ADESC', otString);
    OracleQuery1.DeclareVariable('CHARDATA', otClob);
    OracleQuery1.SetComplexVariable('CHARDATA', LOB);
    OracleQuery1.SetVariable(':ADESC', 'Hello');
    // First insert...
    OracleQuery1.Execute;
    // ...then write the LOB data
    aStrList.LoadFromFile(aFileName);
    aStr := aStrList.Text;
    LOB.AsString := aStr;
  finally
    aStrList.Free;
    LOB.Free;
  end;
end;

If you use a Temporary LOB Locator (CreateTemporary constructor), you can omit the returning clause and write the data before the insert:

procedure TForm1.DoInsertBLOB(aFileName: string);
var
  LOB: TLOBLocator;
  aStr: string;
  aStrList: TStringList;
  buffer: PChar;
begin
  aStrList := TStringList.Create;
  LOB := TLOBLocator.CreateTemporary(OracleSession1, otCLOB, True);
  try
    OracleQuery1.SQL.Clear;
    OracleQuery1.SQL.Add('INSERT INTO RPCTEST(DESCRIPTION, CHARDATA)');
    OracleQuery1.SQL.Add(' VALUES (:ADESC, EMPTY_CLOB())');
    OracleQuery1.DeclareVariable('ADESC', otString);
    OracleQuery1.DeclareVariable('CHARDATA', otClob);
    OracleQuery1.SetComplexVariable('CHARDATA', LOB);
    OracleQuery1.SetVariable(':ADESC', 'Hello');
    aStrList.LoadFromFile(aFileName);
    aStr := aStrList.Text;
    LOB.AsString := aStr;
    OracleQuery1.Execute;
  finally
    aStrList.Free;
    LOB.Free;
  end;
end;