Hi everyone!
I'm having some issues on loading a XML file into my table, i've been using this procedure:
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;
/
And it works well, but it only loads the XML from a database diretory, i need it to load the file selected by the user independent where it is, how can i do it?
I'm using APEX 5.1
Oracle 11g XE