Export i import danych w formacie XML w bazie danych Oracle

ikonka_XML_03

Jeden ze standardowych formatów danych wykorzystywanych przez istniejące systemy to format XML. Jego niezależność od platformy sprawia, że jest on bardzo często wykorzystywany jako format wymiany danych pomiędzy systemami heterogenicznymi. W swoim artykule zaprezentuję, jak w prosty sposób wydobyć dane w formacie XML z bazy danych Oracle oraz jak wykorzystać ten format do przenoszenia danych pomiędzy dwoma instancjami bazy.

Eksport danych XML z bazy – DBMS_XMLGEN

Do generowania danych w formacie XML z bazy danych Oracle wykorzystujemy pakiet DBMS_XMLGEN. Pozwala on na generację XML na podstawie zapytania SQL. Za pomocą funkcji i procedur pakietu możemy dokładnie ustalić format generowanego dokumentu XML, np. nazwę elementów, maksymalną liczbę wierszy.

Podstawowe procedury i funkcje w pakiecie:

  • DBMS_XMLGEN.NEWCONTEXT – Funkcja zwracająca identyfikator nowego kontekstu na podstawie zapytania SQL;

    1
    DBMS_XMLGEN.NEWCONTEXT ( query IN VARCHAR2) RETURN ctxHandle;
  • DBMS_XMLGEN.CLOSECONTEXT – Procedura zamykająca kontekst o podanym identyfikatorze;

    1
    DBMS_XMLGEN.CLOSECONTEXT ( ctx IN ctxHandle);
  • DBMS_XMLGEN.GETXML – funkcja zwracająca dane w formacie XML na podstawie zapytania SQL lub kontekstu;

    1
    2
    3
    DBMS_XMLGEN.GETXML ( ctx IN ctxHandle, dtdOrSchema  IN NUMBER := NONE)  RETURN CLOB;
    DBMS_XMLGEN.GETXML ( sqlQuery IN VARCHAR2, dtdOrSchema  IN NUMBER :=    NONE) RETURN CLOB;
    DBMS_XMLGEN.GETXML ( ctx IN ctxHandle, tmpclob IN OUT NCOPY CLOB,   dtdOrSchema  IN NUMBER := NONE) RETURN BOOLEAN;
  • DBMS_XMLGEN.GETNUMROWSPROCESSED – funkcja zwracająca liczbę przetworzonych wierszy w ramach podanego kontekstu;

    1
    DBMS_XMLGEN.GETNUMROWSPROCESSED ( ctx IN ctxHandle) RETURN  NUMBER;
  • DBMS_XMLGEN.SETROWSETTAG – procedura określająca nazwę głównego elementu ROOT, domyślnie ROWSET;

    1
    DBMS_XMLGEN.SETROWSETTAG ( ctx IN ctxHandle,  rowSetTagName  IN     VARCHAR2);
  • DBMS_XMLGEN.SETROWTAG – procedura określająca nazwę elementu oddzielającego wiersze zapytania;

    1
    DBMS_XMLGEN.SETROWTAG ( ctx IN ctxHandle,rowTagName  IN VARCHAR2).

Przykłady wykorzystania pakietu:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE
  vc_query      VARCHAR2(255):= 'select * from dept';
  v_ctxhandle   DBMS_XMLGEN.ctxHandle;
  v_nrows       NUMBER;
  v_xmlclob     clob;
BEGIN
  v_ctxhandle := DBMS_XMLGEN.newContext(queryString => vc_query);
  DBMS_LOB.createtemporary(v_xmlclob,FALSE);
  DBMS_XMLGEN.getXML(ctx => v_ctxhandle,tmpclob => v_xmlclob);
  --v_xmlclob :=DBMS_XMLGEN.GETXML(vc_query);
  v_nrows := DBMS_XMLGEN.getNumRowsProcessed(ctx => v_ctxhandle);
  DBMS_XMLGEN.closeContext(ctx => v_ctxhandle);
  DBMS_OUTPUT.put_line('Liczba wierszy = '||v_nrows);
  DBMS_OUTPUT.put_line(v_xmlclob);
  DBMS_LOB.freetemporary(v_xmlclob);
END;

Wynik:
Liczba wierszy = 4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
 </ROW>
 <ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
  <LOC>CHICAGO</LOC>
 </ROW>
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>OPERATIONS</DNAME>
  <LOC>BOSTON</LOC>
 </ROW>
</ROWSET>
1
SELECT DBMS_XMLGEN.getXML('select * from dept') FROM dual;

Import danych XML do bazy danych – DBMS_XMLSAVE

