4 Replies Latest reply: Oct 18, 2012 1:27 AM by brianotn RSS

    Extracting BLOB from ORDImage

    brianotn
      Hi

      I need to extract the BLOB from an ORDImage object and store it in the same table (for use in an APEX report).

      I've managed to accomplish this but can't understand why another simpler (to me anyway) approach fails.
      CREATE TABLE ORDImage_images
      (image_id   NUMBER PRIMARY KEY,
       filename   VARCHAR2(20),
       image      ORDSYS.ORDImage, 
       thumbnail  ORDSYS.ORDIMAGE,
       blob_thumb BLOB);
      
      -- load the image and confirm the image is loaded and thumbnail and blob_thumb columns are null 
      
      -- try to extract the BLOB
      DECLARE
        l_image_id INTEGER:= 1;
        l_orig     ORDSYS.ORDImage;
        l_thumb    ORDSYS.ORDImage;
      
      BEGIN
      
        -- lock row
        SELECT image
        INTO l_orig
        FROM ORDImage_images
        WHERE image_id = l_image_id FOR UPDATE;
      
        -- clear thumbnail and blob_thumb columns just in case
        UPDATE ORDImage_images
        SET thumbnail = null, blob_thumb = null
        WHERE image_id = l_image_id;
      
        -- this doesn't work
        l_thumb := ORDSYS.ORDImage.Init();
      
        -- but this does
        /*
        UPDATE ORDImage_images
        SET thumbnail = ORDSYS.ORDImage.Init()
        WHERE image_id = l_image_id;
      
        SELECT thumbnail
        INTO l_thumb
        FROM ORDImage_images
        WHERE image_id = l_image_id;
        */ 
      
        l_orig.processCopy('maxScale=128 128',l_thumb);
      
        UPDATE ORDImage_images
        SET blob_thumb = l_thumb.source.localdata 
        WHERE image_id = l_image_id; 
      
        COMMIT; 
       
      END;
      I get the following error:
      ORA-29400: data cartridge error
      IMG-00710: unable to write to destination image
      ORA-22275: invalid LOB locator specified
      ORA-06512: at "ORDSYS.ORDIMG_PKG", line 1074
      ORA-06512: at "ORDSYS.ORDIMAGE", line 175
      ORA-06512: at line 32
      29400. 00000 -  "data cartridge error\n%s"
      *Cause:    An error has occurred in a data cartridge external procedure.
                 This message will be followed by a second message giving
                 more details about the data cartridge error.
      *Action:   See the data cartridge documentation
                 for an explanation of the second error message.
      I can't see why I need to use the thumbnail column (which I don't have any other use for) to initialise the l_thumb local variable. Why doesn't
      l_thumb := ORDSYS.ORDImage.Init();
      accomplish the same thing? Or am I missing something really obvious?

      Thanks for any help

      Brian

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
        • 1. Re: Extracting BLOB from ORDImage
          mannamal
          ORDSYS.ORDImage.Init() initializes the image object and provides a LOB locator to the BLOB field of the object.

          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.

          For example,

          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);
          -- see http://docs.oracle.com/cd/E11882_01/appdev.112/e10776/ch_relatref.htm#g1116554 (processCopy for BLOBs) for details.

          Finally, if you want to keep the thumbnail image in ORDImage and still use APEX, you could do that - specify to APEX the location of the BLOB in the ORDImage object (by referring to thumbnail.source.localData). Then you could work with image and thumbnail columns in your table, initialize both with ORDSYS.ORDImage.init(), and use processCopy for ORDImage objects.

          Edited by: mannamal on Oct 17, 2012 2:32 PM
          • 2. Re: Extracting BLOB from ORDImage
            brianotn
            Thanks very much - this has been driving me nuts!

            Brian
            • 3. Re: Extracting BLOB from ORDImage
              mannamal
              I just posted an update to my post:

              If you want to keep the thumbnail image in ORDImage and still use APEX, you could do that - specify to APEX the location of the BLOB in the ORDImage object (by referring to thumbnail.source.localData). Then you could work with image and thumbnail columns in your table, initialize both with ORDSYS.ORDImage.init(), and use processCopy for ORDImage objects.
              • 4. Re: Extracting BLOB from ORDImage
                brianotn
                Thanks but I can't seem to be able to specify anything but a BLOB column in a table in APEX

                I've already been down the thumbnail.source.localData route without success

                Brian