Jump to content
sasan

extract BLOB from ORDIMAGE

Recommended Posts

Hello

Happy new year :)

I want to read image store in oracle 11g databse as ORDIMAGE (type),

oracle client, OLEDB and c++ language used for this job but error is ORDIMAGE is unknown and I can't access image field.

The question is how can extract BLOB from ORDIMAGE using convert/function/procedure/etc... ?

Regards, 

sasan.

Share this post


Link to post
Share on other sites

Hi Sasan, and welcome to the forum!

I found this;

“When a LOB that is being initialized is a persistent LOB (a column in the table), the LOB locator points to a location where LOB content can be written to. So processCopy can write to the BLOB. This is what happens in your example that works.

When a LOB that is being initialized is not persistent (for example, is a variable in a PL/SQL program), then the LOB locator is not pointing to any location. A temporary LOB has to be created for this LOB locator. 

l_thumb := ORDSYS.ORDImage.Init();
dbms_lob.createTemporary(l_thumb.source.localData, true);

-- processCopy

-- don't forget to free the space when done
dbms_lob.freeTemporary(l_thumb.source.localData);

Now you would not have to create a column just to be a destination for the processCopy.

You could also directly processCopy to the destination BLOB (blob_thumb in your example), using the relational interface for processCopy.

This will avoid a copy from the local variable to the destination BLOB, and the overhead of creating and deleting the temporary lob.

blob_thumb := empty_blob(); -- to get the LOB locator
processCopy(l_orig.source.localData, 'maxscale=128x128',blob_thumb);  “
 

Share this post


Link to post
Share on other sites

Hello

I found this document:

https://docs.oracle.com/database/121/AIVUG/ch_imgref.htm#AIVUG6000

so write this function to read ORDImage as BLOB:

CREATE OR REPLACE FUNCTION get_blob(IDR IN NUMBER) 
   RETURN BLOB
   AS

   image ORDSYS.ORDImage;
   result BLOB;
  
   BEGIN 
        SELECT ORDIM_FIELD INTO image FROM ORDIM_TABLE
        WHERE ID_ROW = IDR;
        
        result := image.getContent();
   
        RETURN(result); 
   END;  
   

that wrok fine, but I want to select ORDImage directly without function like this:

select image.getContent() 
    from 
    (select  
        (select ORDIM_FIELD from ORDIM_TABLE where ID_ROW = 1) image 
            from dual) 

and got error: 
"[Error] Execution (1: 28): ORA-00904: "IMAGE"."GETCONTENT": invalid identifier"

Is any solution for this issue ?

Regards,
sasan.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×