Forum Stats

  • 3,837,490 Users
  • 2,262,264 Discussions
  • 7,900,301 Comments

Discussions

How to load a XML whole file into database.

Alibune
Alibune Member Posts: 86
edited Jun 12, 2018 7:16AM in APEX Discussions

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

Tagged:
AlibuneMahmoud_Rabie

Best Answer

  • Pavel_p
    Pavel_p Member Posts: 2,314 Gold Trophy
    edited Jun 11, 2018 6:33PM Answer ✓

    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

    Mahmoud_RabieAlibune

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Jun 11, 2018 11:14AM

    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
  • Alibune
    Alibune Member Posts: 86
    edited Jun 11, 2018 1:09PM

    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
    Alibune Member Posts: 86
    edited Jun 11, 2018 1:11PM

    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
    Pierre Yotti Member Posts: 4,040 Bronze Crown
    edited Jun 11, 2018 1:55PM

    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
  • Alibune
    Alibune Member Posts: 86
    edited Jun 11, 2018 2:20PM

    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
    Pierre Yotti Member Posts: 4,040 Bronze Crown
    edited Jun 11, 2018 6:02PM

    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

    Alibune
  • Pierre Yotti
    Pierre Yotti Member Posts: 4,040 Bronze Crown
    edited Jun 11, 2018 6:36PM

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

    SET LONG 5000

    SELECT x.XML_FILE.getClobVal()

    FROM   imp_fm x;

    Bildschirmfoto 2018-06-12 um 00.12.52.png

    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;

    Bildschirmfoto 2018-06-12 um 00.36.10.png

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

    Alibune
  • Pavel_p
    Pavel_p Member Posts: 2,314 Gold Trophy
    edited Jun 11, 2018 6:33PM Answer ✓

    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

    Mahmoud_RabieAlibune
  • Alibune
    Alibune Member Posts: 86
    edited Jun 12, 2018 7:16AM

    Hi Pavel!

    It worked just like i wanted, thank you!

This discussion has been closed.