How to load a XML whole file into database.

Alibune

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

Pavel_p

Hi,

assuming that "selected by the user independent where it is" actually means that it's located somewhere on client's machine and this file will be selected from the browser as a file to upload.

You can upload a file quite easily:

1) create a page item of type File Browse and let's name it P1_XMLFILE,

2) specify its Storage Type attribute as Table APEX_APPLICATION_TEMP_FILES (we can safely set its Purge File at End of Request as we'll not need it later),

3) create a button with Action = Submit Page,

4) create a (conditional on Button Pressed) process in Processing section with the following code

declare  l_xml      xmltype;  l_xml_blob apex_application_temp_files.blob_content%type; -- blob;  l_filename apex_application_temp_files.filename%type;begin  select f.blob_content,f.filename    into l_xml_blob,l_filename    from apex_application_temp_files f    where f.name = :p1_xmlfile;  --create xmltype from blob  l_xml := xmltype(l_xml_blob, nls_charset_id('UTF8'));--specify file encoding  --and now just insert the record  insert into xml_tab(id,filename,xml) values (xml_tab_seq.NEXTVAL,l_filename,l_xml);end;

Luckily the xmltype constructor takes blob as one of options https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/t_xml.htm#i1009842

constructor function XMLType(

   xmlData IN blob, csid IN number,

   schema IN varchar2 := NULL,

   validated IN number := 0,

   wellformed IN number := 0)

return self as result deterministic

so it does all the work for us and we're done.

Regards,

Pavel

Pierre Yotti

read that great Post

https://www.apex-at-work.com/2013/11/working-with-xml-files-and-apex-part-1.html

Mike Kutz

Use a "File Browse..." Item type on your Page.

This will allow the end-user to select a file and upload it to the table APEX_APPLICATION_TEMP_FILES (apex 5.0+)

The end-user must select the file.  This is a security thing defined by W3C.  If automation was allowed, then I'd send you to a web-page that will find and upload your tax returns along with anything that looks like a password file.

Once the file is in APEX_APPLICATION_TEMP_FILES, you should be able to extract it and deal with it as necessary.

Do not call SUBMIT within the APEX Process.  APEX can "rollback and try again."  this will cause (what appears to be) a double insert.

MK

Alibune

Hi @Alli Pierre Yotti

Thanks for the reply, i followed all the steps on the link you posted and i'm getting an error when i call the procedure from APEX:

• is_internal_error: false
• ora_sqlcode: -20001
• ora_sqlerrm: ORA-20001: Internal Error. Action canceled.
• component.type: APEX_APPLICATION_PAGE_PROCESS
• component.id: 78656929643416727
• component.name: New
• error_backtrace:
  ORA-06512: em "ESTUDO.PKG_IMP", line 79 ORA-06512: em line 1 ORA-06512: em "SYS.DBMS_SQL", line 1825 ORA-06512: em "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1880 ORA-06512: em "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1895 ORA-06512: em "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 936 ORA-06512: em "APEX_050100.WWV_FLOW_PROCESS_NATIVE", line 71 ORA-06512: em "APEX_050100.WWV_FLOW_PROCESS_NATIVE", line 1132 ORA-06512: em "APEX_050100.WWV_FLOW_PLUGIN", line 2399 ORA-06512: em "APEX_050100.WWV_FLOW_PROCESS", line 200
• error_statement:
  begin PKG_IMP.fm_imp(:P2_NEW); end;

I don't know what is wrong, i've tried make the steps again and it still don't work.

Alibune

Hi @Mike Kutz!

How could i do it? Simply using a select insert into my table? i've tried directly putting the value on the table and got an error.

Can you explain please?

Pierre Yotti

Ok. Look at the package App in oracle apex. There is a app with name “Sample Data Loading “. You can test it and look how it works

Alibune

i've looked into it, but it doesn't work for xml, it needs defined columns and rows to work, like .csv ones, but thank you anyway

Pierre Yotti

Than check the package of the above post. What is the type of P1_new?

Which Version of APEX do you use?

from 5.1 to 18.1 you need to run that

create or replace PACKAGE BODY PKG_IMP AS

/* ********************* */

/* Package Variables     */

/* ********************* */

  gv_proc_name    VARCHAR2(100);

  gv_action       VARCHAR2(4000);

  gv_ora_error    VARCHAR2(4000);

  gv_custom_error VARCHAR2(4000);

  gv_parameter    VARCHAR2(4000);

  gv_apex_err_txt VARCHAR2(500);

  GV_USERNAME     VARCHAR2(100)     := UPPER(NVL(v('APP_USER'),USER));

