APEX-AT-WORK no image

Working with XML files and APEX - Part 1: Upload

Von Tobias Arnhold 11.28.2013
Working with Oracle and XML can be a pain in the ass especially at the beginning when you don't know the hidden secrets. :) That's why I want to give some major hints how to integrate XML files in APEX applications.

This time I will provide an easy way how to upload a file into a table with a XMLType column.

Let's assume that this is our example XML file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<leagues>
  <league id="1" name="2. Bundeliga">
    <teams>
      <team>
        <id>1</id>
        <name location="Dresden" stadium="Glücksgas-Stadion">SG Dynamo Dresden</name>
      </team>
      <team>
        <id>2</id>
        <name location="Cologne" stadium="RheinEnergieStadion">1. FC Köln</name>
      </team>
      <team>
        <id>3</id>
        <name location="Berlin" stadium="Alte Försterei">1. FC Union Berlin</name>
      </team>
      <team>
        <id>4</id>
        <name location="Düsseldorf" stadium="Esprit Arena">Fortuna Düsseldorf</name>
      </team>
    </teams>
  </league>
</leagues>

Now we need the necessary DDL:

--------------------------------------------------------
--  DDL for XML Table IMP_FM
--------------------------------------------------------
CREATE TABLE IMP_FM 
(
  ID NUMBER NOT NULL 
, USER_NAME VARCHAR2(20 BYTE) 
, XML_FILE XMLTYPE 
, CONSTRAINT IMP_FM_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
) 
XMLTYPE XML_FILE STORE AS BINARY XML ALLOW NONSCHEMA;

/

CREATE SEQUENCE IMP_FM_SEQ INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCACHE;

/

CREATE OR REPLACE TRIGGER "IMP_FM_BI_TR" BEFORE
INSERT ON "IMP_FM" FOR EACH row 
BEGIN
 IF :NEW.USER_NAME IS NULL THEN 
    :NEW.USER_NAME := UPPER(NVL(v('APP_USER'),USER));
 END IF;
 IF :NEW.ID IS NULL THEN 
    SELECT IMP_FM_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
 END IF;
END;
/

--------------------------------------------------------
--  DDL for Table ERR_LOG
--------------------------------------------------------

  CREATE TABLE "ERR_LOG" 
   ( "AKTION" VARCHAR2(4000), 
 "APP_ID" NUMBER, 
 "APP_PAGE_ID" NUMBER, 
 "APP_USER" VARCHAR2(10), 
 "ORA_ERROR" VARCHAR2(4000), 
 "CUSTOM_ERROR" VARCHAR2(4000), 
 "PARAMETER" VARCHAR2(4000), 
 "TIME_STAMP" DATE, 
 "PROC_NAME" VARCHAR2(500), 
 "CLOB_FIELD" CLOB
   ) ;
/

  ALTER TABLE "ERR_LOG" MODIFY ("PROC_NAME" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("TIME_STAMP" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("APP_USER" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("APP_PAGE_ID" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("APP_ID" NOT NULL ENABLE);
  ALTER TABLE "ERR_LOG" MODIFY ("AKTION" NOT NULL ENABLE);
/

Our XML table has the column XML_FILE from type XMLTYPE. Thats the place where our uploaded files will be saved in.
With XMLTYPE you need to define the way the XML should be saved. As I found out (thanks to Carsten Czarski) the "BINARY XML" option is the most effective one.

More details about the saving options can be found here:
http://www.oracle.com/technetwork/database-features/xmldb/xmlchoosestorage-v1-132078.pdf
http://www.liberidu.com/blog/2007/06/24/oracle-11g-xmltype-storage-options/
http://grow-n-shine.blogspot.de/2011/11/one-of-biggest-change-that-oracle-has.html

During my tests (30MB XML file) I started with the option "XMLTYPE XML_FILE STORE AS CLOB" which leaded to a real bad result time.
One of the selects had a executing time of 314 seconds
With the "XMLTYPE XML_FILE STORE AS BINARY XML" it went down to 1 second.

Ok my system did not had a lot of CPU or RAM but to show the difference out of a performance point of view it is a great example.

Last but not least we need the PL/SQL Code to upload our XML file:
For quality aspects I always use packages with some debug features when I need PL/SQL code. Thats why this example becomes maybe a bit bigger then it normally would be.

CREATE OR REPLACE 
PACKAGE PKG_IMP AS 

  procedure fm_imp (p_filename varchar2);

END PKG_IMP;

/

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 wwv_flow_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_DATEI )
  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;

Inside APEX you call our procedure as SUBMIT PL/SQL Process:

  PKG_IMP.FM_IMP (:P1_FILE);
  -- P1_FILE is the file browse item.
That's it.

BTW: My SQL Developer stopped working / got really slow when I tried to update a BINARY XML column.

Next time I will write about my troubles in selecting XML data as readable SQL result.

5 Comments " Working with XML files and APEX - Part 1: Upload "

Anonymous

It's better to use AL32UTF8 for loading your XML.
v_xml := XMLTYPE (v_blob,NLS_CHARSET_ID('AL32UTF8'));

Oracle's UTF8 is really Unicode CESU-8, an outdated Unicode standard.

Tobias Arnhold 29 November, 2013 19:43

Thanks for the hint.

Anonymous

Thanks for this tutorial it was very helpful.

I got a comment thought. On the package script on line 72:

INSERT
INTO IMP_FM
( USER_NAME, XML_DATEI )

Shouldn't it be XML_FILE, instead of XML_DATEI?

Krishan Singh 09 March, 2015 19:48

hi Deutchland people.
I have a question. If i want my xml or CSV file row to update it it have same row(primary key contraint matches). Is there any option for than in oracle apex.

Anonymous

Works well, however if my xml tags use a namespace then it does not work. For example causes an error. Have you seen this problem?