Oracle Database 12.1.0.2.0 – Turning on the In-Memory Database option

24 July, 2014

It is indeed that sample as switching a knob to turn it on. To enable it you will have to set a reasonable among of memory for your environment.

In my test environment, I used the following to fiddle around with some XML in memory option tests.

Via the SYS account or SYSDBA privileged account you must have the compatible database parameter set to 12.1.0.2 or higher and a proper value for the inmemory_size parameter.

ALTER system SET compatible='12.1.0.2.0' scope=spfile;
ALTER system SET inmemory_size=2G scope=spfile;

And that’s it. After bouncing the database the in memory database option will be in effect.

HTH

Loading XML documents into an Oracle database (2)

18 July, 2014

If only I could do the following…(but it seems that it isn’t supported yet)…

That is…based on the first “Loading XML documents into an Oracle database (1)” post…

…build an XMLTYPE VIEW with an instead trigger moving the XML document REFERENCE values in one go in the varchar2() REFERENCE column, while inserting the XML document content in the XMLType column XML_VALUE…

 
SQL> -- while using the following sampleData.ctl SQL Loader control file
 
SQL> host cat sampleData.ctl
 
LOAD DATA
INFILE 'sampleData.dat'
append
INTO TABLE XML_VIEW
xmltype(XMLDATA) (
 filename filler CHAR(120),
 XMLDATA  lobfile(filename) TERMINATED BY eof)
 
SQL> CREATE TABLE RELATIONAL_TABLE (
  2  	REFERENCE  VARCHAR2(100),
  3  	XML_VALUE  XMLTYPE
  4  )
  5  /
 
TABLE created.
 
Elapsed: 00:00:01.69
 
SQL> CREATE OR REPLACE VIEW XML_VIEW OF xmltype
  2  WITH object id (
  3    xmlcast(XMLQuery('$X/PurchaseOrder/Reference' passing OBJECT_VALUE AS "X" returning content) AS varchar2(100))
  4  )
  5  AS
  6  SELECT XML_VALUE
  7    FROM RELATIONAL_TABLE
  8  /
 
VIEW created.
 
Elapsed: 00:00:00.17
 
SQL> CREATE OR REPLACE TRIGGER DATA_LOADER
  2  instead OF INSERT ON XML_VIEW
  3  BEGIN
  4  	     INSERT INTO RELATIONAL_TABLE VALUES (
  5  	       XMLCAST(
  6  		 XMLQUERY(
  7  		   '/PurchaseOrder/Reference'
  8  		   passing :NEW.OBJECT_VALUE
  9  		   returning CONTENT
 10  		 )
 11  		 AS VARCHAR2(100)
 12  	       ),
 13  	       :NEW.OBJECT_VALUE
 14    );
 15  END;
 16  /
 
TRIGGER created.
 
Elapsed: 00:00:00.07
 
SQL> host sqlldr &USERNAME/&PASSWORD@&TNSALIAS control=sampleData.ctl
 
SQL*Loader: Release 12.1.0.1.0 - Production ON Fri Jul 18 16:55:21 2014
 
Copyright (c) 1982, 2013, Oracle AND/OR its affiliates.  ALL rights reserved.
 
Path used:      Conventional
SQL*Loader-485: TABLE XML_VIEW may NOT be loaded USING a subtype
 
SQL> GET sampleData.log
  1  SQL*Loader: Release 12.1.0.1.0 - Production ON Fri Jul 18 16:55:21 2014
  2  Copyright (c) 1982, 2013, Oracle AND/OR its affiliates.  ALL rights reserved.
  3  Control File:   sampleData.ctl
  4  DATA File:      sampleData.dat
  5    Bad File:     sampleData.bad
  6    Discard File:  NONE specified
  7   (Allow ALL discards)
  8  NUMBER TO LOAD: ALL
  9  NUMBER TO skip: 0
 10  Errors allowed: 50
 11  Bind array:     64 ROWS, maximum OF 256000 bytes
 12  Continuation:    NONE specified
 13  Path used:      Conventional
 14  TABLE XML_VIEW, loaded FROM every logical record.
 15  INSERT OPTION IN effect FOR this TABLE: APPEND
 16	COLUMN Name		     POSITION	Len  Term Encl Datatype
 17  ------------------------------ ---------- ----- ---- ---- ---------------------
 18  FILENAME				 FIRST	 120	       CHARACTER
 19    (FILLER FIELD)
 20  XMLDATA			       DERIVED	   *  EOF      CHARACTER
 21	 Dynamic LOBFILE.  Filename IN FIELD FILENAME
 22* SQL*Loader-485: TABLE XML_VIEW may NOT be loaded USING a subtype
 23  .
 
