Page 1 of 1
Insert a Blob using an Oracle Package
Posted: Wed 22 Dec 2010 05:28
by 1222tmiller
I used the package wizard to stub out a procedure. I am saving a zip file to the blob. When I download the zip, it is corrupt.
Here is the code:
aOraLob: TOraLob;
begin
aOraLob := nil;
try
aOraLob := TOraLob.Create(Self.Session.OCISvcCtx);
aOraLob.AllocLob;
aOraLob.CreateTemporary(ltClob);
aOraLob.LoadFromFile(FileName);
try
BeginExecPLSQL;
ClientidParam := AddParam('CLIENT_ID', TFieldType(ftInteger), ptInput);
ClientidParam.AsInteger := ClientId;
SvncommitcommentParam.AsString := SvnCommitComment;
FilestreamParam := AddParam('FILE_STREAM', ftOraBlob, ptInput);
FilestreamParam.AsOraBlob := aOraLob;
aOraLob.WriteLob;
ExecProc('INSERTNEWDONORDIRECTEXE');
finally
EndExecPLSQL;
end;
finally
aOraLob.FreeTemporary;
aOraLob.FreeLob;
aOraLob.Free;
end;
What am I doing wrong?
Posted: Wed 22 Dec 2010 08:49
by AlexP
hello,
Please try to execute the following code:
begin
OraSession1.ExecSQL('create or replace PROCEDURE proc_btest_blob (in_param in blob, out_param out blob) ' + #13 +
'AS ' + #13 +
'BEGIN ' + #13 +
' out_param := in_param; ' + #13 +
'END;', []);
OraStoredProc1.Options.TemporaryLobUpdate := true;
OraStoredProc1.Prepare;
OraStoredProc1.ParamByName('in_param').AsOraBlob.LoadFromFile('file1.zip');
OraStoredProc1.ExecProc;
OraStoredProc1.ParamByName('out_param').AsOraBlob.SaveToFile('file2.zip');
end;
and compare the files.
If files are different, please send a complete sample project to alexp*devart*com to demonstrate the problem including a script to create the table and procedure.
Also please specify the following information:
- the exact version of ODAC;
- the exact version of your IDE.
What about a Package
Posted: Wed 22 Dec 2010 14:57
by 1222tmiller
That worked fine for a procedure. What about a Package?
Please provide similar code for this package (TIA):
CREATE OR REPLACE PACKAGE MY_TEST_PKG
AS
PROCEDURE proc_btest_blob (in_param in blob, out_param out blob);
end MY_TEST_PKG;
/
CREATE OR REPLACE PACKAGE BODY MY_TEST_PKG
AS
PROCEDURE proc_btest_blob (in_param in blob, out_param out blob)
AS
BEGIN
out_param := in_param;
END proc_btest_blob;
end MY_TEST_PKG;
Posted: Thu 23 Dec 2010 08:25
by AlexP
Hello,
The code for the package will be the same as for the procedure.
Code: Select all
OraSession1.ExecSQL('CREATE OR REPLACE PACKAGE MY_TEST_PKG'+#13+
'AS'+#13+
'PROCEDURE proc_btest_blob (in_param in blob, out_param out blob);'+#13+
'end MY_TEST_PKG;',[]);
OraSession1.ExecSQL('CREATE OR REPLACE PACKAGE BODY MY_TEST_PKG'+#13+
'AS'+#13+
'PROCEDURE proc_btest_blob (in_param in blob, out_param out blob)'+#13+
'AS'+#13+
'BEGIN'+#13+
'out_param := in_param;'+#13+
'END proc_btest_blob;'+#13+
'end MY_TEST_PKG;',[]);
OraStoredProc1.StoredProcName:='MY_TEST_PKG.proc_btest_blob';
OraStoredProc1.Prepare;
OraStoredProc1.ParamByName('in_param').DataType:= ftOraBlob;
OraStoredProc1.ParamByName('in_param').ParamType := ptInput;
OraStoredProc1.ParamByName('out_param').DataType:= ftOraBlob;
OraStoredProc1.ParamByName('out_param').ParamType := ptOutput;
OraStoredProc1.Options.TemporaryLobUpdate := true;
OraStoredProc1.Prepare;
OraStoredProc1.ParamByName('in_param').AsOraBlob.LoadFromFile('e:\1.zip');
OraStoredProc1.ExecProc;
OraStoredProc1.ParamByName('out_param').AsOraBlob.SaveToFile('e:\2.zip');