Page 1 of 1

Problems with a blob output parameter in a Oracle stored procedure

Posted: Thu 05 Jun 2008 16:32
by etrabado
Hello,

I'm trying to use an Oracle stored procedure to retrieve some parameters, one of them being a binary file (BLOB type in Oracle).

If I want to upload a binary file into my Oracle database using a BLOB as an input parameter of a stored procedure, it works fine ( sp.Params.ParamByName('data_fichero_in').AsBlob := Copy(Buff , CurrentOffset, ChunkSize))

But if I want to download the binary file from my Oracle database, I need a BLOB output parameter in my Oracle stored procedure. I've been trying different options to do that, but I have not been able to make it work.

If I use a SQL sentence to do the same thing (use a 'SELECT' sentence with a TSQLQuery instead of calling my stored procedure with a TSQLStoredProc), it works properly
(TBlobField(sql.FieldByName('archivo_bin')).SaveToFile(FileNameWithDir)).

But I cannot use a SQL sentence, because there is some addicional work done in the stored procedure.

I am probably doing something wrong, but I cannot see what is it. Could you please send me an example of how to call an Oracle stored procedure with an output BLOB parameter, and save the binary data retrieved from the database to a file?.

Thank you in advance for your prompt attention,

Elvira.

PS: I am using dbexpoda 4.20.4, Oracle 9 and Delphi 2006.

Posted: Fri 06 Jun 2008 07:28
by Plash
Please specify are you using TSQLStoredProc or TSQLQuery component to execute the stored procedure.

TSQLStoredProc component has a bug. It represents BLOB and CLOB parameters are ftBlob instead of ftOraBlob and ftOraClob. Changing DataType of parameters in TSQLStoredProc component is not allowed.

So you should use TSQLQuery component to execute the stored procedure with output BLOB parameter. Assign a text like the following to the SQL property:

Code: Select all

BEGIN
  MY_PROC(:P1, :P_BLOB);
END;
Set DataType property of BLOB parameter to ftOraBlob. Set ParamType to ptOutput.

Still does not work

Posted: Fri 06 Jun 2008 09:10
by etrabado
Hi, Plash

Thanks for your quick response.

As you imagined, I was using TSQLStoredProc to call my Oracle Stored Procedure.

I am now trying to do it with a TSQLQuery as you suggested, but I guess I must be doing something wrong again, because I'm having several problems:

1.- Mysql.ParamByName('NOM_FICH').AsString retrieves only the first character of the file name (but I can see the whole name in my Oracle Log).

2.- I am getting an Access Violation when it executes this line:

TBlobField(Mysql.ParamByName('DATOS_FICH')).SaveToFile(FileNameWithDir);



This is the code that I am trying to execute:

/*******************************/


StrSQL := 'BEGIN ' +
' PKG_CLIENT_INTERFACE.file_get_bin(:ID_FILE, '+
' :COD_SUS, '+
' :NOM_FICH, '+
' :VERSION, '+
' :TIPO, '+
' :FECHA, '+
' :DATOS_FICH, '+
' :RET); '+
'END;';
Mysql := TSQLQuery.Create(Self);

with Mysql do
begin
SqlConnection := DAL.GetConnection (cstrDBName);
SQL.Clear;
SQL.Add(strSQL);
try
ParamByName('ID_FILE').ParamType :=ptInput;
ParamByName('ID_FILE').DataType := ftInteger;
ParamByName('ID_FILE').AsInteger:= 130;

ParamByName('COD_SUS').ParamType :=ptOutput;
ParamByName('COD_SUS').DataType := ftString;

ParamByName('NOM_FICH').ParamType :=ptOutput;
ParamByName('NOM_FICH').DataType := ftString;

ParamByName('VERSION').ParamType :=ptOutput;
ParamByName('VERSION').DataType := ftInteger;

ParamByName('TIPO').ParamType :=ptOutput;
ParamByName('TIPO').DataType := ftString;

ParamByName('FECHA').ParamType :=ptOutput;
ParamByName('FECHA').DataType := ftDateTime;

ParamByName('DATOS_FICH').ParamType :=ptOutput;
ParamByName('DATOS_FICH').DataType := ftOraBlob;

ParamByName('RET').ParamType :=ptOutput;
ParamByName('RET').DataType := ftInteger;

MyLog ('L',SqlConnection.GetLoginUsername+'> '+ strSQL);

ExecSQL;
except
On e : Exception do
begin
MyLog ('E',ClassName+'.GetDataSet:'+e.Message+'.');
end;
end;
end;

FileNameWithDir :=Dir + Mysql.ParamByName('NOM_FICH').AsString;
TBlobField(Mysql.ParamByName('DATOS_FICH')).SaveToFile(FileNameWithDir);

Mysql.Free;
DAL.CerrarConexion(cstrDBName);
except
on e : exception do MyLog('E','Error . ' + e.Message);
end;

/*******************************/

Can you please help me to determine what am I doing wrong?

Thanks again,

Elvira.

Posted: Mon 09 Jun 2008 07:47
by Plash
1. We could not reproduce the problem. Please send to dbxoda*crlab*com a complete small sample that demonstrates the problem, including the script for creating database objects.

2. TParam is not a field. You cannot cast TParam to TBlobField. Use AsString or Value properties, or GetData method of TParam to get its value. Then save it to file using file variable or TFileStream class.