Problems with a blob output parameter in a Oracle stored procedure
Posted: Thu 05 Jun 2008 16:32
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.
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.