Home > XML DB > Oracle XML DB : a practical example

Oracle XML DB : a practical example

November 23, 2011 Leave a comment Go to comments

It took time but here it is, my first blog entry : a practical example demonstrating some key features of Oracle XML DB.
For this article, I’ll be using the Forms XML schema and show how to do some interesting things with conformant documents.

 

1. The Oracle Forms XML schema

Introduction

Oracle Forms modules  binaries (.fmb, .mmb, .olb) can be converted to XML thanks to the Forms to XML conversion tool (frmf2xml). This utility resides in the BIN directory under the Forms HOME of the standard Developer Suite or Application Server installation.
In the same directory, we also find the Forms XML schema generator (frmxmlsg).
Both are actually simple wrapper scripts for the oracle.forms.util.xmltools Java class.

Generating the schema
D:\User\ODS1012\BIN>frmxmlsg
Oracle Forms 10.1.2 XML Schema Generator
Copyright(c) 2001, 2005, Oracle.  All rights reserved.

Forms XML Schema saved as forms.xsd

Converting a module
D:\User\ODS1012\BIN>frmf2xml
Oracle Forms 10.1.2 Forms to XML Tool
Copyright(c) 2001, 2005, Oracle.  All rights reserved.

Correct usage:
   frmf2xml [options] file1 [file2...]
The tool will accept .fmb, .olb and .mmb files.
Options: (default value in brackets)
DUMP=ALL/OVERRIDDEN dumps ALL properties or just those OVERRIDDEN (OVERRIDDEN)
OVERWRITE=YES/NO overwrite files that already exist (NO)
USE_PROPERTY_IDS=YES/NO writes out the numeric ID of the property value rather t
han the property value name (NO)

NOTE : After using the option DUMP=ALL you will NOT be able to reconvert the res
ulting XML files back to their original format.

As explained in the usage text, if we use DUMP=ALL option, we won’t be able to convert back the generated file(s) to the binary format.
With that option, every single property is extracted and assigned a specific namespace to denote whether it’s a default or a user-overridden property.

Besides, only the default DUMP option (OVERRIDDEN) generates files conformant to the Forms XML schema.

For example, to convert multiple files at a time :

