This discussion is archived
7 Replies Latest reply: Nov 25, 2012 9:25 PM by Mindmap RSS

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

Mindmap Pro
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points