Home » Developer & Programmer » Application Express & MOD_PLSQL » ID into a procedure argument (Oracle 11.2, Apex)
ID into a procedure argument [message #571886] Mon, 03 December 2012 09:37 Go to next message
Kashinoda
Messages: 1
Registered: December 2012
Junior Member
I have a simple APEX project where the user uploads an image (ORDImage) to the database. There is a page which lists every image that's on the system, accompanied by a thumbnail of the image.

I have the SQL code to create the thumbnail, but unfortunately when I call the code (using a Button Click + SQL process) I'm only able to create thumbnails for specific IDs (see code at the bottom). I know there's a simple fix but I'm gone brain dead, any help appreciated :wave


Here is procedure to create thumbnail:

CREATE OR REPLACE PROCEDURE create_blob_thumbnail (p_image_id IN INTEGER) IS
  l_orig          ORDSYS.ORDImage;
  l_thumb         ORDSYS.ORDImage;
  l_blob_thumb    BLOB;
 
BEGIN
 
  -- lock row
  SELECT image
  INTO l_orig
  FROM images
  WHERE image_id = p_image_id FOR UPDATE;
  
  l_thumb := ORDSYS.ORDImage.Init();
  dbms_lob.createTemporary(l_thumb.source.localData, true);
  ORDSYS.ORDImage.processCopy(l_orig,
                              'maxscale=128 128',
                              l_thumb);
  
  UPDATE images
  SET thumbnail = l_thumb.source.localData
  WHERE image_id = p_image_id;
  
  dbms_lob.freeTemporary(l_thumb.source.localData);
  
  COMMIT; 
 
END;


This is the code that calls for a specific thumbnail to be created, the number in the parenthesis is the primary key where the image is stored:

BEGIN
  create_blob_thumbnail(1);
  create_blob_thumbnail(2);
  create_blob_thumbnail(3);
END;


To make this a little clearer (hopefully).

The procedure create_blob_thumbnail takes an integer, if that integer matches a unique key it will use the image stored on that row and make a thumbnail for it.

So when I call my procedure using this:

BEGIN
create_blob_thumbnail(102);
END;

I will get a thumbnail created for the image at ID 102, which is fine.

My problem is I need the integer to be filled in automatically, either as a trigger or button press in APEX (the result is the same).

I've tried this trigger to no avail:

CREATE OR REPLACE TRIGGER  "IMAGES_T1" 
AFTER
insert or update or delete on "IMAGES"
for each row
BEGIN
create_blob_thumbnail(:new.IMAGE_ID);
end;
Re: ID into a procedure argument [message #571893 is a reply to message #571886] Mon, 03 December 2012 10:21 Go to previous message
flyboy
Messages: 1780
Registered: November 2006
Senior Member
Just wonder what "no avail" Oracle behaviour looks like as I never faced it. Maybe it would be useful to exactly describe how Oracle responds.

Basically, in the trigger you cannot query/update the table on which is the trigger created. As in trigger those values are directly available in :NEW columns, there is no need for extra querying them. So, the trigger code should look like this (not tested, just showing the idea):
CREATE OR REPLACE TRIGGER  "IMAGES_T1" 
BEFORE
insert or update or delete on "IMAGES"
for each row
declare
  l_thumb         ORDSYS.ORDImage;
begin
  l_thumb := ORDSYS.ORDImage.Init();
  dbms_lob.createTemporary(l_thumb.source.localData, true);
  ORDSYS.ORDImage.processCopy(:new.image,
                              'maxscale=128 128',
                              l_thumb);
  
  :new.thumbnail = l_thumb.source.localData;
  
  dbms_lob.freeTemporary(l_thumb.source.localData);
end;
/

And yes, COMMIT in trigger is also not correct as it the trigger is only part of UPDATE statement and its transaction; after this fix, there is nothing to commit there anyway.

[Edit: the trigger has to be the BEFORE one to change THUMBNAIL column value]

[Updated on: Mon, 03 December 2012 10:27]

Report message to a moderator

Previous Topic: how to disable a selected row in oracle apex
Next Topic: Apex 4.1.1 and 4.2.1
Goto Forum:
  


Current Time: Mon Dec 22 16:25:12 CST 2014

Total time taken to generate the page: 0.14954 seconds