BLOB

[Doug Hurst]

I have a test stored procedure with two parameters. One is a String and the other is a BLOB. When I look at the Params it says one is V_RECEIPT_NUM is a String and the other, V_BLOB, is an OraBlob. I load the String easily enough

TestProc->Params->ParamValues["V_RECEIPT_NUM"] = "LIN0103050001";

How do I load the BLOB into the other parameter before executing the stored procedure?

All your examples are in Pascal. I the blobpicture example has not been helpful.

Thanks,

Doug Hurst

[Alex]

I suggest you to use the next code as example

Code: Select all

var
  vClob :  TOraLob;
begin
  vClob :=  TOraLob.Create(OraSession.OCISvcCtx);
  vClob.CreateTemporary(ltClob);
  vClob.AsString := 'Test CLOB data';
  vClob.WriteLob;
  OraSQL.ParamByName('P_CLOB_DATA').AsOraClob:= vClob;
  OraSQL.Execute;
end;

And the StoredProc may look like this:

Code: Select all

CREATE PROCEDURE PRC_TEST_ODAC (P_CLOB_DATA IN CLOB) IS
begin
  DoSomethingWith(P_CLOB_DATA);
end;

[Doug Hurst II]

Is there any possibility you could give a coding example for this in C++ instead of Pascal?

Loading an image file (.JPG for example) as the BLOB.

Thanks in advance,

Doug Hurst

[Alex]

Try to use next code

Code: Select all

TOraLob *vCLOB;
  vCLOB = new TOraLob(OraSession->OCISvcCtx);
  vCLOB->CreateTemporary(ltBlob);
  //vCLOB->AsString = "CLOB data";
  vCLOB->LoadFromFile (FileName);
  vCLOB->WriteLob();
  OraSQL->ParamByName("P_CLOB_DATA")->AsOraClob = vCLOB;
  OraSQL->Execute();
  delete vCLOB;

[Doug Hurst II]

The C++ example you sent by e-mail me works fine.

I thought I'd done.

TOraLob *vBLOB = new TOraLob(OraSession1->OCISvcCtx);

and it hadn't compiled, but now it seems to work just fine.

Thanks again!!!!!!!!!!!!

[Doug Hurst II]

I want to post this for other C++ users

// Regular Text
TestProc->Params->ParamValues["V_RECEIPT_NUM"] = "LIN9901050011";
// BLOB
TOraLob *vBLOB;
vBLOB = new TOraLob(OraSession1->OCISvcCtx);
vBLOB->CreateTemporary(ltBlob);
vBLOB->LoadFromFile("");
vBLOB->WriteLob();
TestProc->ParamByName("V_BLOB")->AsOraBlob = vBLOB;
TestProc->ExecProc();
delete vBLOB;

[hannes_a]

I did

Code: Select all

     vClob :=  TOraLob.Create(OraSession1.OCISvcCtx);
     vClob.CreateTemporary(ltClob);
     vClob.LoadFromFile('filename.zip');
     vClob.writeLob;

but during vClob.WriteLob I get this error:

ORA-21560 argument 2 is null, invalid, or out of range
ORA-06512: at SYS.DBMS_LOB line 819.....

what did I wrong ?

[jkowal]

Question to Alex: Whe do not use?

Code: Select all

begin
  OraSQL.ParamByName('P_CLOB_DATA').AsString := 'Test CLOB data'; 
  OraSQL.Execute; 
end;

or

Code: Select all

begin
  OraSQL.ParamByName('P_CLOB_DATA').LoadFromFile('filename.zip');
  OraSQL.Execute; 
end;

[hannes_a]

because

Code: Select all

OraSQL.ParamByName('P_CLOB_DATA').LoadFromFile('filename.zip',ftOraBlob);
OraSQL.Execute; 

causes an error 'Lob locator should be inited'

Question to Alex: Whe do not use?

Code: Select all

begin
  OraSQL.ParamByName('P_CLOB_DATA').AsString := 'Test CLOB data'; 
  OraSQL.Execute; 
end;

or

Code: Select all

begin
  OraSQL.ParamByName('P_CLOB_DATA').LoadFromFile('filename.zip');
  OraSQL.Execute; 
end;

[Plash]

CLOB field can contain only character data. You cannot store ZIP file in CLOB because it is binary. Try to write ZIP file to a BLOB field instead of CLOB.
You can use the code:

Code: Select all

begin
  OraSQL.ParamByName('P_BLOB_DATA').LoadFromFile('filename.zip'); 
  OraSQL.Execute; 
end;

if you set the TemporaryLobUpdate property of TOraSQL to True. In this case a temporary LOB is created automatically.

[hannes_a]

ok, the paramname is just a textcopy.
it is in the storedproc it a BLOB, and in delphi code defined as "OraBlob"

Code: Select all

object OraQuery2: TOraQuery
    Session = OraSession1
    SQL.Strings = (
      'declare'
      '   lob_var BLOB := NULL;'
      'begin'
      '   importZipContent(:lob_var);'
      'end;')
    Left = 436
    Top = 216
    ParamData = 
  end

and
LoadFromFile need a second parameter !

Code: Select all

procedure LoadFromFile(const FileName: String; BlobType: TBlobType);

did someone ever tried this ?

feel free to try

Code: Select all

CREATE OR REPLACE PROCEDURE importZipContent (lob_var blob) IS
tmpVar NUMBER;
BEGIN
   tmpVar := 0;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE;
END importZipContent;
/

[hannes_a]

ODAC has here an error !
Please can someone of Devart check this ?

[Plash]

Try the following:

1. Correct your SQL. You don't need to declare variables for parameters. You should use a SQL like the following:

Code: Select all

BEGIN
  importZipContent(:lob_var);
END;

2. Set the TemporaryLobUpdate option to True.

3. Call the LoadFromFile method with ftOraBlob second parameter:

Code: Select all

OraQuery2.ParamByName('LOB_VAR').LoadFromFile('filename.zip', ftOraBlob);
OraQuery2.Execute;

[hannes_a]

thank you ! It works