7 Replies Latest reply: Nov 25, 2012 11:25 PM by Mindmap RSS

    Perform The same processes  twice on different Data. Bug or what ??

    Mindmap
      Hello,
      I am using Oracle 11g R2 SOE. I have three Almost identical tables:
      This table contains images
      TEMP
      (ID number PK,
       CONTENT blob,
       FILENAME varchar2 (200), 
      FILESIZE varchar2 (200),
       MIMETYPE varchar2 (200),
       RES_ID FK)
      I will take the images from TEMP table and watermark, Re-Size them. and store them in IMAGES_TEST TABLE
      IMAGES_TEST 
      (ID number PK,
       CONTENT blob,
       FILENAME varchar2 (200), 
      FILESIZE varchar2 (200),
       MIMETYPE varchar2 (200), 
      RES_ID FK,
      WATERMARKED VARCHAR2 (1))
      Generate thumbnails from the images in TEMP or IMAGES_TEST table and store the thumbnails in IMAGES_T Table
      IMAGES_T
      (ID number PK,
       CONTENT blob,
       FILENAME varchar2 (200), 
      FILESIZE varchar2 (200),
       MIMETYPE varchar2 (200), 
      RES_ID FK,
      IMAGE_ID FK)
      In short, the problem is that I cannot use ORDSYS.ORDImage.process Two times even though it is applied on different variables.
      I need to use it once to re-size the images as following:
      ORDSYS.ORDImage.process(V_source (index), 'fixedScale=800 500');
      Then use it again to create thumbnails as following:
      ORDSYS.ORDImage.process(V_source_T (index), 'fixedScale=150 120');
      Pls, see the next post:
        • 1. Re: Perform two processes on the same data in the memory
          Mindmap
          I managed to Apply watermark and re-size the images FOR ONLY ONE TIME using CODE 2.

          However, to Generate Thumbnails After re-sizing the images I used CODE 1 , but the problem is that the new inserted images into IMAGES_T table have become thumbnails instead of having 800 X 500 (width/height) ????

          h1. CODE 1
          DECLARE
          type source_col is table of blob index by pls_integer ;
          V_source source_col;
          V_source_t source_col;
          
          
          type id_col is table of number index by pls_integer ;
          V_id id_col;
          V_id_t id_col;
          
          
          type char_col is table of IMAGES_TEST.FILE_NAME%TYPE index by pls_integer ;
          V_filename char_col;
          V_mimetype char_col;
          V_filename_t char_col;
          V_mimetype_t char_col;
          
          
          
          
          added_image       BLOB;
          prop ordsys.ord_str_list;
          logging VARCHAR2(2000);
          x integer ;
          begin 
          
          
          
          select content,filename,mimetype,id bulk collect into
           V_source,v_filename,v_mimetype,v_id 
          from TEMP  where  temp = :P700_TEMP_IMAGE
          FOR UPDATE order by id ;
          
          
          SELECT img INTO added_image FROM timg WHERE N = 1;
          
            prop := ordsys.ord_str_list(
                             'position=middlecenter',
                             'transparency=0.5',
                              'width=500',
                              'height=400'
          );
          
          
          for i in V_source.first .. V_source.last 
          loop
          ORDSYS.ORDImage.process(V_source(i), 'fixedScale=800 500');
          ORDSYS.ORDImage.applyWatermark(V_source(i), added_image, V_source(i), logging, prop);
          end loop;
          
          forall i in V_source.first .. V_source.last 
          insert into IMAGES_test 
          (res_id ,CONTENT,filename,mimetype,filesize,watermarked) 
          values 
          (:P700_res_ID ,V_source(i),V_filename(i),
          V_mimetype(i),dbms_lob.getlength(V_source(i)), 'Y');
          COMMIT;
          
          delete TEMP where temp = :P700_TEMP_IMAGE;
          COMMIT;
          
          select content,filename,mimetype,id bulk collect 
          into V_source_t,v_filename_t,v_mimetype_t,v_id_t 
          from images_test  
          where  res_id = :P700_res_ID   for update order by id ;
          
          
          
          for i in V_source_t.first .. V_source_t.last 
          loop
          ORDSYS.ORDImage.process(V_source_t(i), 'fixedScale=150 120');
          end loop;
          
          forall i in V_source_t.first .. V_source_t.last 
          insert into IMAGES_t (res_id ,CONTENT,filename,mimetype,filesize,image_id)
           values
           (:P700_res_ID ,V_source_t(i),V_filename_t(i),
          V_mimetype_t(i),dbms_lob.getlength(V_source_t(i)), V_id_t (i));
           COMMIT;
          
          
          EXCEPTION
             WHEN OTHERS THEN
             RAISE;
          END;
          h1. CODE 2
          DECLARE
          type source_col is table of blob index by pls_integer ;
          V_source source_col;
          
          
          type id_col is table of number index by pls_integer ;
          V_id id_col;
          
          
          type char_col is table of IMAGES_TEST.FILE_NAME%TYPE index by pls_integer ; 
          V_filename char_col;
          V_mimetype char_col;
          
          
          
          
          
          added_image       BLOB;
          prop ordsys.ord_str_list;
          logging VARCHAR2(2000);
          x integer ;
          begin 
          
          select count (nvl(watermarked,1))  into x 
          from TEMP   where  temp = :P700_TEMP_IMAGE ;
           if x > 0 then 
          
          
          select content,filename,mimetype,id bulk collect into
           V_source,v_filename,v_mimetype,v_id from TEMP  
          where  temp = :P700_TEMP_IMAGE   for update order by id ;
          
          
           
          
          
          SELECT img INTO added_image FROM timg WHERE N = 1;
          
            prop := ordsys.ord_str_list(
                             'position=middlecenter',
                             'transparency=0.5',
                              'width=500',
                              'height=400'
          );
          
          
          for i in V_source.first .. V_source.last 
          loop
          ORDSYS.ORDImage.process(V_source(i), 'fixedScale=800 500');
          ORDSYS.ORDImage.applyWatermark(V_source(i), added_image, V_source(i), logging, prop);
          end loop;
          
          forall i in V_source.first .. V_source.last 
          insert into IMAGES_test
           (res_id ,CONTENT,filename,mimetype,filesize,watermarked) 
          values 
          (:P700_res_ID ,V_source(i),V_filename(i),V_mimetype(i),
          dbms_lob.getlength(V_source(i)), 'Y');
          COMMIT;
          
          delete TEMP where temp = :P700_TEMP_IMAGE;
          COMMIT;
          
          
          end if;
          EXCEPTION
             WHEN OTHERS THEN
             RAISE;
          END;
          Any ideas ??

          Regards,
          Fateh

          Edited by: Fateh on Nov 24, 2012 8:27 AM

          Edited by: Fateh on Nov 24, 2012 9:30 PM
          • 2. Re: Perform two processes on the same data in the memory
            rp0428
            >
            I managed to Apply watermark and re-size the images FOR ONLY ONE TIME using CODE 2.

            However, to Generate Thumbnails After re-sizing the images I used CODE 1 , but the problem is that the new inserted images into IMAGES_T table have become thumbnails instead of having 800 X 500 (width/height) ????
            >
            That is correct - you are using BLOB in your code and BLOB is a LOB locator. A locator POINTS TO THE BLOB; the locator isn't a copy of the BLOB. If you want a copy you need to copy it yourself. So after you apply the watermark in the first step you should copy the images and insert them into the other table. Then generate the thumbnails.

            See the PL/SQL Language doc
            http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/toc.htm
            >
            BLOB Data Type

            You use the BLOB data type to store large binary objects in the database, inline or out-of-line. Every BLOB variable stores a locator, which points to a large binary object.

            BLOBs participate fully in transactions, are recoverable, and can be replicated. Changes made by package DBMS_LOB can be committed or rolled back. BLOB locators can span transactions (for reads only), but they cannot span sessions.
            >



            If you are not going to the use the tables in your first post then remove them. Your second post doesn't refer to some of them and it is really confusing trying to understand the table structure you are using since your second post doesn't include some of the table structures but the DDL in the first post is for different tables.

            Also - why are you using two queries to get the data; you query ID in its own query when you can get it in the same query.

            And the column definitions should use %TYPE. You using VARCHAR2(500) when your column is only 200. Just use %TYPE for the columns.
            • 3. Re: Perform two processes on the same data in the memory
              Mindmap
              Thanks rp0428,

              I Modified the post to reflect your appreciated Hints ...


              >
              That is correct - you are using BLOB in your code and BLOB is a LOB locator. A locator POINTS TO THE BLOB; the locator isn't a copy of the BLOB. If you want a copy you need to copy it yourself. So after you apply the watermark in the first step you should copy the images and insert them into the other table. Then generate the thumbnails.
              >

              Sorry, I did not get it ... Can you please further explain. I suppose that I did two different processes on two different tables using two different Variables.

              I took the images from TEMP table
              Manipulate them on the memory, then insert them into IMAGES_TEST Table

              I took the images from IMAGES_TEST Table
              Manipulate them on the memory, then insert them into IMAGES_T Table

              Thanks in advance,
              Fateh
              • 4. Re: Perform two processes on the same data in the memory
                rp0428
                >
                Sorry, I did not get it ... Can you please further explain. I suppose that I did two different processes on two different tables using two different Variables.
                >
                When you query a BLOB column into a variable that variable just contains a pointer to the BLOB; it does not contain the actual BLOB. So if you pass that pointer to code that does a resize it will resize the BLOB that the pointer points to. Now if you pass the same pointer to that code to do another resize and then insert the pointer into the other table the second resize did the resize on the orginal BLOB and then the insert copied the newly resized BLOB into the second table. Then both tables would have the same size BLOB.

                You need to resize the BLOB from the first table and then insert it to the second table. Then use a new lob locator for the second tables blob to resize it.
                >
                I Modified the post to reflect your appreciated Hints ...
                >
                Well I don't see where you did that. Your first post shows three tables: TEMP, IMAGES_TEST, IMAGES_T.

                Then the code you post later uses these tables: IMAGES, TIMG, TEMP, IMAGES_TEST, IMAGES_T

                The IMAGES and TIMG tables aren't defined anywhere so where did they come from?

                And you say
                >
                However, to Generate Thumbnails After re-sizing the images I used CODE 1 , but the problem is that the new inserted images into IMAGES_T table have become thumbnails instead of having 800 X 500 (width/height) ????
                >
                But your code does this
                ORDSYS.ORDImage.process(V_source(i), 'fixedScale=800 500');
                . . .
                insert into IMAGES_test 
                Which scales the images to 800 500 and puts them in the IMAGES_TEST table. Then you query them back out of the IMAGES_TEST table to the '*_t' variables and do this
                ORDSYS.ORDImage.process(V_source_t(i), 'fixedScale=150 120');
                . . .
                insert into IMAGES_t 
                So you just scaled them to 150 120 and put them in the IMAGES_T table. So how can you ask how they got there? You just put them there. Where did you expect them to be?

                You don't need all of those variables and you don't need associative arrays. If you want the data from the IMAGES_TEST table just define ONE var for it
                type id_col is table of number index by pls_integer ;
                TYPE tab_images_test is table of images_test%rowtype;
                v_tab_images_test tab_images_test;
                . . .
                select * bulk collect into v_tab_images_test from images_test where res_id = :P700_res_ID for update order by id;
                Not sure why you have the ORDER BY though.
                • 5. Re: Perform two processes on the same data in the memory
                  Mindmap
                  Thanks a lot,

                  I used WORKING CODE, which does the trick.

                  The only point left, is about How to rectify the code:
                  You mentioned:
                  TYPE tab_images_test is table of images_test%rowtype;
                  v_tab_images_test tab_images_test;
                  Then I use:
                  SELECT content,filename,mimetype,id bulk collect
                  INTO v_tab_images_test.source,v_tab_images_test.filename,v_tab_images_test.mimetype,v_tab_images_test.id
                  FROM photos_temp  
                  WHERE  temp = :P700_TEMP_IMAGE   FOR UPDATE  ;
                  But, I get error,more or less, " CONTENT IS NOT DECLARED "

                  You already answered my question.... Can you please complete your favor with me ? and tell how to rectify the code as per your suggestion ?

                  Best Regards,
                  Fateh
                  --------
                  h1. WORKING CODE
                  DECLARE
                  
                  
                  type char_col is table of images_test.filename%type index by pls_integer ;
                  V_filename char_col;
                  V_mimetype char_col;
                  V_filename2 char_col;
                  V_mimetype2 char_col;
                  
                  type source_col is table of blob index by pls_integer ;
                  V_source source_col;
                  V_source2 source_col;
                  
                  Type id_col is table of photos_temp.id%type index by pls_integer;
                  V_id id_col;
                  
                  added_image       BLOB;
                  prop ordsys.ord_str_list;
                  logging VARCHAR2(2000);
                  
                  x integer ;
                  
                  BEGIN
                  
                  SELECT count (nvl(watermarked,1))  INTO x 
                  FROM photos_temp   WHERE  temp = :P700_TEMP_IMAGE ;
                  IF x > 0 then 
                  
                  SELECT content,filename,mimetype,id bulk collect
                  INTO V_source,v_filename,v_mimetype,v_id
                  FROM photos_temp  
                  WHERE  temp = :P700_TEMP_IMAGE   FOR UPDATE  ;
                  
                  
                  
                  SELECT img INTO added_image FROM timg WHERE N = 1;
                  
                  prop := ordsys.ord_str_list(
                                     'position=middlecenter',
                                     'transparency=0.5',
                                      'width=500',
                                      'height=400'
                  );
                  
                  FOR i IN V_source.first .. V_source.last 
                  LOOP
                  ORDSYS.ORDImage.process(V_source(i), 'fixedScale=800 500');
                  ORDSYS.ORDImage.applyWatermark
                  (V_source(i), added_image, V_source(i), logging, prop);
                  END LOOP;
                  
                  FORALL i in V_source.first .. V_source.last 
                  
                  insert INTO IMAGES_TEST 
                  (ID, res_id ,CONTENT,filename,
                  mimetype,filesize,watermarked)
                  VALUES
                  (v_id(i),:P700_res_ID ,V_source(i),V_filename(i),
                  V_mimetype(i),dbms_lob.getlength(V_source(i)), 'Y');
                  
                  COMMIT;
                  
                  
                  
                  SELECT content,filename,mimetype,id bulk collect 
                  INTO V_source2,v_filename2,v_mimetype2,v_id
                  FROM photos_temp
                  WHERE  temp = :P700_TEMP_IMAGE   FOR UPDATE  ;
                  
                  FOR j IN V_source2.first .. V_source2.last 
                  loop
                  ORDSYS.ORDImage.process(V_source2(j), 'fixedScale=150 120');
                  end loop;
                  
                  FORALL j IN V_source2.first .. V_source2.last 
                  insert INTO IMAGES_T
                  (res_id ,CONTENT,filename,
                  mimetype,filesize,image_id)
                  VALUES
                  (:P700_res_ID ,V_source2(j),V_filename2(J),
                  V_mimetype2(J),dbms_lob.getlength(V_source2(j)), v_id(j));
                  COMMIT;
                  
                  delete photos_temp WHERE temp = :P700_TEMP_IMAGE;
                  
                  
                  COMMIT;
                  
                  END IF;
                  EXCEPTION
                     WHEN OTHERS THEN
                     RAISE;
                  END;
                  • 6. Re: Perform two processes on the same data in the memory
                    rp0428
                    >
                    But, I get error,more or less, " CONTENT IS NOT DECLARED "
                    >
                    Reread my reply - you BULK COLLECT the entire row at the same time. You are still trying to do the columns one at a time.
                    • 7. Re: Perform two processes on the same data in the memory
                      Mindmap
                      Thanks RP,
                      Reread my reply - you BULK COLLECT the entire row at the same time
                      I did, but I could not do it ....
                      DECLARE
                      TYPE tab_images_test is table of images_test%rowtype;
                      v_tab_images_test tab_images_test;
                      .......
                      .......
                      SELECT content,filename,mimetype,id bulk collect
                      INTO v_tab_images_test.source,v_tab_images_test.filename,v_tab_images_test.mimetype,v_tab_images_test.id
                      FROM photos_temp  
                      WHERE  temp = :P700_TEMP_IMAGE   FOR UPDATE  ;
                      ....
                      END;
                      However, You answered my question in this thread, I am going to post a new thread for the %ROWTYPE

                      Regards,
                      Fateh