oracle loadtable,How to load XML files into Oracle table?

I am new at this so bear with me. Thanks.

I have a directory in my C drive. Like so, C:/Temp. it contains XML files that I would like to load into an Oracle table called xml_tab.

CREATE TABLE "OMBIL"."XML_TAB"

("ID" NUMBER(10,0),

"FILENAME" VARCHAR2(100 BYTE),

"XML" "XMLTYPE",

CONSTRAINT "XML_TAB_PK" PRIMARY KEY ("ID")

)

My XML files have the same structure and look like the following

XXXXXXXX

YTYYYYYYYY

Something off grid

open grid

closed grid

I would like to write a PL/SQL procedure to look inside my C:Temp directory and load the xml files into xml_tab oracle table. Do you guys have examples that I can follow to get this done.

I do have the following code, but its not working properly, also I would like the code to loop through all the xml files in the C:Temp directory, how can I do that???

create or replace PROCEDURE load_xml (p_dir IN VARCHAR2,

p_filename IN VARCHAR2) AS

l_bfile BFILE := BFILENAME(p_dir, p_filename);

l_clob CLOB;

l_dest_offset INTEGER := 1;

l_src_offset INTEGER := 1;

l_bfile_csid NUMBER := 0;

l_lang_context INTEGER := 0;

l_warning INTEGER := 0;

BEGIN

DBMS_LOB.createtemporary (l_clob, TRUE);

DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);

-- loadfromfile deprecated.

-- DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));

DBMS_LOB.loadclobfromfile (

dest_lob => l_clob,

src_bfile => l_bfile,

amount => DBMS_LOB.lobmaxsize,

dest_offset => l_dest_offset,

src_offset => l_src_offset,

bfile_csid => l_bfile_csid ,

lang_context => l_lang_context,

warning => l_warning);

DBMS_LOB.fileclose(l_bfile);

INSERT INTO xml_tab (

id,

filename,

xml

)

VALUES (

xml_tab_seq.NEXTVAL,

p_filename,

XMLTYPE.createXML(l_clob)

);

COMMIT;

DBMS_LOB.freetemporary (l_clob);

END load_xml;

Getting the following error:

BEGIN load_xml(p_dir => 'XML_DIR', p_filename => 'test.xml'); END;

Error report -

ORA-22288: file or LOB operation FILEOPEN failed

No such file or directory

ORA-06512: at "SYS.DBMS_LOB", line 805

ORA-06512: at "OMBIL.LOAD_XML", line 14

ORA-06512: at line 1

22288. 00000 - "file or LOB operation %s failedn%s"

*Cause: The operation attempted on the file or LOB failed.

*Action: See the next error message in the error stack for more detailed

information. Also, verify that the file or LOB exists and that

the necessary privileges are set for the specified operation. If

the error still persists, report the error to the DBA.

I did grant all on XML_DIR to public.

Thank you.