This discussion is archived
1 Reply Latest reply: Jul 28, 2010 1:22 PM by mannamal RSS

Upload and display ORDImage

777293 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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

Legend

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