Devart Forums

create or replace package TST_BLOB is

function TEST_BLOB return BLOB;

end TST_BLOB;

create or replace package body TST_BLOB is

function TEST_BLOB return BLOB is
v_res BLOB;
begin
dbms_lob.createtemporary(v_res, true, dbms_lob.session);
dbms_lob.writeappend(v_res, 1, '44');
return(v_res);
end;

end TST_BLOB;

Tried to obtain result value in 2 ways.
1. Using TSQLStoredProc.
2. Using TSQLQuery with SELECT TST_BLOB.TEST_BLOB from DUAL.

Results:
1. TestProc.Params[0].AsString is EMPTY!!!!!!
2. TestQuery.Fields[0].AsString is the value as expected. BUT! Temporary LOB is created in this case, and it remains in memory until session ends. You can see it in V$TEMPORARY_LOBS field CACHE_LOBS.

This is not aceptable. What will be your suggestions in this situation ?????

Using:
DbExpODA 2.50.6
Oracle 10.2.0.1 (both client side and server side)
Delphi 7.01

P.S. Delphi codes:

function CreateConnection: TSQLConnection;
begin
Result := TSQLConnection.Create(nil);

Result.LoadParamsOnConnect := False;
Result.SQLHourGlass := False;
Result.LoginPrompt := False;
Result.DriverName := 'Oracle (Core Lab)';

Result.GetDriverFunc := 'getSQLDriverORA';
Result.LibraryName := 'dbexpoda.dll';
Result.VendorLib := 'OCI.DLL';
Result.Params.Text :=
'BlobSize=-1'#13#10+
'ErrorResourceFile='#13#10+
'LocaleCode=0000'#13#10+
'Oracle TransIsolation=ReadCommited'#13#10;

Result.Params.Values['Database'] := 'MYBASE';
Result.Params.Values['Password'] := 'pass';
Result.Params.Values['User_Name'] := 'user';
end;

function CreateQuery(AConnection: TSQLConnection; ACommand: string): TSQLQuery;
begin
Result := TSQLQuery.Create(nil);
with Result do
begin
SQLConnection := AConnection;
SQL.Text := ACommand;
Prepared := True;
end;
end;

function CreateProc(AConnection: TSQLConnection; APackage: string; AProcName: string): TSQLStoredProc;
begin
Result:=TSQLStoredProc.Create(nil);
with Result do
begin
SQLConnection := AConnection;
PackageName := APackage;
StoredProcName := AProcName;
Prepared := True;
end;
end;

procedure TForm1.Button6Click(Sender: TObject);
var
TestQuery: TSQLQuery;
tmp: string;
begin
TestQuery:=CreateQuery(TestConnection, 'select tst_blob.test_blob from dual');
try
TestQuery.Open;
tmp:=TestQuery.Fields[0].AsString;
ShowMessage(tmp);
TestQuery.Close;
finally
TestQuery.Free;
end;
end;

procedure TForm1.Button7Click(Sender: TObject);
var
TestProc: TSQLStoredProc;
begin
TestProc:=CreateProc(TestConnection, 'TST_BLOB', 'TEST_BLOB');
try
TestProc.ExecProc;
ShowMessage(TestProc.Params[0].AsString);
finally
TestProc.Free;
end;
end;

This is restriction of dbExpress that you cannot read BLOB data from parameters.

When you return temporary LOB locator from stored procedure you cannot then free this BLOB because you cannot get its locator.
You can create temporary table, in stored procedure insert BLOB into this table and return value from the table. When you need to free unnecessary data delete rows from the table.