ID into a procedure argument - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ID into a procedure argument

Author

Topic

Kashinoda
Starting Member

3 Posts

Posted - 2012-12-03 : 10:10:12

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;

Kashinoda
Starting Member

3 Posts

Posted - 2012-12-03 : 10:11:28

To explain it a bit better.

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 10:14:55

It seems like you're using Oracle. This is MS SQL Server forum and we deal with Transact SQL. You may be better off posting this in some Oracle forums like www.orafaq.com for better assistance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kashinoda
Starting Member

3 Posts

Posted - 2012-12-03 : 10:19:12

Sorry bud, thanks for the link!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 10:23:48

np
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources