1 Reply Latest reply: Jul 28, 2010 3:22 PM by Mannamal-Oracle RSS

    Upload and display ORDImage

    777293
      I would greatly appreciate if anyone could tell me how to write a pl/sql process to upload and display ORDImage thumbnail in Apex. I am using Oracle 11g and Apex 3.2. I have created a table with ORDImage row and created a file-browser :P1_IMAGE. I have tried all the procedures I could find but I can never get it to work properly.
      My table is:

      CREATE TABLE images
      (
      id number NOT NULL,
      name varchar2(20),
      description varchar2(1000),
      image ORDSYS.OrdImage
      );

      Many thanks.

      Edited by: user11967434 on 04-Jun-2010 17:49
        • 1. Re: Upload and display ORDImage
          Mannamal-Oracle
          Hi,

          To display an image, you need to generate the URL that will display the image on a webpage. The generated URL will in turn call a proedure to retrieve the image content from the database. Both the generate URL function and the retrieve content procedure should be written by you.

          A thumbnail can be generated using ORDImage APIs and once again the content should be retrieved and the URL generated for display on a webpage.

          Below are some code fragments for the procedures described above. (In these examples the schema is 'security' and the table 'employees' has several columns including an 'image' and a 'thumb' column for storing the image and the thumbnail).

          -- Note that function construct_URL generates a URL that is NOT secure. It can be modified by a malicious user to
          -- access content he or she is not authorized to access. Please refer to the Oracle Application Express
          -- documentation on how to create a URL that is secure.

          CREATE OR REPLACE FUNCTION construct_URL(p_id IN NUMBER)
          RETURN VARCHAR2 IS
          l_str VARCHAR2(1000);
          BEGIN
          l_str := '<img src="SECURITY.demo_get_image?' || 'p_id=' || p_id || '"/>';
          RETURN l_str;
          END;
          /

          --- procedure demo_get_image has code fragments and is not complete

          CREATE OR REPLACE PROCEDURE demo_get_image(p_id IN NUMBER)
          …select image from table
          …read content into local blob variable

          ora_util.mime_header(l_object.getMimeType(), FALSE);
          ordplsgwyutil.set_last_modified( l_object.getUpdateTime());
          owa_util.http_header_close();
          IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD'
          THEN
          wpg_docload.download_file( l_blob );
          END IF;
          …..
          END demo_get_image;

          CREATE OR REPLACE PROCEDURE processImage
          AS
          l_image ORDIMAGE;
          l_thumb ORDIMAGE;
          id NUMBER;
          CURSOR C1 IS SELECT t.image, t.thumb FROM security.employees t FOR UPDATE;
          BEGIN
          OPEN C1;
          LOOP
          FETCH C1 INTO l_image, l_thumb;
          EXIT WHEN C1%NOTFOUND;
          l_image.processCopy('maxScale=100 100', l_thumb);
          END LOOP;
          CLOSE C1;
          COMMIT;
          END;

          Then the procedure in APEX could be as follows:

          SELECT
          "EMPLOYEES"."EMPLOYEE_ID" "EMPLOYEE_ID",
          "EMPLOYEES"."FIRST_NAME" "FIRST_NAME",
          "EMPLOYEES"."LAST_NAME" "LAST_NAME",
          "EMPLOYEES"."EMAIL" "EMAIL",
          "EMPLOYEES"."PHONE_NUMBER" "PHONE_NUMBER",
          security.construct_URL(:P1_ID) "PHOTO"
          FROM
          "SECURITY"."EMPLOYEES" "EMPLOYEES"
          WHERE
          (employee_id = :P1_ID)


          Melli Annamalai