upload_new_images procedure

gistfile1.sql

CREATE OR REPLACE PROCEDURE upload_new_image (p_filename IN VARCHAR2, p_product_id IN NUMBER) IS
    l_upload_size   INTEGER;
    l_upload_blob   BLOB;
    l_image_id      NUMBER;
    l_image         ORDSYS.ORDImage;
BEGIN
    -- Get the length, MIME type and the BLOB of the new image from the
    -- upload table 
    -- apex_application_files is a synonym for WWV_FLOW_FILES
    SELECT doc_size, blob_content
    INTO l_upload_size, l_upload_blob
    FROM apex_application_files
    WHERE name = p_filename;

    -- Insert a new row into the table, initialising the image and 
    -- returning the newly allocated image_id for later use
    INSERT INTO images (
        product_id,
        filename,
        image
    ) VALUES (
        p_product_id,
        p_filename,
        ORDSYS.ORDImage()
    )
    RETURNING image_id
    INTO l_image_id;

    -- Lock the row
    SELECT image
    INTO l_image
    FROM images
    WHERE image_id = l_image_id
    FOR UPDATE;

    -- copy the blob into the ORDImage BLOB container
    DBMS_LOB.COPY(l_image.source.localData, l_upload_blob, l_upload_size);
    l_image.setProperties(); -- just in case

    UPDATE images SET image = l_image WHERE image_id = l_image_id;
  
    -- create thumbnail using another procedure
    create_blob_thumbnail(l_image_id);
    
    -- clear the file from the upload table
    DELETE FROM apex_application_files WHERE name = p_filename; 

    COMMIT;
    
    EXCEPTION
    WHEN others
    THEN htp.p(SQLERRM);
END upload_new_image;