DECLARE

  l_upload_size INTEGER;
  l_upload_blob BLOB;
  l_image_id    NUMBER;
  l_image       ORDSYS.ORDImage;
  l_lat VARCHAR2(100);
  l_lng VARCHAR2(100);


BEGIN

  --
  -- Get the BLOB of the new image from the APEX_APPLICATION_TEMP_FILES (synonym for WWV_FLOW_TEMP_FILES)
  -- APEX 5.0 change from APEX_APPLICATION_FILES which has been deprecated
  -- APEX_APPLICATION_TEMP_FILES has fewer columns and is missing doc_size
  --

  --generate co-ordinates from postcode
  brian.GEOCODE_GM_XML (:P6_postcode, l_lat, l_lng);
  DBMS_OUTPUT.PUT_LINE('Latitude ='||l_lat);
  DBMS_OUTPUT.PUT_LINE('Longitude ='||l_lng);

  SELECT
    blob_content
  INTO
    l_upload_blob
  FROM
    apex_application_temp_files
  WHERE
    name = :P6_image_filename;
  --
  -- Insert a new row into the table, initialising the image and
  -- returning the newly allocated image_id for later use
  --
  INSERT
  INTO
    properties_images
    (
      p_i_id,
      image_filename,
      property_image
    )
    VALUES
    (
      seq_p_images_p_i_id.NEXTVAL,
      :P6_image_filename,
      ORDSYS.ORDImage()
    )
  RETURNING
    p_i_id, property_image
  INTO
    l_image_id, l_image;

  INSERT INTO
      properties
      (
          p_id,
          p_i_id,
          a_id,
          address_line_1,
          address_line_2,
          city,
          postcode,
          geom_location,
          notes
      )
  VALUES
      (

          :P6_p_id,
          l_image_id,
          :P6_A_ID,
          :P6_address_line_1,
          :P6_address_line_2,
          :P6_city,
          :P6_postcode,
          sdo_geometry(2001, 8307, sdo_point_type(l_lng, l_lat, NULL), NULL, NULL),
          :P6_notes
      );

  -- find the size of BLOB (get doc_size)
  l_upload_size := DBMS_LOB.getlength(l_upload_blob);
  -- copy the blob into the ORDImage BLOB container
  DBMS_LOB.COPY( l_image.SOURCE.localData, l_upload_blob, l_upload_size );

  -- set the image properties
  l_image.setProperties();

  UPDATE
    properties_images
  SET
    property_image     = l_image -- original ORDImage image
  WHERE
    p_i_id = l_image_id;

  -- generate the thumbnail image from the ORDImage
  create_blob_thumbnail(l_image_id);

  --Optimise text index's for text searching after inserting new row
  CTX_DDL.OPTIMIZE_INDEX('properties_addr1_ctx_idx','FULL');

  COMMIT;

END;