| ID into a procedure argument [message #571886] |
Mon, 03 December 2012 09:37  |
 |
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  |
flyboy
Messages: 1903
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
