Hello Data Templates

Hello Data Templates

By Tim Dexter-Oracle on Dec 13, 2006

Hopefully you have downloaded and installed the Data Template Test Bed I provided in my last post, you may have even loaded and run the sample I provided ... hopefully sucessfully. You may have even gone off and got started on your own. Well we are going to take a step back here and get back to basics and then move up to the more powerful stuff like filters, triggers and flexfields ... we're going to get there quickly so please be patient.

Being the consumate geek and not wanting to break tradition when it comes to starting out with a new technology we're going to build and test a Hello World data template ... now known as 'DTs' for brevity. First lets take a look at the anatomy of a DT, it'll help later.

<dataTemplate name="EMPLOYEES" defaultPackage="" description="Employee Data">
   <properties>
      <property name="include_parameters" value="true"/>
      <property name="include_null_Element" value="true"/>
      <property name="xml_tag_case" value="upper"/>
      <property name="db_fetch_size" value="500"/>
      <property name="scalable_mode" value="off"/>
      <property name="include_rowsettag" value="false"/>
      <property name="debug_mode" value="off"/>
   </properties>
   <parameters>
      <parameter name="pDeptNo" dataType="number" defaultValue=""/>
   </parameters>
   <lexicals/>
   <dataQuery>
  <sqlStatement name="Q1" dataSourceRef=""><![CDATA[SELECT DEPTNO,DNAME,LOC 
 from dept where deptno = nvl(:p_DeptNo,deptno)
 order by deptno]]>
 </sqlStatement>
  <sqlStatement name="Q2" dataSourceRef=""><![CDATA[SELECT  EMPNO,ENAME,JOB
 ,MGR,HIREDATE,SAL,nvl(COMM,0) COMM  
 from EMP 
 WHERE DEPTNO = :DEPTNO]]>
 </sqlStatement>
  </dataQuery>
   <dataStructure>
  <group name="G_DEPT" source="Q1" groupFilter="">
   <element name="DEPT_NUM" value="DEPTNO" function=""/>
   <element name="DEPT_NAME" value="DNAME" function=""/>
   <element name="LOCATION" value="LOC" function=""/>
   <group name="G_EMP" source="Q2" groupFilter="">
    <element name="EMPNO" value="EMP_NUM" function=""/>
    <element name="EMP_NAME" value="ENAME" function=""/>
    <element name="JOB_TITLE" value="JOB" function=""/>
    <element name="MANAGER" value="MGR" function=""/>
    <element name="HIRE_DATE" value="HIREDATE" function=""/>
    <element name="SALARY" value="SAL" function=""/>
    <element name="COMMISSION" value="COMM" function=""/>
   </group>
  </group>
 </dataStructure>
</dataTemplate> 

I have highlighted the main players in a DT:

• dataTemplate - header element where you provide a name and description for your DT. The name will be used as the root name of the element. 
• properties - these are the runtime switches for the extraction engine:
  
  
  
  • include_parameters - should the parameter values be extracted to the XML or not 
  • include_null_Element - if a value when extracted is null do you want an empty element in the xml 
  • xml_tag_case - upper or lower case tag names 
  • db_fetch_size - maximum number of records to fetch 
  • scalable_mode - if you know this is going to be a large dataset or an intensive extract then set this to true 
  • include_rowsettag - for simple XML with no hierarchy do you want ROWSET as the root element 
  • debug_mode - generate a debug file while processing or not. The test bed I posted will always do this for you.

Moving into the main section:

• parameters - these are the runtime parameters for the DT 
• lexicals - these are EBS specific and will allow you to fetch flexfield values ... more on these later 
• dataQuery - heres where we start to define the
  sqlStatement - this is where we define the query(s) 
• group - this section defines the structure of the data we want to see and contains 
• element - the reference to the column in a specific query. These also have a name for the resulting XML element and you can include functions here.

So thats a quick overview, lets do the HelloWorld ... its as simple as it gets when it comes to DTs.

<dataTemplate name="HELLOWORLD" defaultPackage="" description="Hello World DT">
   <properties>
      <property name="include_parameters" value="true"/>
      <property name="include_null_Element" value="true"/>
      <property name="xml_tag_case" value="upper"/>
      <property name="db_fetch_size" value="500"/>
      <property name="scalable_mode" value="off"/>
      <property name="include_rowsettag" value="false"/>
      <property name="debug_mode" value="off"/>
   </properties>
   <parameters/>
   <lexicals/>
   <dataQuery>
  <sqlStatement name="Q1" dataSourceRef=""><![CDATA[select 'Hello World!' WELCOME from dual]]></sqlStatement>
 </dataQuery>
   <dataStructure>
  <group name="HELLO" source="Q1">
   <element name="WELCOME" value="WELCOME"/>
  </group>
   </dataStructure>
</dataTemplate>

Running it thru the test bed, this is going to generate the following XML:

<?xml version="1.0" encoding="UTF-8"?>
<HELLOWORLD>
 <HELLO>
  <WELCOME>Hello World!</WELCOME>
 </HELLO>
</HELLOWORLD>

Simple stuff, you can see the relationship now between the DT name - HELLOWORLD, group - HELLO and element name WELCOME.
So thats the basics, next we'll build on the HR schema and introduce some more features.