Inserting an image into a table in Oracle Apex

Question

I have created a form based on the following table:

CREATE TABLE  "POSTS" 
   (    "POST_ID" NUMBER(15,0) NOT NULL ENABLE, 
    "USER_ID" VARCHAR2(30) NOT NULL ENABLE, 
    "TITLE" VARCHAR2(255) NOT NULL ENABLE, 
    "TEXT" CLOB, 
    "RESPONSE_TO" NUMBER(15,0), 
    "FLAGGED" CHAR(1), 
    "MIMETYPE" VARCHAR2(50), 
    "LAST_UPDATE" DATE, 
    "THUMB" BLOB, 
    "IMAGE" "ORDSYS"."ORDIMAGE" , 
    "FILENAME" VARCHAR2(255), 
     CONSTRAINT "POSTS_PK" PRIMARY KEY ("POST_ID")
  USING INDEX  ENABLE
   )
/
ALTER TABLE  "POSTS" ADD CONSTRAINT "POSTS_CON" FOREIGN KEY ("RESPONSE_TO")
      REFERENCES  "POSTS" ("POST_ID") ENABLE
/


CREATE INDEX  "POSTS_TEXT_INDEX" ON  "POSTS" ("TEXT") 
   INDEXTYPE IS "CTXSYS"."CONTEXT"
/


CREATE INDEX  "POSTS_TITLE_INDEX" ON  "POSTS" ("TITLE") 
   INDEXTYPE IS "CTXSYS"."CONTEXT"
/


CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_POSTS" 
  before insert on "POSTS"               
  for each row  
begin   
  if :NEW."POST_ID" is null then 
    select "POSTS_SEQ".nextval into :NEW."POST_ID" from sys.dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_POSTS" ENABLE
/


CREATE OR REPLACE EDITIONABLE TRIGGER  "NEW_POST" 
  BEFORE INSERT
  ON posts

FOR EACH ROW
BEGIN
  --l_image := ORDSYS.ORDImage.Init();
  --ORDSYS.ORDImage.process(:new.image, 'maxscale=200 200');

  :new.user_id := v(':APP_USER');
  --process_post_image(:new.post_id);
END;

/
ALTER TRIGGER  "NEW_POST" ENABLE
/

The title, text and filename fields are editable in the form.

To upload the file the following code is used in a custom process in place of the automatically generated insert code:

DECLARE

  l_upload_size INTEGER;
  l_upload_blob BLOB;
  l_image_id    NUMBER;
  l_image       ORDSYS.ORDImage;

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
  --

  SELECT
    blob_content
  INTO
    l_upload_blob
  FROM
    apex_application_temp_files
  WHERE
    name = :P16_FILENAME;
  --
  -- Insert a new row into the table, initialising the image and
  -- returning the newly allocated image_id for later use
  --
  INSERT
  INTO
    posts
    (
      post_id,
      title,
      text,
      filename,
      image
    )
    VALUES
    (
      posts_seq.nextval,
      :P16_TITLE,
      :P16_TEXT,
      :P16_FILENAME,
      ORDSYS.ORDImage()
    )
  RETURNING
    post_id, image
  INTO
    l_image_id, l_image;

  -- 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
    posts
  SET
    image     = l_image -- original ORDImage image
  WHERE
    post_id = l_image_id;

END;

But the following error is produced:

Invalid action CREATE on this object. (D)

Scott · Accepted Answer · 2016-11-25 03:00:08Z

1

If you move the blob, I was able to successfully convert blob to ordImage using

UPDATE images
SET ord_image = ORDSYS.ORDImage(image)
WHERE ...

And the trigger needs colon removed here

:new.user_id := v('APP_USER');

I often use

COALESCE(apex_application.g_user, sys_context('userenv','session_user'))

edited Nov 25, 2016 at 3:00

answered Dec 29, 2015 at 1:02

Scott

5,1303 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NiiL Over a year ago

I am also able to set ORDImage from BLOB using Update query, but after that I am not able to user ORDImage's actual functionality like

select post_id, t.image.getfileformat(), t.image.getcompressionformat(), t.image.getcontentformat(), t.image.getcontentlength() from posts t;

Please help me if you or @HedgepigMatt have any idea, I am fining a way to directly store image to ORDImage via Apex Form Page if possible. Also I was wondering will this process of BLOB to ORDImage will affect quality of uploaded media?

Scott Over a year ago

ORDImages are just blobs stored a layer deeper. What is happening when you attempt that functionality?

Collectives™ on Stack Overflow

Inserting an image into a table in Oracle Apex

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related