How to pass in a binary image data to PL/SQL function

Question

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 27-May-16 4:58am

Eagle32

Updated 27-May-16 5:05am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2016-05-27T05:05:00

Solution 1

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.

Posted 27-May-16 5:05am

OriginalGriff

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.

Eagle32 28-May-16 16:10pm

Is there a way to store byte[] array of data in PL/SQL. Here is what i have:
but i don't know how to store the byte[] array of data in the BLOB column. Currently i can only insert the physical file into the database:

DECLARE
l_dir VARCHAR2(10) := 'IMAGES';
l_file VARCHAR2(25) := 'test.jpg';
l_bfile BFILE;
l_blob BLOB;

BEGIN
INSERT INTO IMAGE_TBL (IMG_ID, IMG_DESC, IMAGE_DATA, IMG_EXT)
VALUES (IMAGE_TBL_SEQ.NEXTVAL, l_file, empty_blob(), '.jpg')
RETURN IMAGE_DATA INTO l_blob;

l_bfile := BFILENAME(l_dir, l_file);
--Opens BFILE for read-only access. BFILE data may not be written through the database.
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
--Copies all or part of the source external LOB to a destination internal LOB
DBMS_LOB.loadfromfile(l_blob,
l_bfile,
DBMS_LOB.getlength(l_bfile));
--Release the resource.
DBMS_LOB.fileclose(l_bfile);

--Save the changes. This will do the actual insert of the image
COMMIT;
END;