Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to load a XML whole file into database.

AlibuneJun 11 2018 — edited Jun 12 2018

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

This post has been answered by Pavel_p on Jun 11 2018
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 10 2018
Added on Jun 11 2018
10 comments
1,224 views