Import danych w formacie XML do bazy Oracle jest możliwy, jeśli skorzystamy z z wykorzystaniem wwbudowanego pakietu DBMS_XMLSAVE. Pakiet ten pozwala na Dzięki niemu można dodawaćnie, usuwaćnie oraz aktualizowaćację wierszey w bazie danych na podstawie dokumentu XML. Pakiet pozwala również na określenie szczegółów importu, takich jak nazwa tabeli docelowej, format daty itp.

Podstawowe procedury i funkcje w pakiecie:

  • DBMS_XMLSAVE.newContext – funkcja zwracająca identyfikator nowego kontextu dla tabeli o podanej nazwie;

    1
    DBMS_XMLSAVE.newContext( targetTable IN VARCHAR2) RETURN ctxType;
  • DBMS_XMLSAVE.closeContext – procedura zamykająca kontekst o podanym identyfikatorze;

    1
    DBMS_XMLSAVE.closeContext( ctxHdl IN ctxType);
  • DBMS_XMLSAVE.insertXML – funkcja wstawiająca rekordy z XML do tabeli określonej w ramach kontekstu i zwracająca liczbę wstawionych wierszy;

    1
    DBMS_XMLSAVE.insertXML( ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN     NUMBER;
  • DBMS_XMLSAVE.deleteXML – funkcja usuwająca wiersze zawarte w XML z tabeli określonej w ramach kontekstu;

    1
    DBMS_XMLSAVE.deleteXML( ctxHdl IN ctxPType, xDoc IN VARCHAR2) RETURN    NUMBER;
  • DBMS_XMLSAVE.updateXML – funkcja aktualizująca rekordy zawarte w XML z tabeli określonej w ramach kontekstu;

    1
    DBMS_XMLSAVE.UPDATEXML( ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN     NUMBER;
  • DBMS_XMLSAVE.useDBDates – procedura określająca, czy w ramach kontekstu ma być wykorzystany format daty bazy danych;

    1
    DBMS_XMLSAVE.useDBDates(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE);
  • DBMS_XMLSAVE.setRowTag – procedura określająca nazwę elementu oddzielającego wiersze XML w ramach kontekstu;

    1
    DBMS_XMLSAVE.setRowTag( ctxHdl IN ctxType,  tag IN VARCHAR2).

Przykład wykorzystania pakietu:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
DECLARE
  v_ctxhandle   DBMS_XMLSAVE.ctxType;
  v_nrows       NUMBER;
  vc_clobxml    clob := '<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
 </ROW>
 <ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
  <LOC>CHICAGO</LOC>
 </ROW>
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>OPERATIONS</DNAME>
  <LOC>BOSTON</LOC>
 </ROW>
</ROWSET>'
;
BEGIN
  v_ctxhandle := DBMS_XMLSAVE.newContext(targetTable => 'DEPT_TEST');
  DBMS_XMLSAVE.setRowTag(ctxHdl => v_ctxhandle,tag => 'ROW');
  DBMS_XMLSAVE.useDBDates(ctxHdl => v_ctxhandle,flag => TRUE);
  v_nrows := DBMS_XMLSAVE.insertXML(ctxHdl => v_ctxhandle,xDoc => vc_clobxml);
  DBMS_XMLSAVE.closeContext(ctxHdl => v_ctxhandle);    
END;

Podsumowanie

Pakiet DBMS_XMLGEN pozwala w bardzo prosty sposób wygenerować dokument XML na podstawie zapytania SQL. Niestety nie daje przy tym możliwości zdefiniowania formatu daty, co sprawia, że musimy zadbać o to sami na etapie tworzenia zapytania SQL lub ustawić parametr sesji NLS_DATE_FORMAT na oczekiwaną wartość.

1
ALTER SESSION SET nls_date_format = "YYYY/MM/DD HH24:MI:SS";

Pakiet DBMS_XMLSAVE upraszcza proces importu danych w formacie XML do bazy danych Oracle. Podczas importu danych XML z wykorzystaniem tego pakietu natrafiłem na problem błędnego formatu daty w zaimportowanych danych pomimo ustawienia takich samych parametrów sesji. Rozwiązaniem okazała się procedura DBMS_XMLSAVE.useDBDates nieopisana w dokumentacji.

Wykorzystanie obu zaprezentowanych pakietów może pomóc w procesie przenoszenia danych pomiędzy instancjami bazy lub integracji z innymi systemami.

DBMS_XMLGEN – http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xmlgen.htm#ARPLS374

DBMS_XMLSAVE – http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xmlsav.htm#ARPLS70004

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *