TLOBLocator: Invalid handle

[grahamr]

Hi, I'm trying to retrieve a BLOB from an Oracle9i server. When ever I try to retrive the BLOB it fails with an Invalid handle message. However inspecting the length returned from the PLSQL function returns the correct size, while using the ->Size property fails (Invalid Handle). The only two functions that don't produce an invalid handle are the ->IsNull() (a silly way of doing things if you ask me) and the ->Seek() function.

Any ideas - anything I have done wrong? The Oracle admin says the function is there and working correctly, the data is being returned, but I can't get at it without generating an exception.

// set up code etc.
  ...

  int length;
  pObLocator = new TLOBLocator(Oracle9i, otBLOB);
  pObLocator->SetEmpty();
  OracleQuery1->SetVariable(":ELR", szELR);
  OracleQuery1->SetComplexVariable(":RETBLOB", pObLocator);
  OracleQuery1->Execute();
  length = OracleQuery1->GetVariable("LENGTH");
  if (pObLocator->IsNull() | | length==0)
    {
    delete pObLocator;
    pObLocator = 0;
    throw Exception("Error: Unable to get valid CurveBlob.");
    }

  pObLocator->Seek(0, soFromBeginning);
//  pObLocator->SaveToFile("C:\temp\blob.bin");  // borks
//  int size = pObLocator->Size;  // borks also
  char *pBlob = new char[length];
  pObLocator->Read(&pBlob, length);

------------------
--

Graham Reeds,http://omnieng.co.uk | grahamr@omnieng.co.uk

 

[Marco Kalter]

My guess is that executing the TOracleQuery does not return an (initialized) LOB Locator.

Can you let me know:

1. The SQL of the TOracleQuery.

2. If it calls a program unit, the PL/SQL text of that program unit.

------------------
Marco Kalter
Allround Automations

 

[grahamr]

The code of the Query:

begin
  :RETBLOB := nvsfunctions.get_elr_data (:ELR, :LENGTH);
end;

The code of the unit:

package nvsfunctions is
...
       function get_elr_data (elr_in in varchar2, length_out out number) return blob;

end;
/
package body nvsfunctions is
...
function get_elr_data (elr_in in varchar2, length_out out number)
return blob
is
v_data elr.elr_data%type;
v_data_length number(6);
begin
	select elr_data, elr_data_length
	       into v_data, v_data_length
	       from elr
	       where elr_name = elr_in;

	length_out := v_data_length;

	return v_data;
end;
end;

Hope you can make sense of that code (cos I can't

------------------
--

Graham Reeds,http://omnieng.co.uk | grahamr@omnieng.co.uk

 

[grahamr]

Also before you mention it the ampersand in the final line of my code is a by-product of my clutching-at-straws style of code development. I've also tried starting the count from 1 and increasing the size of the buffer.

 

[grahamr]

Found something out: If you run it again (ie start the process manually without exiting the program) it works fine. This suggests that there is something wrong with the TLOBLocator.

 

[grahamr]

Fixed it.

I needed to have cast the result of a GetComplexVariable to a TLOBLocator like so:

pObLocator = new TLOBLocator(Oracle9i, otBLOB);
  OracleQuery1->SetVariable(":ELR", szELR);
  OracleQuery1->SetComplexVariable(":RETBLOB", pObLocator);
  OracleQuery1->Execute();
  if (pObLocator->IsNull())
    {
    delete pObLocator;
    pObLocator = NULL;
    throw Exception("Error: Unable to get valid CurveBlob.");
    }
  pObLocator = (TLOBLocator *)OracleQuery1->GetComplexVariable(":RETBLOB");
  length = OracleQuery1->GetVariable("LENGTH");
  char *pBlob = new char[length];
  pObLocator->Seek(0, soFromBeginning);
  pObLocator->Read(pBlob, length);

Which now works.

------------------
--

Graham Reeds,http://omnieng.co.uk | grahamr@omnieng.co.uk