CODEWITHSUNDEEP
sqloracle12c
JTruant
JTruant

Reputation: 417

Cannot insert BLOB data into table

I am trying to store an image in a blob column. I created the table, job_resumes2, using the following code:

create table job_resumes2(
resume_id number,
first_name varchar2(25),
last_name varchar2(25),
profile_picture blob);

I then created a directory:

create directory myimages as 'C:\MYIMAGES';

I then executed the following procedure to populate the table, but I get an error when executing it:

SQL> DECLARE
  2  SRC bfile := bfilename('MYIMAGES','x.jpg');
  3  dest blob;
  4  begin
  5  insert into job_resumes2 values(1,'John','M',empty_blob())
  6  returning profile_picture into dest;
  7  dbms_lob.open(src,dbms_lob.lob_readonly);
  8  dbms_lob.loadfromfile(dest,src,dbms_lob.getlength(src));
  9  dbms_lob.close(src);
 10  commit;
 11  end;
 12  /
DECLARE
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The data is invalid.
ORA-06512: at "SYS.DBMS_LOB", line 1031
ORA-06512: at line 7

I have ensured that the directory and the file, x.jpg, exist. What is the problem and what can I do to fix it?

Upvotes: 2

Views: 2954

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Where is the database installed? Is it on your computer? Because, directory has to reside on the database server (or be accessible to it - if it is elsewhere, use UNC to create it). "C:\temp" looks like it is your own PC, so - it might not work.

As of privileges: directory (as an Oracle object) is owned by SYS, and SYS has to grant privileges to user(s) that will be using it, such as

grant read, write on directory myimages to jtruant;

[EDIT: here's a working example]

This is my directory:

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
EXT_DIR

Creating a table:

SQL> create table job_resumes2
  2    (resume_id       number,
  3     first_name      varchar2(25),
  4     last_name       varchar2(25),
  5     profile_picture blob);

Table created.

Running a procedure:

SQL> declare
  2    src_lob  bfile := bfilename('EXT_DIR', 'robco.jpg');
  3    dest_lob blob;
  4  begin
  5    insert into job_resumes2
  6      (resume_id, first_name, last_name, profile_picture)
  7    values
  8      (1, 'John', 'M', empty_blob())
  9      returning profile_picture into dest_lob;
 10
 11    dbms_lob.open(src_lob, dbms_lob.lob_readonly);
 12    dbms_lob.loadfromfile( dest_lob => dest_lob,
 13                           src_lob  => src_lob,
 14                           amount   => dbms_lob.getlength(src_lob) );
 15    dbms_lob.close(src_lob);
 16
 17    commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

Is there anything there?

SQL> select dbms_lob.getlength(profile_picture) from job_resumes2;

DBMS_LOB.GETLENGTH(PROFILE_PICTURE)
-----------------------------------
                               6427

SQL>

Seems to be OK. Try to follow my steps and see what happens.

Upvotes: 1

Related Questions