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.
