how to insert a video into Oracle db-CodePudding

i need to insert a video into oracle database , and here is what i'm trying to do , i wanna know if it is right i have created this table :

CREATE TABLE video (vid_id number ,vid_name varchar2(100) ,video BLOB) ;

the control file is like this : LOAD DATA INFILE 'video.txt' INTO TABLE tab1 FIELDS TERMINATED BY ',' (id , video_filename FILLER CHAR(100), videoLOBFILE(video_filename) TERMINATED BY EOF)

the video.txt file is like this

1,vid.mp4

i come along this in a tutorial used for storing a picture , what i don't understand is how it's gonna know the path of the video ?

CodePudding user response：

I'd use PL/SQL to do that, not SQL*Loader. Here's an example.

Connected as SYS, create directory (Oracle object that points to filesystem directory that contains the file) and grant privileges to user who will be loading the file:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create directory ext_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

SQL>

Connect as scott; create target table, load the file:

SQL> connect scott/tiger
Connected.
SQL> create table test (id number, movie blob);

Table created.

SQL> declare
  2    l_dir    varchar2(10) := 'EXT_DIR';
  3    l_file   varchar2(20) := 'movie.mp4';
  4    l_bfile  bfile;
  5    l_blob   blob;
  6  begin
  7    insert into test (id, movie)
  8      values (1, empty_blob())
  9      return movie into l_blob;
 10
 11    l_bfile := bfilename(l_dir, l_file);
 12    dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
 13    dbms_lob.loadfromfile(l_blob, l_bfile, dbms_lob.getlength(l_bfile));
 14    dbms_lob.fileclose(l_bfile);
 15
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(movie) from test;

DBMS_LOB.GETLENGTH(MOVIE)
-------------------------
                  1570024

SQL>