Load XML files with Oracle Forms

When you need to load a binary file which is in a client computer to the database in Oracle Forms. You can take advantage of WebUtil library which has many utilities to work with client files.

In this post I'll show how to load a XML file(in the client computer) in a database table with Oracle Forms, then we will convert the xml file into relational data.

Note: To follow this tutorial you must have configured the Weblogic server to enable file transfer.

Let's start:

1. Open the webutil.olb file:

2. Attach the webutil.pll to the forms module

3. Open the webutil.olb and copy the object group WEBUTIL to the module's object groups tag:

 

With this I have the module ready to use the Webutil functionalities.

4. This is the module I'll use in the example, the first button contains the function to raise a window to get the file path and the second one contains the procedure to load the file to the database. Let's see how to use this.

5. Here the code for get the file path. you can filter with file types you'll allow load to the database. For this you only have to change the parameter file_filter. When the user pushes the button a window is raised with a file browser to choose which file he want to load.

6. The code to make the load is a little more complex, the function webutil_file_transfer.client_to_db_with_progress transfers a file to a blob table column.  So, to use it you must have a table with a blob column where the file will be loaded. The table must have a record ready to update the blob column with the file. Let's see how to make the trick.

a. Create the table. Use a temporary table because we're going to use the file only to extract the data; we do not  want to save the file, if you want, you can save it on a permanent storage using a normal table.

CREATE GLOBAL TEMPORARY TABLE blog_user.temp_file_blob(
     id_file INTEGER,
     blob_file BLOB
) ON COMMIT DELETE ROWS;

b. The second button’s code, the v_file_id is the record’s id that we use in our table to insert the blob, the procedure blog_user.process_xml.process_xml(p_id) we will create it later:

c.  The program unit file_to_db execute the file load:

Note: If the load fails you’ll only see a message “Error the file was not load” if you want to see the detailed error you must turn on the java console log to see it.

With this you have the file already in a blob column in the database. You can use all the capabilities that oracle database has for blob files. Now, we will proceed with the extraction of the XML data.

7. The user can load a xml file from the computer as the follow XML data example:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
< cars>
     <car_record>
         <id_car>1</id_car>
         <mark>Honda</mark>
         <model>Civic</model>
     </car_record>
     <car_record>
         <id_car>2</id_car>
         <mark>Ford</mark>
         <model>Scape</model>
     </car_record>
< /cars>

8. The table where we’ll  insert the data is:

CREATE TABLE blog_user.cars(
     id_car INTEGER,
     mark VARCHAR2(100),
     model VARCHAR2(100)
);

9. The package that extracts the XML data contains the last procedure we use on the load button of the form:

create or replace PACKAGE           process_xml
IS
     PROCEDURE process_xml(p_id IN INTEGER);
END;

CREATE OR REPLACE PACKAGE BODY           process_xml
IS
    
     FUNCTION blob_to_clob(p_blob IN BLOB)
         RETURN CLOB
     IS
         v_clob CLOB;
         v_dest_offset INTEGER := 1;
         v_src_offset INTEGER := 1;
         v_lang_context INTEGER := 0;
         v_warning INTEGER := 0;
     BEGIN
         dbms_lob.createtemporary(v_clob, TRUE, dbms_lob.call);

        dbms_lob.converttoclob(dest_lob => v_clob, src_blob => p_blob,
                                 amount => dbms_lob.lobmaxsize, dest_offset => v_dest_offset,
                                 src_offset => v_src_offset,
                                 blob_csid => 871, lang_context => v_lang_context,
                                 warning => v_warning);

        RETURN v_clob;
     END;
    
     PROCEDURE process_xml(p_id IN INTEGER)
     IS
         v_blob BLOB;
         v_clob CLOB;
         v_xml XMLTYPE;
     BEGIN
         SELECT blob_file
         INTO v_blob
         FROM blog_user.temp_file_blob
         WHERE id_file = p_id;
        
         v_clob :=  blob_to_clob(v_blob);
        dbms_output.put_line('Clob length: '||dbms_lob.getlength(v_clob));
         v_xml := xmltype.createxml(v_clob);
        
         INSERT INTO blog_user.cars
         SELECT id_car, mark, model
         FROM  XMLTABLE('/cars/car_record'
                  PASSING v_xml
                 COLUMNS
                    id_car     VARCHAR2(4)  PATH 'id_car',
                    mark     VARCHAR2(10) PATH 'mark',
                    model       VARCHAR2(9)  PATH 'model'
                  ) xml_data;
     END; 
END;

We have finished the example; with this you can load a XML file in oracle forms and extract its data in a table. If you have any questions with the process, feel free to contact me.

I will be posting more tips about PLSQL, Oracle Forms and APEX.