SQL> SELECT REFERENCE
  2    FROM RELATIONAL_TABLE
  3  /
 
no ROWS selected
 
Elapsed: 00:00:00.02

Alas…”SQL*Loader-485: table XML_VIEW may not be loaded using a subtype”

Loading XML documents into an Oracle database (1)

18 July, 2014

Very often people want to load XML documents into an Oracle database so, for example, they can shred the needed values from those XML documents into relational tables.

In most cases, as experienced users know, the fastest way to load data into an Oracle database is via the use of an external table. This is also the case for XML, but how can you set this up? The following explains how the mechanisms work and how to achieve, amongst others automatic shredding of XML content into relational tables. This demo explains the basics, but you can of course completely automate the principles shown.

The sample scripts can be downloaded here: LoadingXMLviaExternalTables

Setting up the environment

For this example I have created an directory called “xml”, under my home directory “/u01/oracle/otn”. The database should be able to read the contents in directory “/u01/oracle/otn/xml” so check that those privileges on (in my case Linux environment) are properly set.

[oracle@localhost otn]$ pwd
/u01/oracle/otn
 
[oracle@localhost otn]$ ll
totaal 100
-rw-r--r--. 1 oracle oracle   165 jul 18 14:20 sampleData.ctl
-rw-r--r--. 1 oracle oracle 29101 jul 18 14:30 sampleData.dat
-rw-r--r--. 1 oracle oracle  1413 jul 18 14:34 testcase.sql
drwxr-xr-x. 2 oracle oracle 57344 jul 18 14:20 xml
-rw-r--r--. 1 oracle oracle  1646 jul 18 14:40 XT_testcase.sql

So on the “top level” /u01/oracle/otn, I have stored my SQL and SQLLoader files. In the directory “xml” (/u01/oracle/otn/xml), I stored my, to be loaded, XML documents.

[oracle@localhost otn]$ pwd
/u01/oracle/otn
 
[oracle@localhost otn]$ cd xml
 
[oracle@localhost xml]$ ls -l | head
totaal 3184
-rw-r--r--. 1 oracle oracle 1764 jul 18 14:19 ABANDA-20120407201438314PDT.xml
-rw-r--r--. 1 oracle oracle 1813 jul 18 14:19 ABANDA-20120417140257543PDT.xml
-rw-r--r--. 1 oracle oracle 1671 jul 18 14:19 ABANDA-20120427125050959PDT.xml
-rw-r--r--. 1 oracle oracle 1608 jul 18 14:19 ABULL-20120406132327821PDT.xml
-rw-r--r--. 1 oracle oracle 1646 jul 18 14:19 ABULL-20120406134455141PDT.xml
-rw-r--r--. 1 oracle oracle 2037 jul 18 14:19 ABULL-20120414210318287PDT.xml
-rw-r--r--. 1 oracle oracle 1981 jul 18 14:19 ABULL-2012041616035493PDT.xml
-rw-r--r--. 1 oracle oracle 2020 jul 18 14:19 ABULL-20120417140940985PDT.xml
-rw-r--r--. 1 oracle oracle 1601 jul 18 14:19 ABULL-20120418133607274PDT.xml
 
[oracle@localhost xml]$ ls -l | wc -l
797

Currently 797 XML documents are stored in this /u01/oracle/otn/xml directory. So far, so good.

A SQLLoader file must be created that contains the paths to the XML documents to be able to load the needed XML files. This can be achieved via the following statement (while your command prompt is in the xml directory that contains the XML documents):

[oracle@localhost xml]$ ls -l *.xml | awk '{print "\/xml\/"$9}' > /u01/oracle/otn/sampleData.dat 2>/dev/NULL
 
[oracle@localhost xml]$ cd ..
 
[oracle@localhost otn]$ cat sampleData.dat | head
 
/xml/ABANDA-20120407201438314PDT.xml
/xml/ABANDA-20120417140257543PDT.xml
/xml/ABANDA-20120427125050959PDT.xml
/xml/ABULL-20120406132327821PDT.xml
/xml/ABULL-20120406134455141PDT.xml
/xml/ABULL-20120414210318287PDT.xml
/xml/ABULL-2012041616035493PDT.xml
/xml/ABULL-20120417140940985PDT.xml
/xml/ABULL-20120418133607274PDT.xml
/xml/ABULL-20120421201211152PDT.xml

As you can see, the sampleData.dat file lists all XML files starting with “/xml/…”. This will be our fixed starting point for our Oracle Directory alias we will create later on so the “sampleData.dat” file can be used in our external table.

Setting up the user environment

In our database we need a user with the proper privileges, in my case a user called LOADTEST in my pluggable database with the TNSALIAS called “pdb1″.