D:\User\ODS1012\BIN>frmf2xml ../forms/*.fmb
Oracle Forms 10.1.2 Forms to XML Tool
Copyright(c) 2001, 2005, Oracle.  All rights reserved.

Processing module ../forms/test.fmb
Graphics IMAGE40 image saved as TEST_CANVAS1_IMAGE40.tif.
XML Module saved as ../forms/test_fmb.xml

Processing module ../forms/TEST_ITEMS.fmb
XML Module saved as ../forms/TEST_ITEMS_fmb.xml

 

A Forms module is basically a list of key-value pairs called properties, grouped under hierarchically organized categories.

Here’s the resulting XML after converting the file “test.fmb” as showed above (people familiar with Forms might recognize the OAS Forms Services splash screen) :

<?xml version = '1.0' encoding = 'UTF-8'?>
<Module version="101020002" xmlns="http://xmlns.oracle.com/Forms">
   <FormModule Name="TEST" ConsoleWindow="" DirtyInfo="true" MenuModule="" Title="OracleAS Forms Services">
      <Coordinate CharacterCellWidth="5" CoordinateSystem="Réel" CharacterCellHeight="14" RealUnit="Point" DefaultFontScaling="true"/>
      <Block Name="TEST" DirtyInfo="true" ScrollbarWidth="10" ScrollbarLength="138">
         <Item Name="OK" FontSize="1000" DirtyInfo="true" Height="18" XPosition="280" FontName="MS Sans Serif" ForegroundColor="black" Width="101" YPosition="144" FontSpacing="Normal" Label="OK" BackColor="gray" FontWeight="Gras" FontStyle="Simple" ItemType="Bouton de commande" CanvasName="CANVAS1">
            <Trigger Name="WHEN-BUTTON-PRESSED" TriggerText="exit_form;" DirtyInfo="true"/>
         </Item>
         <Item Name="VERSION" FontSize="1000" DirtyInfo="true" Height="11" Justification="Gauche" XPosition="10" Bevel="Aucun" PromptJustification="Centre" FontName="Times New Roman" PromptFontName="Times New Roman" PromptFontSpacing="Normal" PromptFontSize="1100" Width="209" PromptAlignOffset="-2" YPosition="153" FontSpacing="Normal" MaximumLength="60" FontWeight="Moyen" PromptAttachmentOffset="4" FontStyle="Simple" ItemType="Elément affiché" CanvasName="CANVAS1" PromptFontStyle="Simple" PromptFontWeight="Moyen"/>
      </Block>
      <Canvas Name="CANVAS1" ViewportHeight="331" BackColor="white" DirtyInfo="true" Height="212" WindowName="WINDOW1" Width="394" ViewportWidth="384">
         <Graphics Name="TEXT37" GraphicsText="Installed successfully!" Height="15" VerticalMargin="14" GraphicsFontSpacing="Normal" GraphicsFontColor="r100g0b0" Width="126" HorizontalJustification="Centre" GraphicsFontWeight="Gras" GraphicsFontSize="2600" StartPromptOffset="7" FillPattern="none" GraphicsFontColorCode="13" HorizontalObjectOffset="14" DirtyInfo="true" Bevel="Aucun" XPosition="64" GraphicsFontStyle="0" ScrollbarWidth="14" HorizontalMargin="7" EdgePattern="none" YPosition="26" GraphicsType="Texte" BackColor="yellow" GraphicsFontName="Times New Roman" JoinStyle="Relief">
            <CompoundText Name="CMPTXT38">
               <TextSegment Name="TEXTSTR39" FontSize="2600" DirtyInfo="true" FontName="Times New Roman" ForegroundColor="r100g0b0" FontWeight="Gras" FontStyle="Simple" Text="Installed successfully!" FontSpacing="Normal"/>
            </CompoundText>
         </Graphics>
         <Graphics Name="IMAGE40" GraphicsText="" DirtyInfo="true" Height="25" XPosition="150" VerticalMargin="14" ClipWidth="244" GraphicsFontColor="" GraphicsFontSpacing="Ultradense" ScrollbarWidth="14" GraphicsFontStyle="0" HorizontalMargin="7" Width="244" GraphicsFontSize="0" GraphicsFontWeight="Ultramince" YPosition="168" StartPromptOffset="7" GraphicsType="Image" GraphicsFontColorCode="0" HorizontalObjectOffset="14" GraphicsFontName="" Dither="false" ImageFilename="TEST_CANVAS1_IMAGE40.tif" ClipHeight="25"/>
         <Graphics Name="RECT41" GraphicsText="" Height="24" VerticalMargin="14" GraphicsFontSpacing="Ultradense" GraphicsFontColor="" ForegroundColor="r100g0b0" Width="244" GraphicsFontSize="0" GraphicsFontWeight="Ultramince" StartPromptOffset="7" GraphicsFontColorCode="0" HorizontalObjectOffset="14" EdgeBackColor="r100g0b0" DirtyInfo="true" Bevel="Aucun" XPosition="0" GraphicsFontStyle="0" ScrollbarWidth="14" HorizontalMargin="7" YPosition="168" GraphicsType="Rectangle" BackColor="r100g0b0" EdgeForegroundColor="r100g0b0" GraphicsFontName="" JoinStyle="Relief"/>
         <Graphics Name="TEXT42" GraphicsText="Oracle Application Server &amp;#10;Forms Services" Height="31" VerticalMargin="14" GraphicsFontSpacing="Normal" GraphicsFontColor="black" Width="288" HorizontalJustification="Centre" GraphicsFontWeight="Gras" GraphicsFontSize="2000" StartPromptOffset="7" FillPattern="none" GraphicsFontColorCode="0" HorizontalObjectOffset="14" DirtyInfo="true" Bevel="Aucun" XPosition="69" GraphicsFontStyle="0" ScrollbarWidth="14" HorizontalMargin="7" EdgePattern="none" YPosition="65" GraphicsType="Texte" BackColor="yellow" GraphicsFontName="Times New Roman" JoinStyle="Relief">
            <CompoundText Name="CMPTXT43">
               <TextSegment Name="TEXTSTR44" FontSize="2000" DirtyInfo="true" FontName="Times New Roman" ForegroundColor="black" FontWeight="Gras" FontStyle="Simple" Text="Oracle Application Server &amp;#10;" FontSpacing="Normal"/>
            </CompoundText>
            <CompoundText Name="CMPTXT65">
               <TextSegment Name="TEXTSTR66" FontSize="2000" DirtyInfo="true" FontName="Times New Roman" ForegroundColor="black" FontWeight="Gras" FontStyle="Simple" Text="Forms Services" FontSpacing="Normal"/>
            </CompoundText>
         </Graphics>
         <Graphics Name="TEXT55" GraphicsText="Oracle Application Server 10g" Height="16" VerticalMargin="14" GraphicsFontSpacing="Normal" GraphicsFontColor="black" Width="169" HorizontalJustification="Centre" GraphicsFontWeight="Moyen" GraphicsFontSize="1400" StartPromptOffset="7" FillPattern="none" GraphicsFontColorCode="0" HorizontalObjectOffset="14" DirtyInfo="true" Bevel="Aucun" XPosition="100" GraphicsFontStyle="0" ScrollbarWidth="14" HorizontalMargin="7" EdgePattern="none" YPosition="122" GraphicsType="Texte" BackColor="yellow" GraphicsFontName="Times New Roman" JoinStyle="Relief">
            <CompoundText Name="CMPTXT56">
               <TextSegment Name="TEXTSTR57" FontSize="1400" DirtyInfo="true" FontName="Times New Roman" ForegroundColor="black" FontWeight="Moyen" FontStyle="Simple" Text="Oracle Application Server 10" FontSpacing="Normal"/>
               <TextSegment Name="TEXTSTR58" FontSize="1400" DirtyInfo="true" FontName="Times New Roman" ForegroundColor="black" FontWeight="Moyen" FontStyle="Italique" Text="g" FontSpacing="Normal"/>
            </CompoundText>
         </Graphics>
      </Canvas>
      <Trigger Name="ON-LOGON" TriggerText="null;" DirtyInfo="true"/>
      <Trigger Name="WHEN-NEW-FORM-INSTANCE" TriggerText=":TEST.VERSION := 'Forms Services version '||GET_APPLICATION_PROPERTY(VERSION);&amp;#10;" DirtyInfo="true"/>
      <Window Name="WINDOW1" MinimizeAllowed="false" ClearAllowed="false" DirtyInfo="true" Height="212" MaximizeAllowed="false" Width="394" ResizeAllowed="false" MoveAllowed="false" Title="Welcome to Oracle Application Server Forms Services"/>
   </FormModule>
</Module>

In the XML data model, a property translates to an attribute of its corresponding category element.
The choice of attributes to store properties makes sense, however debatable for some that could potentially hold large string values such as trigger or program unit code. I think a CDATA section would have been better in those cases.

What do we do with these files?

Forms XML files are inputs of the Forms to APEX conversion process :
Converting an Oracle Forms Application

Of course, this article is not about Forms to APEX migration, so we’re going to see some practical “in-house” examples in the next sections.

 

2. Registering the XML schema in the database

For this first demo, I’ll be using the Object-Relational (OR) storage model, aka “structured” storage. I’ll probably carry on with Binary XML in a future article.

All code samples provided here have been tested on a 11.2.0.2 database (XE).

Preliminary steps

Let’s create a dedicated schema and a directory object :

SQL> conn sys@xe as sysdba
Enter password:
Connected.
SQL> create user forms identified by forms default tablespace users;

User created.

SQL> create directory forms_xml_dir as 'c:\oraclexe\test\forms';

Directory created.

SQL> grant dba to forms;

Grant succeeded.

SQL> grant read, write on directory forms_xml_dir to forms;

Grant succeeded.

And some folders in the XML DB repository :

SQL> conn forms@xe
Enter password:
Connected.
SQL> declare
  2    v_result boolean;
  3  begin
  4    v_result := dbms_xdb.CreateFolder('/forms');
  5    v_result := dbms_xdb.CreateFolder('/forms/fmb');
  6    v_result := dbms_xdb.CreateFolder('/forms/schema');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

We’re almost ready to register the XML schema.
Just a few annotations to add :

  • The XDB namespace prefix
  • xdb:storeVarrayAsTable=”true” (optional) on the schema element
  • xdb:defaultTable=”FORMS_XML” on the Module element
  • xdb:maintainDOM=”false” on each complexType
  • xdb:SQLType=”CLOB” on the aforementioned ProgramUnitText and TriggerText attributes
<?xml version = '1.0' encoding = 'UTF-8'?>
<schema xmlns:tns="http://xmlns.oracle.com/Forms"
 targetNamespace="http://xmlns.oracle.com/Forms"
 xmlns="http://www.w3.org/2001/XMLSchema"
 xmlns:xdb="http://xmlns.oracle.com/xdb"
 xdb:storeVarrayAsTable="true">
   <annotation>
      <documentation xml:lang="fr">
         Oracle Forms 10.1.2 Forms XML Schema Definition File
         Copyright(c) 2001, 2005, Oracle.  All rights reserved.
      </documentation>
   </annotation>
   <element name="Module" type="tns:Module" xdb:defaultTable="FORMS_XML"/>
   <element name="Alert" type="tns:Alert"/>
   ...
   <element name="Window" type="tns:Window"/>
   <complexType name="Module" xdb:maintainDOM="false">
      <choice>
         <element ref="tns:FormModule"/>
         <element ref="tns:ObjectLibrary"/>
         <element ref="tns:MenuModule"/>
      </choice>
      ...
   </complexType>
   ...
</schema>

 

Registration

Optional step : we can first load the schema from the FORMS_XML_DIR into the repository

SQL> declare
  2    v_result boolean;
  3    v_schema xmltype := xmltype(bfilename('FORMS_XML_DIR', 'forms.xsd'), nls_charset_id('AL32UTF8'));
  4  begin
  5    v_result := dbms_xdb.CreateResource('/forms/schema/forms.xsd', v_schema);
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

then register it :

SQL> begin
  2    dbms_xmlschema.registerSchema(
  3      schemaURL => 'forms.xsd'
  4    , schemaDoc => xdburitype('/forms/schema/forms.xsd').getXML()
  5    , local => true
  6    , genTypes => true
  7    , genbean => false
  8    , genTables => false
  9    );
 10  end;
 11  /

PL/SQL procedure successfully completed.

On 11.2, we don’t have to set genTables => true (unless the schema contains recursive references). The necessary nested tables will be created in the next step.

 

3. Creating the XMLType table

SQL> CREATE TABLE forms_xml OF XMLTYPE
  2  XMLTYPE STORE AS OBJECT RELATIONAL
  3  XMLSCHEMA "forms.xsd"
  4  ELEMENT "Module"
  5  ;

Table created.

We check that the nested tables have been created behind the scenes :

SQL> select table_name, parent_table_name, parent_table_column from user_nested_tables;
 
TABLE_NAME                     PARENT_TABLE_NAME              PARENT_TABLE_COLUMN
------------------------------ ------------------------------ ---------------------------------------------
SYS_NT1h+7iEvzRnKPISYCDFYisg== FORMS_XML                      "XMLDATA"."ObjectLibrary"."VisualAttribute"
SYS_NTizUISF8eQcmjQP92WezTxg== FORMS_XML                      "XMLDATA"."ObjectLibrary"."VisualState"
SYS_NTrtXPJZCoQzK8nqPvp+oGug== FORMS_XML                      "XMLDATA"."ObjectLibrary"."Window"
SYS_NTZ15NTilGRWK80e3+mxvRUA== FORMS_XML                      "XMLDATA"."MenuModule"."AttachedLibrary"
SYS_NT961/xTvSQEmLneNwwrxBig== FORMS_XML                      "XMLDATA"."MenuModule"."Menu"
SYS_NTsVToNDWcRSCNOophqruUTw== FORMS_XML                      "XMLDATA"."MenuModule"."ObjectGroup"
SYS_NT0YSjLsRGRi2UMFykb9iljA== FORMS_XML                      "XMLDATA"."MenuModule"."ProgramUnit"
SYS_NT55QeWMYYTSuz/33ng8iqcQ== FORMS_XML                      "XMLDATA"."MenuModule"."PropertyClass"
SYS_NT23SuQEDjSR6jWzRRWbCO9A== FORMS_XML                      "XMLDATA"."MenuModule"."VisualAttribute"
SYS_NTOMCJ3BPVQrqDvQdHnGWiEw== FORMS_XML                      "XMLDATA"."MenuModule"."MenuModuleRole"
SYS_NTnhQ4d3x/RKWfVBY3siojPA== FORMS_XML                      "XMLDATA"."FormModule"."Coordinate"
SYS_NTvT1XqG2MRkmgvxLXwOgnDg== FORMS_XML                      "XMLDATA"."FormModule"."Alert"
SYS_NTC8VTvCPjR7GyYhxJl0JOOw== FORMS_XML                      "XMLDATA"."FormModule"."AttachedLibrary"
SYS_NTxkjPS3dWTNONwu9GlP3AoA== FORMS_XML                      "XMLDATA"."FormModule"."Block"

...

SYS_NTlgjxQR6sS46pjb1haqcLpA== SYS_NTuYBw/fBCRcCfKycF4vHsOg== Graphics
SYS_NTjvy1vur7TAWAOUmpkDcmlw== SYS_NTxkjPS3dWTNONwu9GlP3AoA== DataSourceArgument
SYS_NTOSPlWRK5TzmmwaWWyEKBRw== SYS_NTxkjPS3dWTNONwu9GlP3AoA== DataSourceColumn
SYS_NTeMtNIP23TXSY6sPkaaKBWw== SYS_NTxkjPS3dWTNONwu9GlP3AoA== Item
SYS_NT1Qy0yh52QcuyPjD6MG5hwA== SYS_NTxkjPS3dWTNONwu9GlP3AoA== Relation
SYS_NTarMDriJGT3GfYnKMK1LDjA== SYS_NTxkjPS3dWTNONwu9GlP3AoA== Trigger
 
208 rows selected

We’ll see later how to easily rename thoses tables to give them more meaningful names.

 

4. Inserting  instance documents

We have two options here : inserting directly in the FORMS_XML table, or creating the document as a resource in the XML DB repository (and therefore in FORMS_XML  via the default table mechanism). We’ll explore both approaches with an emphasis on the latter.

Loading directly into the table…

… using SQL, from the directory :

SQL> insert into forms_xml
  2  values(
  3    xmltype(
  4      bfilename('FORMS_XML_DIR', 'MODULE001_fmb.xml')
  5    , nls_charset_id('AL32UTF8')
  6    , 'forms.xsd'
  7    )
  8  );

1 row created.

 

… using SQL*Loader :

Useful to load multiple files at a time. We just have to create a list of the files we want to load, for example all “*_fmb.xml” files in the fmb directory (here 100 identical files, 580kB each) :

C:\oraclexe\test\forms\fmb>dir /b *_fmb.xml > filelist.txt

C:\oraclexe\test\forms\fmb>type filelist.txt
MODULE001_fmb.xml
MODULE002_fmb.xml
MODULE003_fmb.xml
MODULE004_fmb.xml
...
MODULE097_fmb.xml
MODULE098_fmb.xml
MODULE099_fmb.xml
MODULE100_fmb.xml

then invoke SQL*Loader using the following control file (forms.ctl) :

LOAD DATA
CHARACTERSET AL32UTF8
INFILE "filelist.txt"
APPEND
INTO TABLE forms_xml
XMLTYPE(XMLDATA) (
 filename filler char(260),
 XMLDATA LOBFILE(filename) TERMINATED BY EOF
)
C:\oraclexe\test\forms\fmb>sqlldr userid=forms@xe control=forms.ctl log=forms.log
Password:

SQL*Loader: Release 11.2.0.2.0 - Production on Mar. Oct. 25 21:50:06 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 100

 

Creating a resource…

… using PL/SQL

By creating a schema-based XMLType with the BFILE constructor, the document is automatically recognized as a Forms XML instance and loaded in the FORMS_XML table.

SQL> declare
  2    v_result boolean;
  3    v_module varchar2(260) := 'MODULE001_fmb.xml';
  4  begin
  5    v_result := dbms_xdb.CreateResource(
  6                  '/forms/fmb/'||v_module
  7                , xmltype(
  8                    bfilename('FORMS_XML_DIR', v_module)
  9                  , nls_charset_id('AL32UTF8')
 10                  , 'forms.xsd'
 11                  )
 12                );
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select xmlcast(
  2           xmlquery(
  3             'declare default element namespace "http://xmlns.oracle.com/Forms"; (: :)
  4             /Module/FormModule/@Name'
  5             passing object_value returning content
  6           )
  7           as varchar2(30)
  8         ) as module_name
  9  from forms_xml
 10  ;

MODULE_NAME
------------------------------
MODULE001

 

… using WebDAV and FTP protocols

A few points to check first :

Make sure HTTP and/or FTP ports are configured (must be non-zero) :

SQL> select dbms_xdb.getHTTPPort() as "HTTP-Port"
  2       , dbms_xdb.getFTPPort() as "FTP-Port"
  3  from dual
  4  ;

 HTTP-Port   FTP-Port
---------- ----------
      8080       2100

Use DBMS_XDB.setFTPPort and DBMS_XDB.setHTTPPort APIs to set those ports if necessary.

XML resources in the XML DB repository can be created and managed using WebDAV and FTP protocols. However, in order to use the underlying storage model (FORMS_XML table) at creation time, XML documents must be recognized as instances of the Forms XML schema.

There are two ways to handle that :

i. Adding the xsi:schemaLocation attribute in the root element

<Module xmlns="http://xmlns.oracle.com/Forms"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://xmlns.oracle.com/Forms forms.xsd"
        version="101020002">

As a preprocessing step, we can use XSLT to add the attribute in each file. For example, using  the following XSL stylesheet (addxsi.xsl) and Saxon processor :

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
                xmlns="http://xmlns.oracle.com/Forms"
                xmlns:ns0="http://xmlns.oracle.com/Forms"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                exclude-result-prefixes="ns0">
  <xsl:output method="xml" encoding="utf-8"/>
  <xsl:template match="ns0:Module">
    <Module>
      <xsl:attribute name="xsi:schemaLocation">http://xmlns.oracle.com/Forms forms.xsd</xsl:attribute>
      <xsl:apply-templates select="@*|node()"/>
    </Module>
  </xsl:template>
  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>
C:\oraclexe\test\forms>java -jar saxon9he.jar -s:in -xsl:addxsi.xsl -o:out

It’ll process all files in directory in and output transformed files in directory out.

 

ii. Defining a “namespace to schema” mapping at the repository level

As an alternative to adding the xsi:schemaLocation attribute, we can configure a default schema location mapping.
DBMS_XDB package provides an API to do that easily :

SQL> exec dbms_xdb.ADDSCHEMALOCMAPPING('http://xmlns.oracle.com/Forms', 'Module', 'forms.xsd');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

 

With the Windows WebDAV client, we just have to copy/paste the files into the target repository folder :

By the way, the size of “0 Bytes” displayed here confirms that each instance document has been automatically (and correctly) loaded in the FORMS_XML table.

Same exercise, with the FTP command-line interface :

C:\oraclexe\test\forms>ftp
ftp> open localhost 2100
Connecté à PC0900XXX.YYY.local.
220- PC0900XXX
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 PC0900XXX FTP Server (Oracle XML DB/Oracle Database) ready.
Utilisateur (PC0900XXX.YYY.local:(none)) : forms
331 pass required for FORMS
Mot de passe :
230 FORMS logged in
ftp> lcd in
Dossier local maintenant C:\oraclexe\test\forms\in.
ftp> pwd
257 "/" is current directory.
ftp> cd /forms/fmb
250 CWD Command successful
ftp> prompt
Mode interactif désactivé.
ftp> mput *.xml
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp : 597548 octets envoyés en 0,47 secondes à 1276,81 Ko/sec.
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp : 597548 octets envoyés en 0,50 secondes à 1195,10 Ko/sec.

...

200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp : 597548 octets envoyés en 0,58 secondes à 1033,82 Ko/sec.
200 PORT Command successful
150 ASCII Data Connection
226 ASCII Transfer Complete
ftp : 597548 octets envoyés en 0,56 secondes à 1063,25 Ko/sec.
ftp>
ftp> dir
200 PORT Command successful
150 ASCII Data Connection
-rw-r--r--   1 FORMS    oracle         0 OCT 27 12:37 MODULE001_fmb.xml
-rw-r--r--   1 FORMS    oracle         0 OCT 27 12:37 MODULE002_fmb.xml

...

-rw-r--r--   1 FORMS    oracle         0 OCT 27 12:38 MODULE099_fmb.xml
-rw-r--r--   1 FORMS    oracle         0 OCT 27 12:38 MODULE100_fmb.xml
226 ASCII Transfer Complete
ftp : 7300 octets reçus en 0,06 secondes à 117,74 Ko/sec.
ftp> bye
221 QUIT Goodbye.

 

5. Querying the data

Now that we have loaded some XML documents, we can run various queries on them.

Searching for a specific string across all documents, for example to track down a table usage in a block, trigger or program unit :

/* query Q1 */
SELECT XMLCast(
         XMLQuery(
           'declare default element namespace "http://xmlns.oracle.com/Forms"; (: :)
           /Module/FormModule/@Name'
           passing object_value returning content
         )
         as varchar2(30)
       ) as ModuleName
     , x.BlockName
     , x.QueryDataSourceName
