What is a Data Template?
The Oracle XML Publisher data engine enables you to
rapidly generate any kind of XML data structure against any database in a
scalable, efficient manner. The data template is the method by which you
communicate your request for data to the data engine. It is an XML document
whose elements collectively define how the data engine will process the
template to generate the XML.
What Functionality is Supported?
The data engine supports the following functionality:
• Schema
generation
• Default
RTF template generation
• Flexfields
• Single
and multiple data queries
• Query
links
• Parameters
• Aggregate
functions (SUM, AVG, MIN, MAX, COUNT)
• Event
triggers
• Multiple
data groups
• Unicode
for XML Output
Unicode
is a global character set that allows multilingual text to be displayed in a
single application. This enables you to develop a single multilingual
application and deploy it worldwide.
• Canonical
format
The
data engine generates date elements using the canonical ISO date format:
YYYY-MM-DDTHH24:MI:SS.FF3TZH:TZM for a mapped date element, and ######.## for
number elements in the data template XML output.
Report Migration
The data template is an XML document that consists of
four basic sections.
As shown in the sample figure, the data template consists of
a <parameters> section in which parameters are declared in child
<parameter> elements; a <dataQuery> section in which the SQL
queries are defined in child <sqlStatement> elements; and a
<dataStructure> section in which the output XML structure is defined.
Element:
dataTemplet Required
parameters Consists
of one or more <parameter> elements
parameter the
parameter name that will be referenced in the template
lexicals Consists
of one or more lexical elements to support flexfields
lexical 4
types – KFF segments, KFF, KFF where, and KFF order by
dataQuery Consists
of one or more sqlstatement elements
sqlstatement a
query
link the
SQL operator that defines the parent-child relationship
dataTrigger the
trigger and the event that it fires on
dataStructure Required
for multiple queries
group Consists
of one or more <element> elements and sub <group>
elements
element the
tag name to assign the element in the XML data output
Data Template Structure
Data Template Declaration
Define Parameters
How to Pass Parameters
To pass parameters, (for example, to restrict the query),
use bind variables in your query. For example:
Query:
SELECT * FROM EMP WHERE deptno=:department At runtime, the
value of department is passed to the query:
SELECT * FROM EMP WHERE deptno=10
Define Queries
If your column names are not unique, you must use aliases in
your SELECT statements to ensure the uniqueness of your column names. If you do
not use an alias, then the default column name is used. This becomes important
when you specify the XML output in the dataStructure section. To specify an
output XML element from your query you declare a value attribute for the
element tag that corresponds to the source column.
Tip: Performing operations in SQL is faster than
performing them in the data template or PL/SQL. It is recommended that you use
SQL for the following operations:
•
Use a WHERE clause instead of a group filter to
exclude records.
•
Perform calculations directly in your query rather
than in the template.
Lexical References
You can use lexical references to replace the clauses
appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING. Use a
lexical reference when you want the parameter to replace multiple values at
runtime.
Create a lexical reference using the following syntax:
¶metername
Define the lexical parameters as follows:
•
Before creating your query, define a parameter
in the PL/SQL default package for each lexical reference in the query. The data
engine uses these values to replace the lexical parameters.
•
Create your query containing lexical references.
For example:
Package employee
AS
where_clause
varchar2(1000);
.....
Package body employee
AS
.....
where_clause := 'where deptno=10';
.....
Data template definition:
<dataQuery>
<sqlstatement
name="Q1">
<![CDATA[SELECT
ENAME, SAL FROM EMP &where_clause]]>
</sqlstatement>
</dataQuery>
Define Data Links
Tip: To maximize performance when building data
queries in the data template:
XML Publisher tests have shown that using bind variables is
more efficient than using the link tag.
The following example shows a query link using a bind
variable:
<dataQuery>
<sqlstatement
name="Q1">
<![CDATA[SELECT
EMPNO, ENAME, JOB from EMP WHERE DEPTNO = :DEPTNO]]>
</sqlstatement>
</dataQuery>
The <link> element has a set of attributes. Use these
attributes to specify the required link information. You can specify any number
of links. For example:
<link name="DEPTEMP_LINK"
parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q_2"
childColumn="DEPARTMENTNO"/>
Data Triggers
Data triggers execute PL/SQL functions at specific times
during the execution and generation of XML output. Using the conditional
processing capabilities of PL/SQL for these triggers, you can do things such as
perform initialization tasks and access the database.
Data triggers are optional, and you can have as many
<dataTrigger> elements as necessary.
The <dataTrigger> element has a set of related
attributes. These are expressed within the <dataTrigger> tag. For
example, the name and source attributes are expressed as follows:
<dataTrigger name="beforeReport"
source="employee.beforeReport()"/> <dataTrigger
name="beforeReport"
source="employee.beforeReport(:Parameter)"/>
The location of the trigger indicate at what point the
trigger fires:
•
Place a beforeReport trigger anywhere in your
data template before the <dataStructure> section.. A beforeRepot trigger
fires before the dataQuery is executed.
•
Place an afterReport trigger after the
<dataStructure> section. An afterReport trigger fires after you exit and
after XML output has been generated.
The Data Structure Section
In the data structure section you define what the XML
output will be and how it will be structured. You can do the following:
• Define
a group hierarchy.
• Create
break groups.
• Apply
group filters.
• Create
summary columns.
Flexfield Support
This applies to queries against an Oracle eBusiness Suite
database. You create them as follows:
• Define
the SELECT statement to use for the report data.
• Within
the SELECT statement, define each flexfield as a lexical. Use the
&LEXICAL_TAG to embed flexfield related lexicals into the SELECT statement.
• Define
the flexfield-related lexicals using XML tags in the data template.
Data Query
How to define Queries:
–
Uses <sqlStatement name="">
to define query
–
Performing operations in SQL is faster than
performing them in Data Template or PL/SQL. The following are the most common
cases where using SQL would improve performance:
–
Use a WHERE clause instead of a group filter
to exclude records.
–
Perform calculations directly in your query
rather than in template
Example:
Data Query
<parameters>
<parameter name="p_DeptNo"
dataType="character" />
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[
SELECT d.DEPTNO,d.DNAME,d.LOC,
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL
from dept d, emp e
where d.deptno=e.deptno
AND d.deptno =
nvl(:p_DeptNo,d.deptno) ]]>
</sqlStatement>
</dataQuery>
Linking Queries
Two ways of linking
are supported
- Bind variables in your query
- Link element <link name="" ……../>
Performance
Considerations
“As far as our test shows that use bind
PL/SQL variable for detail query is
more efficient to merge the two queries rather than build one single query out
of that “
This might not apply
to all cases.
Example: Link Query
Using Link tag
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[
SELECT DEPTNO,DNAME,LOC from dept
where &pwhereclause
order by deptno ]]>
</sqlStatement>
<sqlStatement name="Q2">
<![CDATA[
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL
from EMP ]]>
</sqlStatement>
<link name="DEPTEMP_LINK"
parentQuery="Q1" parentColumn="DEPTNO" childQuery="Q2"
childColumn=“DEPTNO“condition="="/>
</dataQuery>
Data Triggers
Data Triggers can be used
Ÿ Perform
initialization tasks
Ÿ Allows
to build dynamic query
Data Triggers Types
Ÿ beforeReport
trigger: Fires before the dataQuery is executed .
Ÿ afterReport trigger
: Fires after you exit and after XML output has been generated
Example :-
<dataTrigger name="beforeReport"
source="employee.beforeReport()"/>
<dataTrigger name="beforeReport"
source="employee.beforeReport(:Parameter)"/>
Data Structure
Break groups
•
Order By in SQL query should be set
Group filters
•
WHERE clause Should be use instead of a group
filter
Summary Column
•
Sum, Average, Count, Maximum and Minimum
Example :-
<group name="G_DEPT" source="Q1"
groupFilter="empdata.G_DEPTFilter(:DEPT_NUMBER)">
<element
name="DEPT_NUMBER" value="DEPTNO" />
<element
name="DEPTSAL"
value="G_EMP.SALARY"
function="SUM()"/>
<group name="G_EMP" source="Q2">
<element name="EMPLOYEE_NUMBER" value="EMPNO"
/>
<element name="NAME"
value="ENAME"/>
<element name="JOB" value="JOB" />
<element name="SALARY" value="SAL"/>
</group>
</group>
Flexfield Support
Ÿ Flexfield
aware
Ÿ Support
all Flexfield features
Ÿ
KFF related
lexicals
•
oracle.apps.fnd.flex.kff.segments_metadata
•
oracle.apps.fnd.flex.kff.select
•
oracle.apps.fnd.flex.kff.where
•
oracle.apps.fnd.flex.kff.order_by
Example: Flexfield Query
<dataTemplate ...
<parameters ...
</parameters>
<lexicals>
<lexical
type="oracle.apps.fnd.flex.kff.select"
name="FLEX_VALUE_ALL"
comment="Comment"
application_short_name="SQLGL"
id_flex_code="GL#"
id_flex_num=":P_ID_FLEX_NUM"
multiple_id_flex_num="N"
code_combination_table_alias="gcc"
segments="ALL"
show_parent_segments="Y"
output_type="VALUE"
/>
</lexicals>
Easy and Quick to Build
<?xml version="1.0" encoding="WINDOWS-1252"
?>
<dataTemplate
name=“Employees" description="Templat"
version="1.0">
<parameters>
<parameter name="Parameter1" dataType="character"
defaultValue="Null"/>
<parameter name="Parameter2" dataType="date"
formatMask="DD/MM/YYYY"/>
</parameters>
<dataQuery>
<sqlStatment name="Q1">
<![CDATA[SELECT DEPTNO,DNAME,LOC from dept]]>
</sqlStatment>
<sqlStatment name="Q2">
<![CDATA[SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM
from EMP
WHERE DEPTNO = :DEPTNO]]>
</sqlStatment>
</dataQuery>
<dataTrigger name="beforeReport"
source="employee.beforeReport(:parameter1)"/>
<dataStructure>
<group name="G_DEPT" source="Q1“
groupFilter="empdata.G_DEPTF(:parameter1)">
<element name="DEPT_NUMBER" value="DEPTNO" />
<element name="DEPT_NAME"
value="DNAME"/>
<element name="DEPTSAL"
value="G_EMP.SALARY" type="SUM()"/>
<element name="LOCATION" value="LOC" />
<group name="G_EMP" source="Q2">
<element name="EMPLOYEE_NUMBER" value="EMPNO"
/>
<element name="NAME" value="ENAME"/>
<element name="JOB"
value="JOB" />
<element name="MANAGER" value="MGR"/>
<element name= "HIREDATE" value="HIREDATE"/>
<element name="SALARY" value="SAL"/>
</group>
</group>
</dataStructure>
</dataTemplate>
Output:
<?xml version="1.0"
encoding="UTF-8" ?>
- <Employees>
- <LIST_G_DEPT>
- <G_DEPT>
<DEPT_NUMBER>10</DEPT_NUMBER>
<DEPT_NAME>ACCOUNTING</DEPT_NAME>
<LOCATION>NEW YORK
- <LIST_G_EMP>
- <G_EMP>
<EMPLOYEE_NUMBER>7782</EMPLOYEE_NUMBER>
<NAME>CLARK </NAME>
<JOB>MANAGER</JOB>
<MANAGER>7839</MANAGER>
<HIREDATE>1981-00-09T00:00:00PDT</HIREDATE>
<SALARY>2450</SALARY>
</G_EMP>
- <G_EMP>
<EMPLOYEE_NUMBER>7839</EMPLOYEE_NUMBER>
<NAME>KING</NAME>
<JOB>PRESIDENT</JOB>
<MANAGER />
<HIREDATE>1981-00-17T00:00:00PST</HIREDATE>
<SALARY>5000</SALARY>
</G_EMP>
-
<G_EMP>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.