Reading and storing all files in directory using oracle procedure

Question

I am trying to access some directory in C which is (C:/FilesDirectory) and am looking for an oracle procedure/script to fetch all files in this directory and insert them in a table in my database(insert files themselves). I created the directory like this

CREATE OR REPLACE DIRECTORY xml_dir AS 'C:/FilesDirectory)'; 
-- Create table to store XML file in 

CREATE TABLE SCTHAPIS.xml_tab (
  id        NUMBER(10),
  filename  VARCHAR2(100),
  insertdate date,
  xml       XMLTYPE
);

ALTER TABLE SCTHAPIS.xml_tab ADD (
  CONSTRAINT xml_tab_pk PRIMARY KEY (id)
);

CREATE SEQUENCE SCTHAPIS.xml_tab_seq;

Now, I've created the directory, but I don't know how to get all the files inside this directory and insert them in my table in database. Any suggestions?

unfortunately not, i saw asktom link and iam avoiding java stored procedure that's why am asking about another way more like a plsql script. — TAM.G, Jan 04 '20 at 12:54
Is the database server running on the same windows machine as the client, because you cannot access local files from the database server. Unfortunately, other than a java stored proc, there is no existing function to do that. Long overdue. — OldProgrammer, Jan 04 '20 at 15:20
Yes, the database is running on same machine that i want to access files from. — TAM.G, Jan 04 '20 at 15:33
The second answer in the post that Tejash mentioned gives another method that does not need java. It's a bit awkward, but should work. — gsalem, Jan 04 '20 at 17:30
By design the database is insulated from the OS. Consequently you need to do something which creates a crack in that insulation. Your two options are: Java Stored Procedures or External Tables. (For completeness there is a third option, which is to write a Pro*C routine: but that seems way less palatable than either of the others.) You've already created the directory, so the external table with the preprocessor script seems ideal for your needs. — APC, Jan 04 '20 at 23:08

Reading and storing all files in directory using oracle procedure

0 Answers0