FROM forms_xml t
   , XMLTable(
       XMLNamespaces(default 'http://xmlns.oracle.com/Forms')
     , '/Module/FormModule/Block'
       passing t.object_value
       columns BlockName            varchar2(30)   path '@Name'
             , QueryDataSourceType  varchar2(4000) path '@QueryDataSourceType'
             , QueryDataSourceName  varchar2(4000) path '@QueryDataSourceName'
             , DatabaseBlock        varchar2(5)    path '@DatabaseBlock'
     ) x
WHERE DatabaseBlock = 'true'
AND QueryDataSourceType = 'Table'
AND QueryDataSourceName LIKE '%CONT%'
;

 

Getting block properties, one row per property :

/* query Q2 */
SELECT x1.block_name
     , x2.property
     , utl_i18n.unescape_reference(x2.property_value) as property_value
FROM forms_xml t
   , XMLTable(
       XMLNamespaces(default 'http://xmlns.oracle.com/Forms')
     , '/Module/FormModule/Block'
       passing t.object_value
       columns block_name  varchar2(30) path '@Name'
             , block       xmltype      path '.'
     ) x1
   , XMLTable(
       xmlnamespaces(default 'http://xmlns.oracle.com/Forms')
     , '(#ora:xq_proc#){for $i in /Block/@* return <p name="{local-name($i)}">{data($i)}</p>}'
       passing x1.block
       columns property       varchar2(30)   path '@name'
             , property_value varchar2(4000) path '.'
    ) x2
