SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ID into a procedure argument
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kashinoda
Starting Member

3 Posts

Posted - 12/03/2012 :  10:10:12  Show Profile  Reply with Quote
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 - 12/03/2012 :  10:11:28  Show Profile  Reply with Quote
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;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/03/2012 :  10:14:55  Show Profile  Reply with Quote
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/

Go to Top of Page

Kashinoda
Starting Member

3 Posts

Posted - 12/03/2012 :  10:19:12  Show Profile  Reply with Quote
Sorry bud, thanks for the link!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/03/2012 :  10:23:48  Show Profile  Reply with Quote
np
you're welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000