Delphi sql statement inserting a CLOB into Oracle

from my paq using a dataset,
--> seeking now for an insert-statement

to store

ms : TMemoryStream;
begin
  ms := TMemoryStream.Create;
  try
    memo1.lines.savetostream(ms);
    ms.position := 0; //spool back
    //Supply the parameter
    Dataset.ParamByName('BlobParamName').LoadFromStream(ms);
    Dataset.ExecSQL;
  finally
    ms.free;
  end;
end;

to get

ms : TMemoryStream;
begin
  ms := TMemoryStream.Create;
  try
    TBlobField(Dataset.FieldByName('BlobFieldName')).SaveToStream(ms);
    ms.position := 0; //spool back
    memo1.lines.loadfromstream(ms);
  finally
    ms.free;
  end;
end;

just from head

meikl ;-)

how about if i use pl/sql
how to insert CLOB..

I know that BLOB use to stored picture
and CLOB use for storing character.
Code above is for BLOB can it be use for CLOB?

Also say i have this
query1.Clear;
query1.Add(BEGIN);
query1.Add('Update table1 set column1=:value1, column2=:value2,column3=:value3');
query1.Add(' where column1=:value1;');
query1.Add('IF SQL%IS_EMPTY');
query1.Add('Insert into table1 (column1,column2,column3)');
query1.Add(' values (:value1,:value2,:value3);');
query1.Add('END IF;');
query1.Add('END;');

query1.fieldbyname('column1').AsString := 'testing';
query1.fieldbyname('column2').AsCLOB := 'CLOB';
query1.fieldbyname('column3').AsCLOB := 'CLOB;
quert1.ExecSQL;

>Code above is for BLOB can it be use for CLOB?
there is no differnce, but, clobs can easier transferred usinbg the asText-method of the field or the parameter

meikl  ;-)

you on above code i should do like this?

query1.Clear;
query1.Add(BEGIN);
query1.Add('Update table1 set column1=:value1, empty_blob()) returning column2 into :column2,empty_blob()) returning column3 into :column3');
query1.Add(' where column1=:value1;');
query1.Add('IF SQL%IS_EMPTY');
query1.Add('Insert into table1 (column1,column2,column3)');
query1.Add(' values (:value1,empty_clob()) returning column2 into :column2,empty_clob()) returning column3 into :column3

,:value3);');
query1.Add('END IF;');
query1.Add('END;');

query1.fieldbyname('column1').AsString := 'testing';
query1.fieldbyname('column2').AsText := 'fsdkfjlsdkjf';
query1.fieldbyname('column3').AsText := 'dfsdkfsdkfskdjf';
quert1.ExecSQL;

not sure if this, never tied this way, but could work . . . except

in delphi you must supply the parameters, delphi itself do not know the fieldname in such an anonymous pl/sql-block

you have to set the parameters
like

query1.parambyname('column1').AsString := 'testing';
query1.parambyname('column2').AsText := 'fsdkfjlsdkjf';
query1.parambyname('column3').AsText := 'dfsdkfsdkfskdjf';

if your pl/sql-block itself is valid you could test with sql/plus or TOAD
(currently i can't test this myself)

meikl ;-)