WHERE XMLExists('declare default element namespace "http://xmlns.oracle.com/Forms"; (: :)
                 /Module/FormModule[@Name=$name]'
                 passing t.object_value, 'MODULE001' as "name")
;

or one column per property :

/* query Q3 */
SELECT x.*
FROM forms_xml t
   , XMLTable(
       XMLNamespaces(default 'http://xmlns.oracle.com/Forms')
     , '/Module/FormModule/Block'
     passing t.object_value
     columns Name                        varchar2(4000) path '@Name'
           , DatabaseBlock               varchar2(4000) path '@DatabaseBlock'
           , DeleteAllowed               varchar2(4000) path '@DeleteAllowed'
           , DirtyInfo                   varchar2(4000) path '@DirtyInfo'
           , InsertAllowed               varchar2(4000) path '@InsertAllowed'
           , QueryAllowed                varchar2(4000) path '@QueryAllowed'
           , ShowScrollbar               varchar2(4000) path '@ShowScrollbar'
           , UpdateAllowed               varchar2(4000) path '@UpdateAllowed'
           , UpdateChangedColumns        varchar2(4000) path '@UpdateChangedColumns'
           , DMLDataType                 varchar2(4000) path '@DMLDataType'
           , QueryDataSourceType         varchar2(4000) path '@QueryDataSourceType'
           , RecordsBufferedCount        varchar2(4000) path '@RecordsBufferedCount'
           , RecordsDisplayCount         varchar2(4000) path '@RecordsDisplayCount'
           , RecordsFetchedCount         varchar2(4000) path '@RecordsFetchedCount'
           , DMLDataName                 varchar2(4000) path '@DMLDataName'
           , NextNavigationBlockName     varchar2(4000) path '@NextNavigationBlockName'
           , PreviousNavigationBlockName varchar2(4000) path '@PreviousNavigationBlockName'
           , QueryDataSourceName         varchar2(4000) path '@QueryDataSourceName'
           , VisualAttributeName         varchar2(4000) path '@VisualAttributeName'
     ) x
