Click here to Skip to main content
15,506,552 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table called IMAGE_TB with the following structure:

IMG_ID - PK
IMG_DESC
IMG_DATA - BLOB
IMG_EXTENSION


This table is stored in Oracle 11g R2 database. i am using ODP.NET to interact with the PL/SQL.

I am inserting image data into this table. I already know the values regarding the Image ID, the Image Description and Image Extension.

Now i am finding it difficult understanding of storing the IMG_DATA which the actual content of the image. In the C# i have binary[] array of image data which i want to pass to oracle so it can store the data.

Would I have to create a BLOB ARRAY in PL/SQL and pass multiple binary[] array of data to it.
e.g
SQL
create type BLOB_ARRAY as VARRAY(10) of BLOB;


Secondly, is there anything i need to be aware of when inserting BLOB Data?

What I have tried:

Inserted data without the Blob data using the following statement:
SQL
INSERT INTO IMAGE_TB(IMG_ID, IMG_DESC,IMG_EXTENSION)VALUES(1,'Mountain','.jpg');
Posted
Updated 27-May-16 6:05am

1 solution

Use a parameterised query - you should do that all the time anyway - or you won't pass the actual data. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parametrized queries instead.

This may help: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^] - the code is SQL Server based rather than Ocacle, but that's pretty much just different Connection and Command objects.
 
Share this answer
 
Comments
Eagle32 27-May-16 11:08am    
Yes that's correct. I should have made myself clearer, the insert statement i have used isn't the actual code i am going to use as final solution. It is merely for debugging purposes.
OriginalGriff 27-May-16 11:12am    
:laugh: You'd be surprised how many people don't bother...
Me? Even for testing / debugging I always use parameters - partly because it's an ingrained habit now, and partly because if I forgot one and it got into production...:gulp:
Eagle32 27-May-16 11:15am    
Yeah that is also true. In regards to Oracle and PL/SQL for writing images is there anything to for me to remember? I could be passing in 1MB images.
OriginalGriff 27-May-16 11:26am    
Shouldn't be a problem: Oracle BLOB can hold 2GB of binary data.
Eagle32 27-May-16 12:23pm    
Ok, thats great. I will give that a go.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900