SQL> GRANT CREATE any directory, DROP any directory, CONNECT, resource, unlimited tablespace, ALTER SESSION, CREATE VIEW TO LOADTEST IDENTIFIED BY LOADTEST;
 
SQL> ALTER USER LOADTEST DEFAULT tablespace USERS TEMPORARY tablespace TEMP;
 
SQL> CONNECT LOADTEST/LOADTEST@pdb1
 
SQL> CREATE OR REPLACE directory XTDIR AS '/u01/oracle/otn';

Creating the external table

Now we have setup the basics, the external table can be created.

SQL> CREATE TABLE XML_XT_DOCUMENTS (
  2    FILENAME VARCHAR2(100),
  3    DOCUMENT CLOB
  4  )
  5  ORGANIZATION EXTERNAL (
  6   TYPE ORACLE_LOADER
  7   DEFAULT DIRECTORY XTDIR
  8   ACCESS PARAMETERS (
  9  	FIELDS (
 10  	  FILENAME CHAR(100)
 11  	)
 12  	COLUMN TRANSFORMS (
 13  	  DOCUMENT FROM lobfile (FILENAME)
 14  	)
 15   )
 16   LOCATION ('sampleData.dat')
 17  )
 18  /
 
TABLE created.

Via the XTDIR directory alias the external table is able to find the XML documents specified in the contents of “sampleData.dat”.

SQL> SELECT COUNT(*)
  2    FROM XML_XT_DOCUMENTS
  3  /
 
  COUNT(*)
----------
       796
 
Elapsed: 00:00:00.17
 
SQL> SELECT *
  2    FROM XML_XT_DOCUMENTS
  3   WHERE rownum < = 5
  4  /
 
FILENAME
--------------------------------------------------
DOCUMENT
--------------------------------------------------
/xml/ABANDA-20120407201438314PDT.xml
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/X
MLSchema-instance" xsi:noNames
 
/xml/ABANDA-20120417140257543PDT.xml
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/X
MLSchema-instance" xsi:noNames
 
/xml/ABANDA-20120427125050959PDT.xml
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/X
MLSchema-instance" xsi:noNames
 
/xml/ABULL-20120406132327821PDT.xml
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/X
MLSchema-instance" xsi:noNames
 
/xml/ABULL-20120406134455141PDT.xml
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/X
MLSchema-instance" xsi:noNames
 
Elapsed: 00:00:00.02

Shredding XML content into relational columns

The following can now be used to get the "REFERENCE" element value from the XML documents in the external table.

SQL> CREATE TABLE RELATIONAL_TABLE (
  2  	REFERENCE  VARCHAR2(100),
  3  	XML_VALUE  XMLTYPE
  4  )
  5  /
 
TABLE created.
 
Elapsed: 00:00:01.12
SQL> INSERT INTO RELATIONAL_TABLE
  2  SELECT XMLCAST(
  3  	      XMLQUERY(
  4  		'/PurchaseOrder/Reference'
  5  		passing DOCUMENT
  6  		returning content
  7  	      )
  8  	      AS VARCHAR2(100)
  9  	   ),
 10  	   DOCUMENT
 11   FROM (
 12  	      SELECT XMLTYPE(DOCUMENT) DOCUMENT
 13  		FROM XML_XT_DOCUMENTS
 14  	   )
 15  /
 
796 ROWS created.
 
Elapsed: 00:00:01.18
SQL> commit
  2  /
 
Commit complete.

The used "select XMLTYPE(DOCUMENT) DOCUMENT from XML_XT_DOCUMENTS" in the from clause will get the XML documents from the external table and feed it via the DOCUMENT column alias into the XMLQUERY bit of the statement via the PASSING clause.

The XPATH expression '/PurchaseOrder/Reference' now gets the needed REFERENCE element values of the XML document input and is transformed via the XMLCAST operator into a VARCHAR2(100) datatype. Now the rest is easy because the content of column REFERENCE in the relational table is accessible as in a standard relational environment via SQL.

SQL> SELECT COUNT(*)
  2    FROM RELATIONAL_TABLE
  3  /
 
  COUNT(*)
----------
       796
 
Elapsed: 00:00:00.02
 
SQL> SELECT REFERENCE
  2    FROM RELATIONAL_TABLE
  3   WHERE rownum < = 5
  4  /
 
REFERENCE
--------------------------------------------------
ABULL-20120421201211152PDT
ABULL-20120423143056875PDT
ABULL-20120430174525177PDT
ACABRIO-20120401201015839PDT
ACABRIO-20120406143307807PDT
 
Elapsed: 00:00:00.00

12 3 Next ›Last »

Category: Howto