WHERE XMLExists('declare default element namespace "http://xmlns.oracle.com/Forms"; (: :)
                 /Module/FormModule[@Name=$name]'
                 passing t.object_value, 'MODULE001' as "name")
;

 

Retrieving resource metadata thanks to the REF pointer :

/* query Q4 */
SELECT x2.file_name
     , x2.date_modified
     , x1.module_name
     , x1.module_title
FROM forms_xml t
   , XMLTable(
       XMLNamespaces(default 'http://xmlns.oracle.com/Forms')
     , '/Module/FormModule'
       passing t.object_value
       columns module_name   varchar2(30)   path '@Name'
             , module_title  varchar2(4000) path '@Title' 
     ) x1
   , resource_view v
   , XMLTable(
       XMLNamespaces(default 'http://xmlns.oracle.com/xdb/XDBResource.xsd')
     , '/Resource'
       passing v.res
       columns file_name      varchar2(260) path 'DisplayName'
             , date_modified  timestamp     path 'ModificationDate'
     ) x2
WHERE REF(t) = XMLCast(
                 XMLQuery(
                   'declare default element namespace "http://xmlns.oracle.com/xdb/XDBResource.xsd"; (: :)
                    data(/Resource/XMLRef)'
                    passing v.res returning content
                 )
                 as REF XMLType
               )
