LOBs as out parameters in oracle external procedure

[zobbi1]

Hi all,

I am developing an external procedure to perform some complicated image processing. More specifically

1. I have a table (A) storing small images as BLOBs

2. Based on different criteria a number of images are selected from the table

3. These images must be combined together to produce a large image

In order to achieve this I have developed a delphi dll which exports a function (cdecl calling convention) that essentially runs the selection query, gather images together and produces a new large image.

procedure ExtractRaster(Context: Pointer);cdecl;

After creating the library and the necessary PL/SQL specs everything is ok

( Create or replace library RasterUtils as 'c:\xxx\rasterUtils.dll'

Create or replace procedure ExtractRaster as
language c
name "ExtractRaster"
library rasterUtils
PARAMETERS(CONTEXT)
with context;
)

The problem is if I want to get the large image back in PL/SQL. i.e. create a procedure in PL/SQL like this :

create or replace procedure DoSomethingWithLargeImage as
aBLOB BLOB;
begin
ExtractRaster(aBLOB);
dbms_lob.getLength(aBLOB);
etc .......
...
...
end;

where ExtractRaster must now be defined as
something like this

procedure ExtractRaster(Context: Pointer;aLocIndicator : word;var aLocator : OCILobLocator);cdecl;
begin
end;

returning the image as a LobLocator

with the pl/sql specification modified like this

Create or replace procedure ExtractRaster(aimage out BLOB) as
language c
name "ExtractRaster"
library RasterUtils
PARAMETERS(CONTEXT, aImage INDICATOR, aImage OCILobLocator)
with context;


I am not quite sure how to do it, and I am a bit confused. I gave it some attempts but none seemed to succeed.

Thanks for any suggestions

George

 

[Marco Kalter]

Unfortunately this is currently not supported. You can only pass scalar data types such as strings, numbers, and dates.

 

[Max K.]

Hello George!

Recently I found your post and it makes me happy! You've succeded in passing BLOB to Delphi external procedure from Oracle!
I've been searched for that example for last 3 days. For me passing a BLOB to delphi extproc is realy a current hot problem.

It seems passing LOB back to PL\SQL is not possible using DOA, but may be free NCOCI library with OCI API wrappers for Delphi can be useful.
Its homepage da-soft.com is not available now but if you want I can send you zip.

Can I please you to send me some sources (or may be declaration) of your procedure ExtractRaster(Context: Pointer);cdecl; ? I mean declaration block in Delphi library.

Although you described here general way I extremly want to see full example of your code.

Max.

Originally posted by zobbi1:
Hi all,

I am developing an external procedure to perform some complicated image processing. More specifically

1. I have a table (A) storing small images as BLOBs
2. Based on different criteria a number of images are selected from the table
3. These images must be combined together to produce a large image

In order to achieve this I have developed a delphi dll which exports a function (cdecl calling convention) that essentially runs the selection query, gather images together and produces a new large image.

procedure ExtractRaster(Context: Pointer);cdecl;

After creating the library and the necessary PL/SQL specs everything is ok

( Create or replace library RasterUtils as 'c:\xxx\rasterUtils.dll'

Create or replace procedure ExtractRaster as
language c
name "ExtractRaster"
library rasterUtils
PARAMETERS(CONTEXT)
with context;
)

The problem is if I want to get the large image back in PL/SQL. i.e. create a procedure in PL/SQL like this :

create or replace procedure DoSomethingWithLargeImage as
aBLOB BLOB;
begin
ExtractRaster(aBLOB);
dbms_lob.getLength(aBLOB);
etc .......
...
...
end;

where ExtractRaster must now be defined as
something like this

procedure ExtractRaster(Context: Pointer;aLocIndicator : word;var aLocator : OCILobLocator);cdecl;
begin
end;

returning the image as a LobLocator

with the pl/sql specification modified like this

Create or replace procedure ExtractRaster(aimage out BLOB) as
language c
name "ExtractRaster"
library RasterUtils
PARAMETERS(CONTEXT, aImage INDICATOR, aImage OCILobLocator)
with context;


I am not quite sure how to do it, and I am a bit confused. I gave it some attempts but none seemed to succeed.

Thanks for any suggestions

George

 

[Max K.]

George,
for speed you can reply me directly by mail
kmsmail.ru