Monthly Archives: April 2016

Reading XML in Oracle -2

Loading XML into Oracle might require some SQLLDR code. Of course, the standard insert is a possibility, but this might be slow if many XML files are to be loaded. From publications, I got the idea that the SQLLDR is the most recommended tool to do it. It does require some time and tuning but once, the script is ready, it might be re-used and it saves quite some time.
Using this SQLLDR vehicle, one might load a series of XML files into an Oracle table.
The code to do so is not really trivial. I used this table:

CREATE TABLE "SCOTT"."XML_TAB" 
   (	"XML_DATA" "XMLTYPE", 
	"ID" NUMBER(*,0)
   )

and the code to load XML files is as follows:

load data
infile *
append
into table XML_TAB
fields terminated by ';' 
(
 filename FILLER char(47),
 XML_DATA  lobfile(filename) terminated by eof,
 id  
)


begindata
c:\app\tom\product\12.1.0\dbhome_1\BIN\weg.xml;14
c:\app\tom\product\12.1.0\dbhome_1\BIN\weg1.xml;15
c:\app\tom\product\12.1.0\dbhome_1\BIN\weg2.xml;16


This file can be loaded by a SQLLDR command that may be something like:
SQLLDR CONTROL=C:\Users\Tom\Dropbox\Uitwisseling\xml.ctl LOG=sample.log, BAD=baz.bad, USERID=scott/binvegni, ERRORS=999, LOAD=500000, DISCARD=toss.dsc, DISCARDMAX=5

Reading XML in Oracle -1

As a first example on how to read an XML file, I show how an XML file can be read with a PLSQL programme. The logic is quite simple. We know that XML can be declared as an XMLtype. Once that is known, we declare the full XML file as an object that is read on a record by record base. The values can then be extracted with the ExtractValue procedure. Once the values are extracted, they can be inserted into a table.

We use the XMLTYPE here. This type allows to store XML files in a field. With special methods, one may read, update or delete the content of the XML. One may notice in the procedure below the ExtractValue method that allows to extract a certain value from the XML file.

Untitled

Another display is given when we look at a table that has two XML files:

Untitled

One may want to extract from the second XML file all values that are stored in. That can be done with

SELECT xt.*
FROM   xml_tab x,
       XMLTABLE('/person/row'
         PASSING x.xml_data
         COLUMNS 
           "State"    VARCHAR2(12)  PATH 'Address/State',
           "City"    VARCHAR2(15)  PATH 'Address/City',
           "Name"    VARCHAR2(10)  PATH 'name'
           ) xt where ID=2;

A final word on the clause ‘/person/row’. This is an example of a so-called XQuery. This ‘/person/row’ returns a row. It is possible to make a selection like ‘/person/row[name=”Tom”]’. This only returns the rows where name equals “Tom”.

An Oracle Programme with a loop

This programme creates an Oracle procedure. This Oracle programme has a loop. Within that loop, one record is read from a table. The content of that record is used within the loop.

I want to retain this programme for future usage. I tend to forget how such a table is created within a cursor and how the value from such query is retrieved and used within the programme.

The programme starts with the creation of a table. In the table, a new feature of Oracle 12 is included. This is the autoincrement, which is applied to the “id”. This provides 1, 2, 3 etc to the field “id”. The table is partioned on date, where dates before 1 jan 2014 are allocated in one partition, dates in 2014 in a second, date in 2015 in a third and so forth .

The idea is that we will calculate an average for “waarde” on each year. As the records related to different years are stored in different partitions, it is assumed that the calculation are limited to one of the partitions.

In the subsequent PL/SQL programme, a loop is created. The loop runs thru one partition first, after which the second partition will be used.

set serveroutput on 
drop table groot;
CREATE TABLE groot
  ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
  , waarde       NUMBER
  , datum       DATE)
 PARTITION BY RANGE (datum)
 ( PARTITION groot_2013 VALUES LESS THAN (TO_DATE('01-JAN-2014','dd-MON-yyyy'))
 ,  PARTITION groot_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','dd-MON-yyyy'))
 ,  PARTITION groot_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy'))
 , PARTITION groot_2016 VALUES LESS THAN (maxvalue)
 );
 
 describe groot;
 
 insert into SCOTT.GROOT(waarde, datum)
SELECT trunc(dbms_random.value(1,(1000)))
, to_date('2013-01-01', 'yyyy-mm-dd')+trunc(dbms_random.value(1,(4*365)))
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 2000000
);


create or replace PROCEDURE "TEST_PROCEDURE"
AS
  v_waarde number:=0;
  Lcntr integer:=0;
  CURSOR c1
IS
  SELECT 2013 as aantal
  FROM dual
  union all
  SELECT 2014 as aantal
  FROM dual
  union all
  SELECT 2015 as aantal
  FROM dual
  union all
  SELECT 2016 as aantal
  FROM dual
  ;
  l_aantal   c1%ROWTYPE;
BEGIN
  dbms_output.enable;
  OPEN c1;
  LOOP
   Lcntr := Lcntr + 1;
   FETCH c1 INTO l_aantal;
   EXIT WHEN c1%NOTFOUND;
   select avg(waarde) into v_waarde from groot where to_number(to_char(datum,'yyyy')) = l_aantal.aantal;
  dbms_output.put_line(Lcntr ||' '|| v_waarde ||' '||l_aantal.aantal);
  END LOOP;
END;
/

exec "TEST_PROCEDURE"