;

 

Updating properties in multiple documents at a time :

UPDATE forms_xml t
SET object_value = updateXML( 
                     object_value
                   , '/Module/FormModule/Block/Item/@Height'
                   , 600
                   , 'xmlns=http://xmlns.oracle.com/Forms'
                   )
;

 

What about performance?

Let’s see execution plans of the first two queries above :

Q1 :

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                                |     1 |    90 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                                |       |       |            |       |
|   2 |   NESTED LOOPS               |                                |     1 |    90 |     8   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | FORMS_XML                      |     1 |    49 |     5   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | SYS_C0010203                   |    19 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| SYS_NTxkjPS3dWTNONwu9GlP3AoA== |     1 |    41 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(SYS_CHECKACL("ACLOID","OWNERID",xmltype(''))=1)
   4 - access("NESTED_TABLE_ID"="FORMS_XML"."SYS_NC0004900050$")
   5 - filter("QueryDataSourceName" IS NOT NULL AND "SYS_ALIAS_0"."SYS_NC00039$"=HEXTORAW('04')  AND
              "QueryDataSourceName" LIKE '%CONT%' AND SYS_XMLCONV("DatabaseBlock",1,3,252,1,1)='true')

Q2 :

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                                |  1552 |  1074K|    37   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                      |                                |  1552 |  1074K|    37   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                     |                                |     1 |   707 |     8   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL               | FORMS_XML                      |     1 |   394 |     5   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID     | SYS_NTxkjPS3dWTNONwu9GlP3AoA== |    19 |  5947 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN               | SYS_C0010203                   |    19 |       |     1   (0)| 00:00:01 |
|   6 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE          |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("FORMS_XML"."SYS_NC00035$"='MODULE001' AND SYS_CHECKACL("ACLOID","OWNERID",xmltype(''))=1)
   5 - access("NESTED_TABLE_ID"="FORMS_XML"."SYS_NC0004900050$")

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

