title: Compare a BLOB image to images stored as ORDImage using SQL/MM Still Image
body: <p>I am using <b>Oracle 11g r2</b>.</p>
  
  <p>I have a table that stores images as <b>ORDImage</b> :</p>
  
  <pre><code>PHOTOS (phot_id integer
          , phot_filename varchar2(256)
          , phot_source ordsys.ordimage)
  </code></pre>
  
  <p>And another temporary table that stores an image uploaded by a user as <b>BLOB</b>.</p>
  
  <pre><code>INSERT_TEMP (itemp_id integer, itemp_source blob)
  </code></pre>
  
  <p>I want to move the BLOB image to the PHOTOS table only if does not already exists, by comparing the two images. I need to use <b>SQL/MM Still Image</b> methods as ORDImageSignature methods are deprecated in Oracle 11g.</p>
  
  <p>Here is the code :</p>
  
  <pre><code> declare
      [...]
   begin
      [...]
      -- get the blob from the temporary table (in_id passed as parameter)
      select itemp_source into l_img_blob from insert_temp where itemp_id = in_id;
      -- build the stillimage object from the blob
      l_img_obj := new si_stillimage(l_img_blob);
      -- get image features and build the featureList object
      l_avgcolor := new si_averagecolor(l_img_obj);
      l_colorhist := new si_colorhistogram(l_img_obj);
      l_poscolor := new si_positionalcolor(l_img_obj);
      l_texture := new si_texture(l_img_obj);
      l_featurelist := new SI_FeatureList(l_avgcolor, 1, l_colorhist, 1, l_poscolor, 1, l_texture, 1);
      -- check if a similar image already exists
      select count(*) into l_exist from photos p where SI_ScoreByFtrList(l_featurelist, SI_MkStillImage1(p.phot_source.source.localdata)) = 0;
      if (l_exist &gt; 0) then
         out_message := app_util.get_translated_message('ERR_SIMILAR_PHOTO_ALREADY_EXISTS');
      else
         /* here the blob is inserted into the PHOTOS table as ORDImage successfully */
         out_message := app_util.get_translated_message('SUC_PHOTO_INSERTED');
      end if;
   end;
  </code></pre>
  
  <p>The image is inserted as ORDImage successfully if I omit the comparison, else an exception is raised (<b>sqlcode: 1, sqlerrm: User-defined Exception</b>), using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE it tells me the following :</p>
  
  <blockquote>
    <p>ORA-06512: à "ORDSYS.SI_STILLIMAGE", ligne 27<br>
    ORA-06512: à "ORDSYS.SI_MKSTILLIMAGE1", ligne 6<br>
    ORA-06512: à "SURV.APP_CORE", ligne 212 </p>
  </blockquote>
  
  <p>line 212 is the line that checks if a similar image already exists :</p>
  
  <pre><code> select count(*) into l_exist
   from photos p 
   where SI_ScoreByFtrList(l_featurelist, SI_MkStillImage1(p.phot_source.source.localdata)) = 0;
  </code></pre>
  
  <p>It seems the problem is that it does not accept <code>p.phot_source.source.localdata</code> as parameter. Do you have any idea on how I can solve this ?</p>
  
  <p>I have also tried :</p>
  
  <pre><code> select count(*) into l_exist 
   from photos p
   where l_featurelist.si_score(new si_stillimage1(p.phot_source.source.localdata)) = 0;
  </code></pre>
  
  <p>Thank you !</p>