OraFAQ Forum: Forms » how to insert image with pl/sql insert into values()

Home » Developer & Programmer » Forms » how to insert image with pl/sql insert into values() (oracle database10g , oracle developer suite10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to insert image with pl/sql insert into values() [message #439347]

Fri, 15 January 2010 13:47

tonna
Messages: 33
Registered: November 2009
Location: thai

Member

Hello, experts

I have problem when i want to insert image into database field blob with datablock item type is image with pl/sql

example : i want to insert picture together with data and transaction_id

Button trigger WHEN-BUTTON-PRESSED


DECLARE
	
V_FILE_NAME VARCHAR2(300) := CLIENT_GET_FILE_NAME('C:\', NULL,'JPEG Image (*.JPG,*.JPEG,*.JPE,*.JFIF)|*.JPG|Bitmap Image (*.bmp)|*.bmp|GIF Image (*.GIF)|*.GIF|TIFF Files (*.tif)|*.tif|All Files (*.*)|*.*|', 'Browse Restuarant Picture',OPEN_FILE,TRUE);

begin
read_image_file(V_FILE_NAME, 'JPEG Image (*.JPG,*.JPEG,*.JPE,*.JFIF)|*.JPG|Bitmap Image (*.bmp)|*.bmp|GIF Image (*.GIF)|*.GIF|TIFF Files (*.tif)|*.tif|All Files (*.*)|*.*|','menu.f_picture');

insert into menu(f_name,f_price,f_id,f_picture)
values (:menu.f_name,:menu.f_price,:menu.f_id,:menu.f_picture);
end;

When i compile it's error with " Error 49 bad bind variable ':menu.f_picture'"

could you give me any suggestion how to insert image to field blob together with other data , because when i use under syntax

DECLARE
	
V_FILE_NAME VARCHAR2(300) := CLIENT_GET_FILE_NAME('C:\', NULL,'JPEG Image (*.JPG,*.JPEG,*.JPE,*.JFIF)|*.JPG|Bitmap Image (*.bmp)|*.bmp|GIF Image (*.GIF)|*.GIF|TIFF Files (*.tif)|*.tif|All Files (*.*)|*.*|', 'Browse Restuarant Picture',OPEN_FILE,TRUE);

begin
read_image_file(V_FILE_NAME, 'JPEG Image (*.JPG,*.JPEG,*.JPE,*.JFIF)|*.JPG|Bitmap Image (*.bmp)|*.bmp|GIF Image (*.GIF)|*.GIF|TIFF Files (*.tif)|*.tif|All Files (*.*)|*.*|','menu.f_picture');

insert into menu(f_name,f_price,f_id)
values (:menu.f_name,:menu.f_price,:menu.f_id);
commit_form
end;

the result in database was duplicate record first record have insert transactionid(:menu.f_id)into DB ,but have no save blob into database field blob, the second record have no save transactionid(:menu.f_id)into DB, but save image field blob to database ,so i want to know how to save image field blob into database field blob in the same record. is it imposible to use insert into ... values() this syntax to insert datablock item type is image or do you have any suggestion tell me please. i waiting for the answer...

Thank you for your help. That's very kind of you..

Report message to a moderator

Re: how to insert image with pl/sql insert into values() [message #439376 is a reply to message #439347]

Sat, 16 January 2010 00:51

tamzidulamin
Messages: 132
Registered: October 2009
Location: Dhaka

Senior Member

Dear

U must convert your image file into blob data type for useing insert into command form DeveloperSuite.
the following procedure will help u to convert a blob data.
first create a directory(where u stored image file). then
call the following procedure.

CREATE OR REPLACE Procedure  Dpr_fileToBlob(Fname in VARCHAR2, Fdir in VARCHAR2, OutBlob out BLOB) 
 
 IS
 
 fblob               BLOB;   
 theBFile            BFILE; 
 
 Bsrc_offset         NUMBER :=1;
 Bdest_offset        NUMBER :=1;  
  
BEGIN

 dbms_lob.createtemporary(fblob,FALSE,DBMS_LOB.SESSION);

 theBFile := BFileName(Fdir,Fname);  
 
 dbms_lob.fileOpen(theBFile);  
 
 dbms_lob.loadblobfromfile(dest_lob    => fblob     ,
                              src_bfile   => theBFile  ,
                              amount      => dbms_lob.getLength(theBFile),
                              dest_offset => Bdest_offset,
                              src_offset  => Bsrc_offset
                              );
 
 dbms_lob.fileClose(theBFile);
 
 OutBlob := fblob;
 
End;
/
Declare
 vImage blob;
Begin
Dpr_fileToBlob(Fname=> 'Image_file_name', 
               Fdir => 'Image_Directory',
               OutBlob=>  vImage);

Insert into menu(f_name,
                 f_price,
                 f_id,
                 f_picture)
          values(:menu.f_name,
                 :menu.f_price,
                 :menu.f_id  ,
                 vImage);
commit;

End;

[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Sat, 16 January 2010 02:38] by Moderator

Report message to a moderator

Re: how to insert image with pl/sql insert into values() [message #439403 is a reply to message #439376]

Sat, 16 January 2010 04:01

tonna
Messages: 33
Registered: November 2009
Location: thai

Member

First Thank you very much for your answer ser, but i still confuse a little

i try to use your procedure but it's error this
"FRM-40735:WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-22285 "

DECLARE
	VIMAGE BLOB;
BEGIN

Dpr_fileToBlob(Fname=> 'M'||:PARAMETER.V_COUNT||'.jpg', 
        Fdir => 'Z:\RESTAURANT'||:GLOBAL.GLOBAL_USER_ID||'\MENU',
               OutBlob=>  VIMAGE);

INSERT INTO MENU(RESTAURANT_ID,F_NAME,F_TYPE,F_PRICE,F_DESCRIPTION,F_PICTUREPATH,F_PICTURE) 
	 VALUES(TO_NUMBER(:GLOBAL.GLOBAL_USER_ID),:EDITMENU.F_NAME,:EDITMENU.F_TYPE,:EDITMENU.F_PRICE,:EDITMENU.F_DESCRIPTION,:EDITMENU.F_PICTUREPATH,VIMAGE);
	 COMMIT;

END;

i'm not sure about your parameter in Dpr_fileToBlob ser, Does Fname is picture name and OutBlob is the variable type blob,
and my problem is when my picture was read image to show in datablock that's item type was image from client_get_file_name ,and i want to save this image that show in this datablock also save other data. how i convert type datablock that's type image to save image into field blob.
Could you help me why came out this error? ,i waiting for your answer.Thank you very much,That's very kind of you.

Report message to a moderator

Re: how to insert image with pl/sql insert into values() [message #439446 is a reply to message #439403]

Sat, 16 January 2010 22:40

tamzidulamin
Messages: 132
Registered: October 2009
Location: Dhaka

Senior Member

please connect your user in sqlplus or any third party tools as toad.
then,

CREATE OR REPLACE DIRECTORY 
TEMP AS 
'C:\Temp\';
--Make sure your image in the 'C:\Temp\' directory.

Now try the following code (from WHEN-BUTTON-PRESSED trigger):

DECLARE
    VIMAGE BLOB;
BEGIN

Dpr_fileToBlob(Fname=> 'M'||:PARAMETER.V_COUNT||'.jpg', -- U have to write Only Image Name with ext. For example: abc.jpg
               Fdir => 'TEMP',-- Directory name is case sensative
               OutBlob=>  VIMAGE);

INSERT INTO MENU(RESTAURANT_ID,F_NAME,F_TYPE,F_PRICE,F_DESCRIPTION,F_PICTUREPATH,F_PICTURE) 
     VALUES(TO_NUMBER(:GLOBAL.GLOBAL_USER_ID),:EDITMENU.F_NAME,:EDITMENU.F_TYPE,:EDITMENU.F_PRICE,:EDITMENU.F_DESCRIPTION,:EDITMENU.F_PICTUREPATH,VIMAGE);
     COMMIT;

END;

Tamzidul Amin.

[EDITED by LF: removed unnecessary quote of the whole previous message ... again]

[Updated on: Sun, 17 January 2010 08:37] by Moderator

Report message to a moderator

Re: how to insert image with pl/sql insert into values() [message #439463 is a reply to message #439446]

Sun, 17 January 2010 05:20

tamzidulamin
Messages: 132
Registered: October 2009
Location: Dhaka

Senior Member

have u solve your problem...?

Report message to a moderator

Previous Topic:	Medical product code list
Next Topic:	hierarchical tree query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 04 05:09:39 CST 2025