Both plans show that XPath rewrite occurs (partially for the 2nd though), and that Oracle actually accesses the data using the underlying relational structure.
In particular, we can see “SYS_NTxkjPS3dWTNONwu9GlP3AoA==” : it’s the nested table that stores “Block” elements data, which was created at step 3.

For a better understanding of the plans, we are going to rename all the generated nested tables. Back in step 3, we saw that there are 208 of them, so we have two options :

  • rename them one by one
  • batch-rename them, using a user-generated name

Obviously, I’ll develop the second option. The new name will consist of :

  • a prefix ‘F’
  • a 3-character identifier representing the nature of the module (Form, Menu or Object Library)
  • a computed path identifier derived from the target element’s access path

For example, the nested table storing RadioButton elements will be renamed like this :
‘/Module/FormModule/Block/Item/RadioButton’ –> ‘FFM$B_I_RB‘.

The following will do the job in correct order, and take care of duplicate paths :

BEGIN
  FOR r IN (
    SELECT prior sys_connect_by_path(short_name, '_') as parent_name
         , parent_table_column
         , sys_connect_by_path(short_name, '_') as name
    FROM (
      SELECT parent_table_name
           , table_name
           , short_name || 
             case when count(*) over(partition by parent_table_name, short_name) > 1
                  then to_char(
                         row_number() over(partition by parent_table_name, short_name order by table_name)
                       , 'fm09'
                       )
             end as short_name
           , parent_table_column
      FROM (
        SELECT parent_table_name
             , table_name
             , regexp_substr(table_type_name, '^\D+') as type_name
             , case when parent_table_column like '"XMLDATA"."FormModule"%' then 'FM$'
                    when parent_table_column like '"XMLDATA"."ObjectLibrary"%' then 'OL$'
                    when parent_table_column like '"XMLDATA"."MenuModule"%' then 'MM$'
               end ||
               regexp_replace(regexp_substr(table_type_name, '^\D+'), '[^[:upper:]]') as short_name
             , parent_table_column
        FROM user_nested_tables
        WHERE table_type_owner != 'XDB'
      )
    )
    CONNECT BY PRIOR table_name = parent_table_name
    START WITH parent_table_name = 'FORMS_XML'
  )
  LOOP
   
   dbms_xmlstorage_manage.renameCollectionTable(
     tab_name              => case when r.parent_name is null then 'FORMS_XML' else 'F'||substr(r.parent_name,2) end
   , xpath                 => r.parent_table_column
   , collection_table_name => 'F'||substr(r.name,2)
   );
   
  END LOOP;
