10 Replies Latest reply on Jun 12, 2018 11:16 AM by Alibune

    How to load a XML whole file into database.


      Hi everyone!


      I'm having some issues on loading a XML file into my table, i've been using this procedure:


        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;
        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);
        INSERT INTO xml_tab (
        VALUES (
        DBMS_LOB.freetemporary (l_clob);


      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

        • 2. Re: How to load a XML whole file into database.
          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.



          • 3. Re: How to load a XML whole file into database.

            Hi 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.

            • 4. Re: How to load a XML whole file into database.

              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?

              • 5. Re: How to load a XML whole file into database.
                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

                • 6. Re: How to load a XML whole file into database.

                  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

                  • 7. Re: How to load a XML whole file into database.
                    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




                         INTO ERR_LOG



                          ( 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 );





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

                    /* Import Procedure      */

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



                    procedure fm_imp (p_filename varchar2) AS



                      v_blob BLOB;

                      v_xml  XMLTYPE;




                      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';


                      INTO IMP_FM

                        ( USER_NAME, XML_FILE )


                        ( GV_USERNAME, v_xml );



                      gv_action := 'Delete file';

                      DELETE FROM wwv_flow_files

                        WHERE name = p_filename;







                    WHEN OTHERS THEN  

                          gv_ora_error := SQLERRM;

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


                          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

                    1 person found this helpful
                    • 8. Re: How to load a XML whole file into database.
                      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;


                      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,


                               PASSING x.XML_FILE


                                 id     VARCHAR2(10)  PATH 'id',

                                 name     VARCHAR2(20) PATH 'name'

                               ) xt;


                      Bildschirmfoto 2018-06-12 um 00.36.10.png



                      1 person found this helpful
                      • 9. Re: How to load a XML whole file into database.


                        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

                          l_xml      xmltype;
                          l_xml_blob apex_application_temp_files.blob_content%type; -- blob;
                          l_filename apex_application_temp_files.filename%type;
                          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);

                        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.



                        1 person found this helpful
                        • 10. Re: How to load a XML whole file into database.

                          Hi Pavel!


                          It worked just like i wanted, thank you!