/* ********************* */

/* Save errors           */

/* ********************* */

PROCEDURE ADD_ERR  IS

   PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

     INSERT

     INTO ERR_LOG

      ( PROC_NAME,AKTION,APP_ID,APP_PAGE_ID,APP_USER,ORA_ERROR,CUSTOM_ERROR,PARAMETER,TIME_STAMP )

      VALUES

      ( gv_proc_name,gv_action,nvl(v('APP_ID'),0),nvl(v('APP_PAGE_ID'),0),nvl(GV_USERNAME,'Unknown'),

        gv_ora_error,gv_custom_error,gv_parameter,sysdate );

     COMMIT;

END;

/* ********************* */

/* Import Procedure      */

/* ********************* */

procedure fm_imp (p_filename varchar2) AS

  v_blob BLOB;

  v_xml  XMLTYPE;

BEGIN

  gv_proc_name := 'pkg_imp.fm_imp';

  gv_parameter := '';

  gv_parameter := 'p_filename: ' || p_filename;

  gv_action := 'Delete old data';

  DELETE FROM IMP_FM

   WHERE user_name = GV_USERNAME;

  gv_action := 'Read file';

  SELECT blob_content

    INTO v_blob

    FROM APEX_APPLICATION_TEMP_FILES

   WHERE name = p_filename;

  gv_action := 'XML Conversion';

  v_xml := XMLTYPE (v_blob,NLS_CHARSET_ID('AL32UTF8'));

  /* UTF-8 clause because we use it in our XML file */

  gv_action := 'Insert in IMP_BESTELLLISTE_XML';

  INSERT

  INTO IMP_FM

    ( USER_NAME, XML_FILE )

  VALUES

    ( GV_USERNAME, v_xml );

  gv_action := 'Delete file';

  DELETE FROM wwv_flow_files

    WHERE name = p_filename;

  COMMIT;

EXCEPTION

WHEN OTHERS THEN  

      gv_ora_error := SQLERRM;

      gv_custom_error := 'Internal Error. Action canceled.';

      ROLLBACK;

      ADD_ERR; raise_application_error(-20001, gv_custom_error);

END fm_imp;

END PKG_IMP;

The trick is this Table APEX_APPLICATION_TEMP_FILES

wwv_flow_files will not works. you should remove it from your code

Pierre Yotti

To see the content of your xml, you can read that query

SET LONG 5000

SELECT x.XML_FILE.getClobVal()

FROM   imp_fm x;

To retrive it as row just make something like this

SELECT xt.*

FROM   imp_fm x,

       XMLTABLE('/leagues/league/teams/team'

         PASSING x.XML_FILE

         COLUMNS

           id     VARCHAR2(10)  PATH 'id',

           name     VARCHAR2(20) PATH 'name'

         ) xt;

https://oracle-base.com/articles/misc/xmltable-convert-xml-data-into-rows-and-columns-using-sql

Pavel_p

Hi,

assuming that "selected by the user independent where it is" actually means that it's located somewhere on client's machine and this file will be selected from the browser as a file to upload.

You can upload a file quite easily:

1) create a page item of type File Browse and let's name it P1_XMLFILE,

2) specify its Storage Type attribute as Table APEX_APPLICATION_TEMP_FILES (we can safely set its Purge File at End of Request as we'll not need it later),

3) create a button with Action = Submit Page,

4) create a (conditional on Button Pressed) process in Processing section with the following code

declare  l_xml      xmltype;  l_xml_blob apex_application_temp_files.blob_content%type; -- blob;  l_filename apex_application_temp_files.filename%type;begin  select f.blob_content,f.filename    into l_xml_blob,l_filename    from apex_application_temp_files f    where f.name = :p1_xmlfile;  --create xmltype from blob  l_xml := xmltype(l_xml_blob, nls_charset_id('UTF8'));--specify file encoding  --and now just insert the record  insert into xml_tab(id,filename,xml) values (xml_tab_seq.NEXTVAL,l_filename,l_xml);end;

Luckily the xmltype constructor takes blob as one of options https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/t_xml.htm#i1009842

constructor function XMLType(

   xmlData IN blob, csid IN number,

   schema IN varchar2 := NULL,

   validated IN number := 0,

   wellformed IN number := 0)

return self as result deterministic

so it does all the work for us and we're done.

Regards,

Pavel

Alibune

Hi Pavel!

It worked just like i wanted, thank you!