Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Storing Binary image data in Oracle database table

c1b41d9f-ef76-4e96-bcd6-9ffe358fc0c7May 30 2016 — edited Jun 1 2016

Hi there,


I am trying to store a byte[] array of Image data into a BLOB column, however i am having some difficulty.

Table Structure

FX_IMG_ID (PrimaryKEY)

FX_IMG_DESC

FX_IMG_DATA (BLOB)

FX_IMG_EXT


My C# application is simply taking the user provided base64encoded string and converting it to byte[] array. Correct me if i am wrong, a byte[] array maps to BLOB in Oracle? The byte[] array could be up to 1MB/2MB in size.


Is the conversion approach correct? Please bear in mind i want to read this data out later but that's a separate topic for discussion. I need to be able to store the image correctly in the BLOB column. I would greatly appreciate if you could please provide me feedback/code sample.

Here is my test SQL code which i did and it works but its NOT taking into account of the binary[] data as i am finding difficult to implement this particular part. My current example is reliant on a physical directory where the image file resides. I would like to get away from this if possible.

DECLARE

l_dir    VARCHAR2(10) := 'IMAGES';

l_file   VARCHAR2(25) := 'test.jpg';

l_bfile  BFILE;

l_blob   BLOB;

BEGIN

  INSERT INTO IMAGE_TBL (FX_IMG_ID, FX_IMG_DESC, FX_IMG_DATA, FX_IMG_EXT)

  VALUES (IMAGE_TBL_SEQ.NEXTVAL, l_file, empty_blob(), '.jpg')

  RETURN FX_IMG_DATA INTO l_blob;

  l_bfile := BFILENAME(l_dir, l_file);

  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);

  DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));

  DBMS_LOB.fileclose(l_bfile);

COMMIT;

END;


Here is my dummy C# method I made to pass in the byte[] array(its not final solution):

public void UploadImage(OracleConnection myConnection, ImageDAO theImage)

{

myConnection.Open();

//

// logic to produce the binaryData is here.

//

//

byte[] data = Convert.FromBase64String(base64EncodedString);

//Perform INSERT

OracleCommand myCommand = new OracleCommand(

"INSERT INTO FX_IMAGE (FX_IMG_ID, FX_IMG_DESC, FX_IMG_DATA, FX_IMG_EXT)

VALUES(1,'pict1',:Img_Data, '.jpg')", myConnection);

OracleParameter myParam = myCommand.Parameters.Add(":Img_Data", OracleDbType.Blob);

myParam.OracleValue = data;

try

{

Console.WriteLine(myCommand.ExecuteNonQuery() + " rows affected.");

}

finally

{

  myConnection.Close();

}

}

Questions

In Oracle is it mandatory to store the a physical image on a server directory? i.e Can i not just take the byte[] of data from C# pass it to the database proc and simply store it in the BLOB column without the need to store the image physically on a server directory? if yes then what are my options? Can someone provide me with a sample please?


Kind Regards,

This post has been answered by Mark Williams on Jun 1 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 29 2016
Added on May 30 2016
1 comment
4,743 views