END;
/

DBMS_XMLSTORAGE_MANAGE used here is part of the XML DB Manageability Packages available on the Oracle XML DB Sample Code page.

That being done, let’s focus back on query Q2.
In Q2, we want to access only one document (whose module name is “MODULE001”), and we logically end up with a FULL SCAN on FORMS_XML table. Since the module name is supposed to be unique in the table, we can create an index on the underlying column :

SQL> create unique index forms_xml_module_name_uix on forms_xml ("XMLDATA"."FormModule"."Name");

Index created.

The new execution plan now looks like :

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                           |   155K|   104M|   522   (1)| 00:00:07 |
|   1 |  NESTED LOOPS                      |                           |   155K|   104M|   522   (1)| 00:00:07 |
|   2 |   NESTED LOOPS                     |                           |    19 | 13433 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID     | FORMS_XML                 |     1 |   394 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN              | FORMS_XML_MODULE_NAME_UIX |     1 |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID     | FFM$B                     |    19 |  5947 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN               | SYS_C0010203              |    19 |       |     1   (0)| 00:00:01 |
|   7 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE     |  8168 | 16336 |    27   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(SYS_CHECKACL("ACLOID","OWNERID",xmltype(''))=1)
   4 - access("FORMS_XML"."SYS_NC00035$"='MODULE001')
   6 - access("NESTED_TABLE_ID"="FORMS_XML"."SYS_NC0004900050$")

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

The warning note at the bottom is a new feature introduced in 11.2.0.2 : Diagnosing XQuery Optimization. The “construct” it refers to is the one behind the COLLECTION ITERATOR operation, which happens here because a part of the XQuery must undergo a functional evaluation :

(#ora:xq_proc#)
{
  for $i in /Block/@* 
  return <p name="{local-name($i)}">{data($i)}</p>
}

What we are doing here is similar to the UNPIVOT operation in SQL, and cannot be rewritten to use the OR storage model. We’ll see in a future article that such optimization is possible with Binary XML storage.

 

What next?

If some XML documents have been modified, either individually or through set operations, then we could export them from the repository to the OS file system using FTP or WebDAV, and ultimately convert them back to Forms binaries.

Advertisements
  1. February 2, 2012 at 14:06

    Very helpfull and needy article for batch forms transformations and operations on forms during migration procedure.
    Thanx for sharing it.

  2. Hans
    August 8, 2014 at 14:08

    I know this is an old post but I recently needed to convert some forms to xml and found your post.

    Some of my fmb’s are giving the following error during the insert into the forms_xml table :

    Record 1: Rejected – Error on table FORMS_XML.
    ORA-21700: object does not exist or is marked for delete

    and

    Record 9: Rejected – Error on table FORMS_XML.
    ORA-30951: Element or attribute at Xpath Comment exceeds maximum length

    insert into forms_xml
    values(
    xmltype(
    bfilename(‘FORMS_DIR’, ‘xpro_fmb.xml’)
    , nls_charset_id(‘AL32UTF8’)
    , ‘forms.xsd’
    )
    );
    xmltype(
    *
    ERROR at line 3:
    .ORA-21700: object does not exist or is marked for delete

    Could you explain what could be the problem ?

    Thanks

    Hans

  3. Andy Krenitz
    December 30, 2014 at 00:52

    I have a schema registered with Oracle OTA_SimpleTypes.xsd. I am able to use this schema in an and the referencing XSD sees it. However, when I try to use it I get HTNG_SimpleTypes.xsd ORA-31000: Resource ‘../opentravel/OTA_SimpleTypes.xsd’ is not an XDB schema document

  4. April 12, 2016 at 16:05

    Excellent article, very useful. Thanks for the effort,

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: