BLOB parameters in OracleQuery

[Krzysztof]

Hi,

I am using:
DOA 4.0.7.1
OCI: version 8.0
Oracle Database 9i

I have OracleQuery with following SQL

begin
BLOB_TOOLS.BLOB_TESTP_ROWID,_BLOB);
end;

_ROWID is a string, but _BLOB is a blob parameter.

I try set this BLOB parameter in the following manner:


LOB := TLOBLocator.Create(OracleSession1, otBLOB);
LOB.LoadFromFile(Edit1.Text);
OracleQuery1.SetComplexVariable('P_BLOB', LOB);


Unfortunatelly on LoadFromFile I receive exception "TLOBLocator: Invalid handle".

I tried following code, too:

LOB := TLOBLocator.CreateTemporary(OracleSession1, otBLOB, false);
LOB.LoadFromFile(Edit1.Text);
OracleQuery1.SetComplexVariable('P_BLOB', LOB);


But in this case I receive on LoadFromFile exception: TLOBLocator requires Net8 8.1 or later.

Can you show me solution?

Best regards
Krzysztof

 

[Marco Kalter]

There are 2 options:

• Upgrade to Net 8.1 (Oracle 8i Client or later).
  This is probably the best and easiest solution.
• Modify the code so that the BLOB_TOOLS.BLOB_TEST inserts or updates an empty_blob(), return the initialized BLOB to the TLOBLocator, and write the data after the BLOB_TOOLS.BLOB_TEST call. The advantage is that this works on 8.0, but the disadvantage is that it is not as efficient and more complex.

 

[Krzysztof]

Thank you for your answer,

1. I can't upgrade to Net 8.1 - it does not depend on me

2. I tried to implement your second option... so...
I added procedure to BLOB_TOOLS package :

procedure INITBLOB(P_BLOB out blob) is
begin
P_BLOB := EMPTY_BLOB();
end;

I use them to initialize my TLOBLocator (is it right way?)

In Delphi I have:

LOB := TLOBLocator.Create(OracleSession1, otBLOB);

// blob initialization
OracleQuery2.SetComplexVariable('P_BLOB', LOB);
OracleQuery2.Execute;

LOB.LoadFromFile(Edit1.Text);


OracleQuery2 contains INITBLOB invocation.

Everything is fine to LoadFromFile, when I receive ORA-22275 error. It is strange for me because I think about LOB (TLOBLocator variable) as a local variable. So LoadFromFile is a local procedure - database error is surprising to me.

Can you give me some advice?

Best regards
Krzysztof

 

[IngoBfN]

Hello Krzytztof,

perhaps try writing a PL/SQL function with return type LOB - like:

function INITBLOB returns BLOB is
begin
return EMPTY_BLOB();
end;

Kind regards

Ingo

 

[Marco Kalter]

If the procedure updates a record, you would need to do the following:

procedure update_blob(p_rowid in rowid, p_blob in out blob) is
begin
  update my_blob_table
     set blob_column = empty_blob()
   where rowid = p_rowid
         returning blob_column into p_blob;
end;

This returns an initialized BLOB to the caller, which can then write the binary data.

 

[Krzysztof]

Hi,

To IngoBfN:

Thank you for your solution, but it provides to the same error - ORA-22275.

To Marco Kalter:

Many thanks for your engagement with my problem.

Unfortunatelly, I am not interested to put this blob into the table... I want only pass it as parameter to procedure. In this procedure a file is produced (by UTL_FILE) from this BLOB. Using a table is a workaround, which I already have. Due to performance issues I want to change it.

Best regards
Krzysztof

 

[Marco Kalter]

I want only pass it as parameter to procedure. In this procedure a file is produced (by UTL_FILE) from this BLOB.

In that case you will need an Oracle 8.1 client or later. The Oracle 8.0 client does not support the Temporary BLOB's that are required for this.