XML Publisher is
a template based publishing solution by Oracle which allows us to create
visually rich documents with numerous formatting options.
The data templates are useful
when you need to create a XML Publisher report without using the RDF, SQL or PL/SQL
concurrent program. The XML Publisher data engine enables you to rapidly
generate any kind of XML data structure against any database in a scalable,
efficient manner which you can easily use in your templates.
Data Template:
The data template is an XML
document that consists of four basic sections:
- Parameters (Optional)
- Triggers (Optional)
- Data Query
- Data Structure
Below is the simple data
template
Data Template Declaration:
This is the root element of data template
and it is required. It’s has 5 Attributes:
Attribute Name
|
Required
|
Description
|
Name
|
Yes
|
Data Template Name
|
description
|
No
|
Data Template Description
|
Version
|
Yes
|
Version of Data Template
|
dataSourceRef
|
Yes/No
|
Required when Data Template have
lexical reference or other PL/SQL call like triggers
|
defaultPackage
|
No
|
The default data source reference for
entire template
|
Example:
<dataTemplate
name=”testTemplate” version =”1.0” >
--- Data query, Data
structure etc
</ dataTemplate>
1. Parameters Section:
A parameter is a variable whose value you can set at runtime. Parameters
are especially useful for modifying SELECT statements and setting PL/SQL
variables at runtime. However, the Parameters section of the data template is
optional and it’s has the following attributes:
Attribute Name
|
Required
|
Description
|
Name
|
Yes
|
Parameter Name
|
dataType
|
No
|
Data Type of the Parameter (character,
number, data)
|
defaultValue
|
No
|
Default Value for Parameter
|
Include_in_output
|
No
|
Include Parameter in XML output
|
Example:
<parameters>
<parameter
name="P_DATE_FROM" dataType="character" />
<parameter
name="P_DATE_TO" dataType="character" />
</parameters>
Passing Parameter Example:
To pass parameters, (for example, to
restrict the query), use bind variables in your query.
SELECT *
FROM apps.gl_balances glb
WHERE glb.period_name BETWEEN
:P_DATE_FROM AND :P_DATE_TO;
2. Data Query Section:
The section of the data template is
required and it’s has the following attributes:
Attribute Name
|
Required
|
Description
|
Name
|
Yes
|
Query Name (Must be unique)
|
Example
<dataQuery>
<sqlStatement
name="Q1">
<![CDATA[ SELECT *
FROM apps.gl_balances glb
WHERE glb.period_name
BETWEEN :P_DATE_FROM AND :P_DATE_TO]]>
</sqlStatement>
</dataQuery>
2.1. 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.
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.
Syntax:
&ParameterName
Example:
<dataQuery>
<sqlstatement
name="Q1">
<![CDATA[SELECT ENAME,
SAL FROM EMP &where_clause]]>
</sqlstatement>
</dataQuery>
2.2. Data Link:
If you have multiple queries, you must link them to create the
appropriate data output. In the data template, there are two methods for
linking queries: using bind variables or using the <link> element to
define the link between queries. This element has the following attributes:
Attribute Name
|
Required
|
Description
|
Name
|
Yes
|
Link Name (Must be unique)
|
parentQuery
|
Yes
|
Parent Query Name (Must be defined in
template)
|
parentColumn
|
Yes
|
Parent Column Name.
|
parentQuery
|
Yes
|
Child Query Name (Must be defined in
template)
|
parentColumn
|
Yes
|
Child Column Name.
|
Example:
<link
name="DEPTEMP_LINK" parentQuery="Q1"
parentColumn="DEPTNO" childQuery="Q_2"
childColumn="DEPARTMENTNO"/>
3. 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 elements as necessary. It’s has the following attributes:
Attribute Name
|
Required
|
Description
|
Name
|
Yes
|
Trigger Name (Must be unique)
|
source
|
Yes
|
The PL/SQL where the executable code
resides
|
Example:
<dataTrigger
name="beforeReport1" source=" xx_test_pkg.beforeReport()"/>
<dataTrigger
name="beforeReport2" source=" xx_test_pkg.beforeReport(:Parameter)"/>
4. Data Structure Section:
In the data
structure section you define what the XML output will be and how it will be
structured. The complete group hierarchy is available for output. You can
specify all the columns within each group and break the order of those columns;
you can use summaries, and placeholders to further customize within the groups.
Data Structure consists of 2 elements
group and element. Group has the following attributes:
Attribute Name
|
Required
|
Description
|
Name
|
Yes
|
Group Name (Must be unique)
|
source
|
Yes
|
Query Source Name
|
Element has the following attributes:
Attribute Name
|
Required
|
Description
|
Name
|
Yes
|
Element Name (Must be unique)
|
value
|
Yes
|
Query Column Name or element name for
aggregation
|
function
|
No
|
The aggregation function to be
performed.
|
Example:
<dataStructure>
<group name="G1"
source="Q1">
<element
name="LEDGER_ID" value="LEDGER_ID" />
<element
name="LEDGER_SHORT_NAME" value="LEDGER_SHORT_NAME" />
<element
name="LEDGER_DESCRIPTION" value="LEDGER_DESCRIPTION" />
<element
name="LEDGER_NAME" value="LEDGER_NAME" />
<element
name="SUM_BAL_DR" value="G2.ACCT_BAL_DR"
function="SUM()" />
<element
name="SUM_BAL_CR" value="G2.ACCT_BAL_CR"
function="SUM()" />
<group name="G2"
source="Q1">
<element name="PERIOD_YEAR"
value="PERIOD_YEAR" />
<element name="PERIOD_NUMBER"
value="PERIOD_NUMBER" />
<element name="PERIOD_NAME"
value="PERIOD_NAME" />
<element name="CODE_COMB"
value="ACCOUNTING_CODE_COMBINATION" />
<element name="CODE_DESC"
value="CODE_COMBINATION_DESCRIPTION" />
<element name="ACCT_BAL_DR"
value="BEGIN_BALANCE_DR" />
<element name="ACCT_BAL_CR"
value="BEGIN_BALANCE_CR" />
</group>
</group>
</dataStructure>
Note Related To Better Performance:
·
BI Publisher tests have shown that using bind variables is more
efficient than using the link tag.
·
Use a WHERE clause instead of a group filter to exclude records.
·
Perform calculations directly in your query rather than in the template.
Great..!!!
ReplyDelete