Insert ORDImage using WORKSPACE_IMAGES ?

Yann39

Hi,

I work with Oracle 10g r2 and APEX 3.2 on Windows XP Pro.

I have tables using ORDImage data type (for logos), and I want to allow users to insert rows.

So I have created a procedure to insert ORDImages :

-----
CREATE OR REPLACE PROCEDURE insert_logo(dest_id NUMBER, filename VARCHAR2) IS
img ordsys.ordimage;
ctx raw(64) := null;
BEGIN
SELECT logo INTO img FROM Marque WHERE id=dest_id FOR UPDATE;
img.importFrom(ctx, 'file', v('WORKSPACE_IMAGES'), filename);
UPDATE Marque SET logo=img WHERE id=dest_id;
END;
*/*
-----
succesfully created.

But when I run it by exec insert_logo(2,'logo_Gigabyte.jpg');, it show me the ORA-30625 error.

can't I use v('WORKSPACE_IMAGES') for images directory ?

I tried : CREATE OR REPLACE directory images AS v('WORKSPACE_IMAGES'); but don't work too.

any help ?

Thanks !

jariola

Yann39 wrote:
can't I use v('WORKSPACE_IMAGES') for images directory ?

I tried : CREATE OR REPLACE directory images AS v('WORKSPACE_IMAGES'); but don't work too.

any help ?

Hi,

No, you can not use #WORKSPACE_IMAGES# that way.

WORKSPACE_IMAGES points to apex system table that hold your files you upload to workspace.

Br,Jari

Edited by: jarola on Feb 4, 2010 8:23 PM

This may help you to right direction
http://download.oracle.com/docs/cd/E14373_01/appdev.32/e13363/up_dn_files.htm#CIHHEHCJ

Also if you install Demonstrative application, you can see example from page 6
To install Demo app:
Home>Application Builder>Create
and select Demonstration Application

Yann39

Arf, so I have to convert BLOB to ORDImage ? Is there any way to do that ?

thx.

fac586

Arf, so I have to convert BLOB to ORDImage ? Is there any way to do that ?

Yes, there is an [ORDImage constructor for BLOBs|http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10776/ch_imgref.htm#CHDDFGJC].

Yann39

OK thank you

I tried that :

INSERT INTO Marque(logo)
SELECT ORDSYS.ORDImage(t.BLOB_CONTENT) FROM APEX_APPLICATION_FILES t WHERE t.FILENAME = 'logo_Gigabyte.jpg';

But I have the ORA-02315 error : incorrect number of arguments for default constructor.

In the example below it seems to work :
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10776/ch_imgref.htm#CHDDFGJC

I forget something ?

fac586

Sorry, I didn't spot that you were one of those rare people who actually identifies their DB version up front. The doc I linked to was for 11.2: that BLOB constructor doesn't exist in 10.2.

Yann39

ohh nooooooo

I will search for an other solution.

If isomeone thinks of something interresting do not hesitate !

Thank you fac586

Yann39

OK I think "I" have found :

I updated my logo field to intialize ordimage with empty constructor :

update Marque set logo = ordsys.ordimage.init();

Then copy apex blob image data into the blob storage of the ordimage column of my table (here just for the row id=2) :

update marque m set m.logo.source.localdata = (select t.BLOB_CONTENT FROM APEX_APPLICATION_FILES t WHERE t.FILENAME = 'logo_Gigabyte.jpg') WHERE id=2;

Then loop through images to set properties (here just for row id=2, loop don't needed) :

begin
for rec in (select id, logo from marque where id=2) loop
rec.logo.setProperties();
update marque set logo = rec.logo where id = rec.id;
end loop;
end;

And finnaly show informations :

DECLARE
image ORDImage;
idnum integer;
rowcount integer;
BEGIN
SELECT id, logo into idnum, image from marque where id=2;
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line('image id: '|| idnum);
dbms_output.put_line('image height: '|| image.getHeight());
dbms_output.put_line('image width: '|| image.getWidth());
dbms_output.put_line('image MIME type: '|| image.getMimeType());
dbms_output.put_line('image file format: '|| image.getFileFormat());
dbms_output.put_line('BLOB Length: '|| TO_CHAR(image.getContentLength()));
dbms_output.put_line('-------------------------------------------');
END;
/

Result :
-------------------------------------------
image id: 2
image height: 162
image width: 180
image MIME type: image/jpeg
image file format: JFIF
BLOB Length: 8601
-------------------------------------------

Properties are good, it seems to work !

I found some information on the following thread : http://kr.forums.oracle.com/forums/thread.jspa?threadID=443410&start=0&tstart=0, thanks to us.

I will try to show images now, and I will make more tests before marking this post as answered

Thanks.

Yann.

Yann39

Hi,

just a last question :

is it possible to show an ORDImage (in a region) using only PL/SQL ? I don't find any information on the net.

or should I use a Java component ?

Thanks.

fac586

Search the forum for "ORDImage". I think all/most of the posts deal with using the ORDImage static methods on BLOBs, but there may be something relevant. Otherwise just look for stuff about displaying images from BLOBs: it's all really the same.

(Too busy to dive into this in depth at present.)

Yann39

OK thanks for the tip.
I will try some things I have found, it should work.

Bye