I was looking at stack overflow for ways to insert blob data in oracle and found some ways to do so (How to insert a picture into BLOB column in Oracle table using INSERT syntax?).
I am trying to insert a blob already retrieved in my oracle DB on an environment where I cannot create new tables. How do I insert this blob that I have? Can I do something where I convert the BLOB I have to raw data and then insert?
Insert into TABLE values (1234,utl_raw.cast_to_raw(blob_data))
Will the data be unmodified? Thanks!
-
If you can't create new tables, do you have any existing tables which have blob columns? What type of column are you trying to insert this blob into?kfinity– kfinity2018-03-09 19:39:47 +00:00Commented Mar 9, 2018 at 19:39
-
I am trying to insert this blob in a table that has blob columns.jackalope– jackalope2018-03-09 19:45:28 +00:00Commented Mar 9, 2018 at 19:45
Add a comment
|
1 Answer
From your question, it sounds like you already have a blob in the database, and you want to insert it into a table. For this example, let's say it's this empty blob.
select empty_blob() as my_blob from dual;
You don't need to do any conversion; just insert it into your table.
insert into MY_TABLE (example_column, blob_column) values
(1, (select empty_blob() as my_blob from dual) );
If it's PL/SQL, it's even cleaner:
declare
my_blob blob;
begin
my_blob := empty_blob();
insert into MY_TABLE (example_column, blob_column)
values (1, my_blob );
end;
/
If you give us more details about how you're retrieving and storing your blob, or what your table structure looks like, we can probably provide a more specific answer.
3 Comments
jackalope
I have the BLOB as a file that I downloaded from a DB and now this file that has the blob, I want to insert in my table.
kfinity
So you want to insert a file as a blob into your table?
jackalope
Hi, thanks @kfinity for your time! What I did was I converted the blob file to hex data and used Oracle SQL's hextoraw(...) method in the insert statement to insert the data in my DB. Thanks once again!