Adding OBJECT data to an Oracle database

Use the input schema to add OBJECT data to your Oracle database.

Before you begin

For this example to work, a database administrator must run the following SQL statements to create an example object. You can have your database administrator run the SQL statements, or you can modify the example to use an existing object.

CREATE OR REPLACE TYPE ADDRESS_OBJECT_TYPE AS OBJECT (
     streetAddress   VARCHAR(30),
     city            VARCHAR(30),
     state           VARCHAR(2),
     postalCode      INTEGER);
/

grant all privileges on ADDRESS_OBJECT_TYPE TO DBUSER;

CREATE TABLE DBUSER.CUSTOMER_TABLE (
     lname           VARCHAR(30),
     address         ADDRESS_OBJECT_TYPE);
Additionally, you need an input XML file in this format.
<?xml version="1.0" encoding="UTF-8"?>
<Customer>
  <lastname>Smith</lastname>
  <address>10 Main Street</address>
  <city>Boston</city>
  <state>MA</state>
  <zip>02188</zip>
</Customer>

About this task

The SQL INSERT statement adds OBJECT data to the database.

Procedure

  1. Create a stylesheet in the following format.
    This stylesheet obtains the data from the input XML file.
    <?xml version="1.0" encoding="UTF-8"?>
    <xsl:stylesheet version="1.0"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xmlns:dp="http://www.datapower.com/extensions"
        xmlns:dpconfig="http://www.datapower.com/param/config"
        extension-element-prefixes="dp" exclude-result-prefixes="dp dpconfig">
      <xsl:output method="xml" encoding="utf-8" />
    
      <xsl:template match="/Customer">
        <xsl:variable name="statement">
          INSERT INTO DBUSER.CUSTOMER_TABLE  VALUES (?,?)
        </xsl:variable>
        <dp:sql-execute source="'OracleDS'" statement="$statement">
          <arguments>
            <argument type="SQL_VARCHAR">
              <xsl:value-of select="./lastname/text()" />
            </argument>
            <argument type="SQL_STRUCT">
              <object typename="SYS.ADDRESS_OBJECT_TYPE">
                <field name="streetAddress" type="SQL_VARCHAR">
                  <xsl:value-of select="./address/text()" />
                </field>
                <field name="city" type="SQL_VARCHAR">
                  <xsl:value-of select="./city/text()" />
                </field>
                <field name="state" type="SQL_VARCHAR">
                  <xsl:value-of select="./state/text()" />
                </field>
                <field name="postalCode" type="SQL_INTEGER">
                  <xsl:value-of select="./zip/text()" />
                </field>
              </object>
            </argument>
          </arguments>
        </dp:sql-execute>
      </xsl:template>
    </xsl:stylesheet>
  2. Send a request through a DataPower® service.

Results

<?xml version="1.0" encoding="UTF-8"?>
<sql result="success" modified="1"/>