1

I need to read every file of an Oracle Directory (without knowing the name) and update a BLOB column with the file if the name of the file match with the PK table.

TABLE_TEST
ID   NUMBER(10,0) PK 
FOTO BLOB NULL

I did a PL/SQL function to read one file (knowing the name) and update the table with the BLOB, which works correctly. But, I don't know how I could read every file without knowing the name and take the name of the file.

DECLARE
    l_blob      BLOB;
    v_src_loc   BFILE := BFILENAME ('IMAGE_FILES8', '4.PNG');
    v_amount    INTEGER;
BEGIN
    UPDATE TABLE_TEST
       SET FOTO = EMPTY_BLOB ()
     WHERE ID = 4
    RETURN FOTO
      INTO l_blob;

    DBMS_LOB.OPEN (v_src_loc, DBMS_LOB.LOB_READONLY);
    v_amount := DBMS_LOB.GETLENGTH (v_src_loc);
    DBMS_LOB.LOADFROMFILE (l_blob, v_src_loc, v_amount);
    DBMS_LOB.CLOSE (v_src_loc);
    COMMIT;
END;
Seba López
  • 739
  • 1
  • 9
  • 18
  • See https://asktom.oracle.com/pls/asktom/asktom.search?tag=reading-files-in-a-directory-how-to-get-a-list-of-available-files and http://www.oracle-developer.net/display.php?id=513 on two methods of reading the details of files in a directory. – MT0 Sep 11 '18 at 09:11
  • 2
    Probably a duplicate of https://stackoverflow.com/questions/17404708/listing-files-in-a-specified-directory-using-pl-sql (you then just need to take your solution and put it into a loop for each file in the directory to update the blobs). – MT0 Sep 11 '18 at 09:12
  • It need to create a new table. Could be posible without creating new table? – Seba López Sep 11 '18 at 09:33
  • Yes, you could use Java to read the directory and return an array of strings as an Oracle collection. – MT0 Sep 11 '18 at 09:44
  • I can use just a PL/SQL script. I can't use Java for different reasons. – Seba López Sep 11 '18 at 09:55
  • @Sebastian. You may probably need to keep a list of already read files somewhere to stop prevent reading the same blobs over and over again? – Rene Sep 11 '18 at 09:56
  • 2
    I'm talking about using Oracle's [`CREATE JAVA`](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5012.htm) statement to embed Java into the database and then you can invoke it within PL/SQL. It is complicated to create a solution and I don't have time to do it for you but I hope that there are enough breadcrumbs here that you or someone else could achieve it. The other solution is to use an external table preprocessor and there is a detailed explanation in the second link of my first comment. – MT0 Sep 11 '18 at 10:01
  • 1
    The nub of the problem is that interacting with the OS from inside the database is hard: not programmatically but architecturally. There are all sorts of security and administrative issues, which is why it isn't switched on by default. So while there is a bit of technical complexity the tougher issues are political (getting privileges to do things). That you would like to avoid creating a new table suggests that you are working under such political constraints. – APC Sep 11 '18 at 12:22
  • Another option to resolve this is to have a shell script that just reads the file names (this is trivial in bash / perl or whatever scripting language you want), then calls your stored proc passing in the name of the file you want to read. This way when you have loaded the file it can be moved easily to another location on the file system. – Shaun Peterson Sep 13 '18 at 01:45

1 Answers1

1

Take a look of this web, it explain how to read all file from directory.

https://asktom.oracle.com/pls/asktom/asktom.search?tag=reading-files-in-a-directory-how-to-get-a-list-of-available-files

VladiEU
  • 21
  • 4