This time, there are no high fundas. This article will contain a step-by-step explanation of the entire conversion procedure with examples and screenshots.
Prerequisites:
1) You have a perfectly working RDF report that is registered as a Concurrent Program
2) You have all the required responsibilities to create a concurrent program and also the XML Publisher Responsibility to register the data definition and templates.
3) You have some basic idea of XML and XSLT. If not, you can refer the w3schools site:
4) You have the required utility in your MS Word that facilitates the creation of RTF Template.
If not, you can download it from
Ok, so lets get started.
I shall convert the BR vacation report which is in RDF to the XMLP Report.
1) Copy the existing CP to create a new CP with relevant short_name, executable as XDODTEXE which is a Java Concurrent Program that will read the XML data Definition and execute the query in it to generate the desired output and output format as 'XML' as shown below:
2) While defining the parameters, make sure you are passing the token value.
Two Points to note here are :
1. The short name of the registered CP should be exactly same as the code name of the data definition .
2. The token passed should exactly match the parameter name defined in the data definition. Both should be in the same case.
3) Now we shall create the data definition. Here is the data definition xml file for your reference:
So that's the data definition xml that you can use as sample.
As you can see, in the xml data definition,
1.We first declare the version and the encoding which is by default UTF-8. '
2.Then we give a name to the data definition followed by the names of the corresponding pl/sql
package name the data definition refers so as to derive the values of the formula columns or
pl/sql logic for the report triggers etc.
3. Then comes the property tag that specifies the case of the xml tags here. We can declare other
properties too in this segment if we want and we may even totally avoid this segment.
4. Then comes the parameter segment where we declare the parameters that we would we
passing through our CP. The case and name of the parameter should exactly match the token
we pass while declaring the parameters.
5. Followed by the parameter is the sql query which is essentially same as the sql query in the
RDF report. But any formula columns that we calculate in separate program unit inn the RDF
report, has to be calculated here , in this query section in xml report. For such calculations, we
can directly refer the packaged functions or procedures. For eg : If we want a formula column
CF_Employee_age. Then we can write the pl/sql logic of calculating the age of an employee in
the packaged function MISPER_BR_VABAL.age. he logic here will be same as the logic in the
RDF program unit for CF_Employee age but here we write it in tha packaged function and call
it the query section as select ...., MISPER_BR_VABAL(p_employee_date_of_birth)
CF_Employee_age .....from ....., where...... ( Assuming p_employee_date_of_birth is one of our
user parameters declared in the parameter section or some column of any table.
Here , an important point to note is that, if the packaged function internally calls any other function that uses any DML, DDL, or TCL on any temporary table then we might get an error. This is because, here we are calling the function from a sql statement and functions performing DML, DDL or TCL are not allowed to be called from sql statement. In such cases, we might have to declare the DML, DDL or TCL performing function as PRAGMA AUTONOMOUS_TRANSACTION so that it is executed as an independent program unit.
6. After the query section, we declare the report triggers which again have their code wriiten in packaged functions or procedures.
7. In the data structure portion, we first write the grouping information followed by the element names. If we have any summary columns, we declare them here. For eg :
Here, we declare two summary columns and one formula column.
4) Once this much is done , we register the data definition through the xml publisher Administrator responsibility as shown below :
As mentioned earlier, the code name should be same as the CP short name. That is how the executable identifies the data definition corresponding to a CP.
5) Now, we need to create the RTF template. For this we will first run the CP and save the output as the xml file. Then we shall open MS Word and load the XML file using the Oracle BI Utility as mentioned in teh Prerequisites. Following this we shall add the data fields as required on the RTF file and save it. Here is a sample RTF File :
6) The data fields generally have a structure like this:
7) In the 'Add Help Text' , we write the actual data tag or any formatting trigger in XSLT that is to be picked from the output XML. The default text is just a place holder for the actual data and can be written as anything. For example, we want to display a particular layout only if the value of a particular parameter is 'N' then, in XSLT the condition can be written as follows:
8) Ok, now once we have the RTF template ready, we need to check if this is fine. For doing so, we load the XML output obtained earlier in step 5) and then check the preview in pdf or html or excel or text. If this is ok and we are satisfied with the output, then we register the template.
9) For registering, we again go to the XML Publisher Administrator Responsibility, and the go to Template Definition and register the template as follows:
10) Now, we have the CP, the data definition as well as the RTF template in place.
Our steps our almost done. Now we only need to run the CP and check the output.
OK, one important point, while declaring the CP Parameters, you need to declare one additional parameter DebugFlag only for test purposes as follows:
This is just to debug the report in case we are getting errors. You can set this to 'N' in case you don't want it and you can also remove this once everything is working fine. Remember, you do not need to declare this parameter in the data definition or in the packages or anywhere else . The CP executable XDODTEXE knows how to handle this.
11) Just for you reference, I am pasting the codes of a sample package spec and package body:
This is the MISPER_BR_VABALS.pls and below is the pkg body MISPER_BR_VABALB.pls:
So that completes all the components of the XML report.
Just a reminder : Before running the CP at step 5), make sure that you have compiled the pkg spec and pkg body on the same instance where you are submitting the CP.
Thisn completes the entire RDF to XML Conversion process.
I shall try to discuss some common errors we face and the troubleshooting that requires to be done in my next blog.
Till then , do try this out and let me know how it works. Hope this helps everyone who reads it and surely me so that I donot forget the steps.
Ok, then, its too late for me already 8:30pm and I am still in office. I got to go. Bbye and take care.
3) Now we shall create the data definition. Here is the data definition xml file for your reference:
So that's the data definition xml that you can use as sample.As you can see, in the xml data definition,
1.We first declare the version and the encoding which is by default UTF-8. '
2.Then we give a name to the data definition followed by the names of the corresponding pl/sql
package name the data definition refers so as to derive the values of the formula columns or
pl/sql logic for the report triggers etc.
3. Then comes the property tag that specifies the case of the xml tags here. We can declare other
properties too in this segment if we want and we may even totally avoid this segment.
4. Then comes the parameter segment where we declare the parameters that we would we
passing through our CP. The case and name of the parameter should exactly match the token
we pass while declaring the parameters.
5. Followed by the parameter is the sql query which is essentially same as the sql query in the
RDF report. But any formula columns that we calculate in separate program unit inn the RDF
report, has to be calculated here , in this query section in xml report. For such calculations, we
can directly refer the packaged functions or procedures. For eg : If we want a formula column
CF_Employee_age. Then we can write the pl/sql logic of calculating the age of an employee in
the packaged function MISPER_BR_VABAL.age. he logic here will be same as the logic in the
RDF program unit for CF_Employee age but here we write it in tha packaged function and call
it the query section as select ...., MISPER_BR_VABAL(p_employee_date_of_birth)
CF_Employee_age .....from ....., where...... ( Assuming p_employee_date_of_birth is one of our
user parameters declared in the parameter section or some column of any table.
Here , an important point to note is that, if the packaged function internally calls any other function that uses any DML, DDL, or TCL on any temporary table then we might get an error. This is because, here we are calling the function from a sql statement and functions performing DML, DDL or TCL are not allowed to be called from sql statement. In such cases, we might have to declare the DML, DDL or TCL performing function as PRAGMA AUTONOMOUS_TRANSACTION so that it is executed as an independent program unit.
6. After the query section, we declare the report triggers which again have their code wriiten in packaged functions or procedures.
7. In the data structure portion, we first write the grouping information followed by the element names. If we have any summary columns, we declare them here. For eg :
Here, we declare two summary columns and one formula column.4) Once this much is done , we register the data definition through the xml publisher Administrator responsibility as shown below :
As mentioned earlier, the code name should be same as the CP short name. That is how the executable identifies the data definition corresponding to a CP.5) Now, we need to create the RTF template. For this we will first run the CP and save the output as the xml file. Then we shall open MS Word and load the XML file using the Oracle BI Utility as mentioned in teh Prerequisites. Following this we shall add the data fields as required on the RTF file and save it. Here is a sample RTF File :
6) The data fields generally have a structure like this:
7) In the 'Add Help Text' , we write the actual data tag or any formatting trigger in XSLT that is to be picked from the output XML. The default text is just a place holder for the actual data and can be written as anything. For example, we want to display a particular layout only if the value of a particular parameter is 'N' then, in XSLT the condition can be written as follows:
8) Ok, now once we have the RTF template ready, we need to check if this is fine. For doing so, we load the XML output obtained earlier in step 5) and then check the preview in pdf or html or excel or text. If this is ok and we are satisfied with the output, then we register the template.9) For registering, we again go to the XML Publisher Administrator Responsibility, and the go to Template Definition and register the template as follows:
10) Now, we have the CP, the data definition as well as the RTF template in place.Our steps our almost done. Now we only need to run the CP and check the output.
OK, one important point, while declaring the CP Parameters, you need to declare one additional parameter DebugFlag only for test purposes as follows:
This is just to debug the report in case we are getting errors. You can set this to 'N' in case you don't want it and you can also remove this once everything is working fine. Remember, you do not need to declare this parameter in the data definition or in the packages or anywhere else . The CP executable XDODTEXE knows how to handle this.11) Just for you reference, I am pasting the codes of a sample package spec and package body:
This is the MISPER_BR_VABALS.pls and below is the pkg body MISPER_BR_VABALB.pls:
So that completes all the components of the XML report.Just a reminder : Before running the CP at step 5), make sure that you have compiled the pkg spec and pkg body on the same instance where you are submitting the CP.
Thisn completes the entire RDF to XML Conversion process.
I shall try to discuss some common errors we face and the troubleshooting that requires to be done in my next blog.
Till then , do try this out and let me know how it works. Hope this helps everyone who reads it and surely me so that I donot forget the steps.
Ok, then, its too late for me already 8:30pm and I am still in office. I got to go. Bbye and take care.
No comments:
Post a Comment