By clicking on this link you can insert a gadget which will list the last tree blog entry title in you iGoogle page.
Hil & Co IT Solutions on iGoogle
From June 21th, 2010 this blog's rss/Atom feed is available also as iGoogle gadget.
By clicking on this link you can insert a gadget which will list the last tree blog entry title in you iGoogle page.
By clicking on this link you can insert a gadget which will list the last tree blog entry title in you iGoogle page.
How to create web service via JDeveloper 10.1.3.3
1) At first you have to create Application Server Connection to oc4j and Database Connection to hr user in JDeveloper. And create EMP_FETCHER package
2) Right-click the Applications node in the Applications Navigator and select New Application
Application Name: App_001
Application Template: No Template [All Technologies]
and click the OK button
Project Name:Proj_001
and click the OK button
Right-click Proj_001 and select New node
Select General -> Deployment Descriptors -> OC4J Deployment Descriptor Wizard
Click the next button, after that select data-sources.xml
select 1.0 version and click the Finish button
Right-click Proj_001 and select New node
Select Business Tier -> Web Services -> PL/SQL Web Service and click the next button
Select J2EE 1.3 (Oracle) Web Service
Select the hr Database Connection, select EMP_FETCHER package, and the Web Service Name: ws_001
Select GET_EMP_ALL function and click the next button, and again click the next button and at finally click on the Finish button
In Applications Navigator, expand the Resources node, right-click WebServices.deploy and select Deploy to "your Application Server for OC4J"
Click the Source tab at the bottom of the WSDL
And copy the address location link
At finally paste the link into browse and you can try the your Web Service:
Click on getEmpAll link
2) Right-click the Applications node in the Applications Navigator and select New Application
Application Name: App_001
Application Template: No Template [All Technologies]
and click the OK button
Project Name:Proj_001
and click the OK button
Right-click Proj_001 and select New node
Select General -> Deployment Descriptors -> OC4J Deployment Descriptor Wizard
Click the next button, after that select data-sources.xml
select 1.0 version and click the Finish button
Right-click Proj_001 and select New node
Select Business Tier -> Web Services -> PL/SQL Web Service and click the next button
Select J2EE 1.3 (Oracle) Web Service
Select the hr Database Connection, select EMP_FETCHER package, and the Web Service Name: ws_001
Select GET_EMP_ALL function and click the next button, and again click the next button and at finally click on the Finish button
In Applications Navigator, expand the Resources node, right-click WebServices.deploy and select Deploy to "your Application Server for OC4J"
Click the Source tab at the bottom of the WSDL
And copy the address location link
At finally paste the link into browse and you can try the your Web Service:
Click on getEmpAll link
Return all data from table via table of object type
Running the following scripts in hr schema:
1) Create types:
2) Create procedure:
1) Create types:
create or replace
type EMP_REC as object
(
employee_id number(6),
last_name varchar2(25),
job_id varchar2(10),
manager_id number(6),
hire_date date,
salary number(8,2),
commission_pct number(2,2),
department_id number(4)
);
/
create or replace
TYPE emp_rec_t AS TABLE OF emp_rec;
/
2) Create procedure:
create or replace
PACKAGE EMP_FETCHER AS
FUNCTION get_emp_all RETURN emp_rec_t;
END;
/
create or replace
PACKAGE BODY EMP_FETCHER AS
FUNCTION get_emp_all RETURN emp_rec_t
IS
emp_type_all emp_rec_t:=emp_rec_t();
emp_type_row emp_rec;
i number := 0;
begin
for emp_found in ( SELECT employee_id,last_name,job_id,manager_id,hire_date,salary,commission_pct,department_id
FROM employees
where rownum <= 100)
Loop
i := i + 1;
emp_type_row := emp_rec(null, null, null, null,null, null, null, null);
emp_type_row.employee_id:=emp_found.employee_id;
emp_type_row.last_name:=emp_found.last_name;
emp_type_row.job_id:=emp_found.job_id;
emp_type_row.manager_id:=emp_found.manager_id;
emp_type_row.hire_date:=emp_found.hire_date;
emp_type_row.salary:=emp_found.salary;
emp_type_row.commission_pct:=emp_found.commission_pct;
emp_type_row.department_id:=emp_found.department_id;
emp_type_all.extend;
emp_type_all(i) :=emp_type_row;
end loop;
RETURN emp_type_all;
END get_emp_all;
END;
/
How to check Oracle Aplication Server version
There are many ways to check the version installed but for me its the most easiest:
- Make sure ORACLE_HOME is equal to IAS HOME
- run the following command in "$ORACLE_HOME/OPatch" directory:
http://youdomain:18100/emd/console/aboutApplicationOR
- Make sure ORACLE_HOME is equal to IAS HOME
- run the following command in "$ORACLE_HOME/OPatch" directory:
./opatch lsinventory -detail | grep Application
Oracle Application Express Listener (Build:0.10.110.10.57) on Apache Tomcat (6.0.26 win)
1) download Apache Tomcat and download Oracle Application Express Listener
2) unpack Tomcat
3) edit Tomcat's configuration file and set maximum header file; note in my case I changed the port number too (file in my case F:\apache-tomcat-6.0.26\conf\server.xml)
6) copy files from /i/ to F:\apache-tomcat-6.0.26webapps\ROOT\i
7) start up Tomcat
http://localhost:8088/apex/Config when it ask for credentials give what you specified in step#4 the following page appears where you can set up parameters for connection
2) unpack Tomcat
3) edit Tomcat's configuration file and set maximum header file; note in my case I changed the port number too (file in my case F:\apache-tomcat-6.0.26\conf\server.xml)
4) edit Tomcat's users and roles file, add new roles and user; (file in my case F:\apache-tomcat-6.0.26\conf\tomcat-users.xml)
5) copy apex.war from apex_listener_xxx.zip to F:\apache-tomcat-6.0.26\webapps
6) copy files from /i/ to F:\apache-tomcat-6.0.26webapps\ROOT\i
7) start up Tomcat
F:\apache-tomcat-6.0.26\bin\startup.bat8) configure APEX listener, go to
http://localhost:8088/apex/Config when it ask for credentials give what you specified in step#4 the following page appears where you can set up parameters for connection
Grabbing response from web service greater than 32k using UTL_HTTP
When we get more than 32k response from the web service, we can manage it the following way:
l_url => url of web service
l_env => request xml in clob file
v_clob => response xml in clob file
l_url => url of web service
l_env => request xml in clob file
v_clob => response xml in clob file
create or replace
PROCEDURE getResponseXMLfromURL (l_url IN VARCHAR2, l_env IN clob, v_clob out clob)
IS
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
l_text VARCHAR2(32767);
begin
http_req := UTL_HTTP.begin_request (l_url, 'POST', 'HTTP/1.1');
UTL_HTTP.set_body_charset (http_req, 'UTF-8');
UTL_HTTP.set_header (http_req, 'Content-Type', 'text/xml');
UTL_HTTP.set_header (http_req, 'Content-Length', LENGTH (l_env));
UTL_HTTP.set_header (http_req, 'SOAPAction', 'runReport');
UTL_HTTP.write_text (http_req, l_env);
http_resp := UTL_HTTP.get_response (http_req);
-- utl_http.read_text(http_resp, v_clob);
-- utl_http.end_response(http_resp);
DBMS_LOB.createtemporary(v_clob, FALSE);
-- Copy the response into the CLOB.
BEGIN
LOOP
UTL_HTTP.read_text(http_resp, l_text, 32767);
DBMS_LOB.writeappend (v_clob, LENGTH(l_text), l_text);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(http_resp);
END;
end;
Backup your Oracle database in the Cloud
Recently I'm messing around with Amazon Web Services . So now I'll describe how to backup your database to the Cloud e.g Amazon Simple Storage Service (Amazon S3).
1) Create an Amazon S3 account. Also I want to notice that you may want to check the pricing .
2) Download the OSB Cloud Module.
3) Unzip it
5) Configure RMAN to use OSB Cloud Module, log into to RMAN and run:
Here is an image how it looks like on S3:
1) Create an Amazon S3 account. Also I want to notice that you may want to check the pricing .
2) Download the OSB Cloud Module.
3) Unzip it
[oracle@ec2 ~] unzip osbws_installer.zip4) install
[oracle@ec2 ~] java -jar osbws_install.jar -AWSID *** -AWSKey *** -otnUser *** -otnPass *** -walletDir $ORACLE_HOME/dbs/osbws_wallet -libDir $ORACLE_HOME/lib/ Oracle Secure Backup Database Web-Service Install Tool OTN userid is valid. AWS credentials are valid. S3 user already registered. Registration ID: *** S3 Logging Bucket: *** Validating log bucket location ... Validating license file ... Create credential oracle.security.client.connect_string1OSB web-services wallet created in directory /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/osbws_wallet.OSB web-services initialization file /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/osbwsXE.ora created. Downloading OSB Web Services Software Library. Downloaded 13940776 bytes in 4 seconds. Transfer rate was 3485194 bytes/second.Download complete. Extracted file /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libosbws11.sohere are detailed description of parameters
5) Configure RMAN to use OSB Cloud Module, log into to RMAN and run:
RMAN> configure channel device type SBT parms 'SBT_LIBRARY=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libosbws11.so ENV=(OSB_WS_PFILE=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/osbwsXE.ora)';6) OK your almost done, do some encryption:
RMAN> set encryption on for all tablespaces algorithm 'AES128' identified by *** only;7) do the backup:
RMAN> backup device type sbt tag 'janos_2010_06_10' as compressed backupset database include current controlfile;It's done, using the OSB Cloud Module is very simply and fast forward. If disaster comes and you need to recover, the backup comes just as if it were on tape.
Here is an image how it looks like on S3:
How to get rtf or pdf file from BI Publisher via web service in APEX
1) Create a web service in APEX
Home -> Application Builder -> Shared Components -> Web Service References -> Create: -> Do you want to search a UDDI registry to find the WSDL: No -> click on the next button -> WSDL Location:
http://<host>:<port>/xmlpserver/services/PublicReportService?wsdl
-> Click on Finish button
2) Shared Components -> Application Items -> Create
Name: NS
3) Create new page (page 24) ->Choose "Form" ->Choose "Form on Web Service" ->
Web Service Reference: PublicReportServiceService
Operation: getFolderContents
-> Next -> Next -> Rename new items (P24_USERNAME => P24_USERNAME_AUTH, and P24_PASSWORD => P24_PASSWORD_AUTH) -> Next -> Next -> Next -> Create Form
4) Create new report
SQL Query:
5) Create new process on page 24 ->
Process Point: On Load- Before Header
Process:
6) Create a new page(page 25)
-> Create a new region
-> Create new items:
P25_ABS_PATH Hidden and Protected
P25_FORMAT Hidden and Protected
P25_FILENAME Hidden and Protected
-> Create new computation ->
Item name: P25_ABS_PATH
Type: PL/SQL Function Body
Computation: return wwv_flow_utilities.url_decode2(:P25_ABS_PATH);
7) Create a new process:
Process point: On Load - After Region
Process:
8) Create a new function:
9) and here you can find how to create the getResponseXMLfromURL procedure
Home -> Application Builder -> Shared Components -> Web Service References -> Create: -> Do you want to search a UDDI registry to find the WSDL: No -> click on the next button -> WSDL Location:
http://<host>:<port>/xmlpserver/services/PublicReportService?wsdl
-> Click on Finish button
2) Shared Components -> Application Items -> Create
Name: NS
3) Create new page (page 24) ->Choose "Form" ->Choose "Form on Web Service" ->
Web Service Reference: PublicReportServiceService
Operation: getFolderContents
-> Next -> Next -> Rename new items (P24_USERNAME => P24_USERNAME_AUTH, and P24_PASSWORD => P24_PASSWORD_AUTH) -> Next -> Next -> Next -> Create Form
4) Create new report
SQL Query:
select extractValue(value(t),'/*/displayName',v('NS'))"Name"
, extractValue(value(t),'/*/creationDate',v('NS')) "creationDate"
, decode(extractValue(value(t),'/*/type',v('NS')),
'Folder',' ',
'<a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',pdf,'||extractValue(value(t),'/*/displayName',v('NS'))||'.pdf">[PDF]</a> '
||' <a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',html,'||extractValue(value(t),'/*/displayName',v('NS'))||'.html">[HTML]</a> '
||' <a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',rtf,'||extractValue(value(t),'/*/displayName',v('NS'))||'.rtf">[Word]</a> '
||' <a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',excel,'||extractValue(value(t),'/*/displayName',v('NS'))||'.xls">[Excel]</a> '
||' <a href="f?p=&APP_ID.:25:&SESSION.::::P25_ABS_PATH,P25_FORMAT,P25_FILENAME:'||wwv_flow_utilities.url_encode2(extractValue(value(t),'/*/absolutePath',v('NS')))||',ppt,'||extractValue(value(t),'/*/displayName',v('NS'))||'.ppt">[PPT]</a>') "Download"
from wwv_flow_collections c,
table(xmlsequence(extract(xmltype.createxml(c.clob001),'//getFolderContentsResponse/getFolderContentsReturn','xmlns="http://xmlns.oracle.com/oxp/service/PublicReportService"'))) t
5) Create new process on page 24 ->
Process Point: On Load- Before Header
Process:
begin
:NS:='xmlns="http://xmlns.oracle.com/oxp/service/PublicReportService';
end;
6) Create a new page(page 25)
-> Create a new region
-> Create new items:
P25_ABS_PATH Hidden and Protected
P25_FORMAT Hidden and Protected
P25_FILENAME Hidden and Protected
-> Create new computation ->
Item name: P25_ABS_PATH
Type: PL/SQL Function Body
Computation: return wwv_flow_utilities.url_decode2(:P25_ABS_PATH);
7) Create a new process:
Process point: On Load - After Region
Process:
declare
l_mime varchar2(48);
l_env clob;
l_blob blob;
v_clob clob;
l_size number;
l_ns varchar2(4000) :='xmlns="http://xmlns.oracle.com/oxp/service/PublicReportService';
l_url varchar2(4000);
v_start integer;
v_stop integer;
v_username varchar2(256) := :P24_USERNAME;
v_password varchar2(256) := :P24_PASSWORD_AUTH;
v_format varchar2(256):=:P25_FORMAT ;
v_abs_path varchar2(2000):= :P25_ABS_PATH;
v_filename varchar2(256):= :P25_FILENAME;
begin
l_env := '<soapenv:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
<soapenv:Header/>
<soapenv:Body>
<pub:runReport soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<reportRequest xsi:type="pub:ReportRequest">
<attributeFormat xsi:type="xsd:string">'||v_format||'</attributeFormat>
<attributeLocale xsi:type="xsd:string">en-US</attributeLocale>
<parameterNameValues xsi:type="pub:ArrayOfParamNameValue" soapenc:arrayType="pub:ParamNameValue[]"/>
<reportAbsolutePath xsi:type="xsd:string">'||v_abs_path||'</reportAbsolutePath>
<sizeOfDataChunkDownload xsi:type="xsd:int">10000000</sizeOfDataChunkDownload>
</reportRequest>
<userID xsi:type="xsd:string">'||v_username||'</userID>
<password xsi:type="xsd:string">'||v_password||'</password>
</pub:runReport>
</soapenv:Body>
</soapenv:Envelope>';
l_url := 'http://orasu.dyndns.info:9704/xmlpserver/services/PublicReportService';
getResponseXMLfromURL (l_url, l_env, v_clob);
--mime
SELECT DBMS_LOB.instr (v_clob,'<reportContentType>',1,1)
into v_start
from dual;
SELECT DBMS_LOB.instr (v_clob,'</reportContentType>',1,1)
into v_stop
from dual;
SELECT DBMS_LOB.SUBSTR (v_clob,v_stop-v_start-19,v_start+19)
into l_mime
from dual;
--clob
SELECT DBMS_LOB.instr (v_clob,'<reportBytes>',1,1)
into v_start
from dual;
SELECT DBMS_LOB.instr (v_clob,'</reportBytes>',1,1)
into v_stop
from dual;
l_blob := getblobfile(v_clob,v_start,v_stop);
l_size := dbms_lob.getlength(l_blob);
htp.init;
owa_util.mime_header( nvl(l_mime,'application/octet'), FALSE );
htp.p('Content-length: '||l_size);
htp.p('Content-Disposition: attachment; filename="'||replace(replace(v_filename,chr(10),null),chr(13),null)||'"');
owa_util.http_header_close;
wpg_docload.download_file( l_blob );
end;
8) Create a new function:
create or replace
function getblobfile (
p_clob in clob
, in_start in integer
, in_stop_width in integer) return blob
is
pos pls_integer := 1;
buffer blob;
res blob;
lob_len integer := dbms_lob.getlength (p_clob);
l_width pls_integer := (76 / 4 * 3)-9;
v_witdh integer;
begin
pos:= in_start+13;
lob_len:=in_stop_width;
v_witdh:=lob_len-pos;
dbms_lob.createtemporary (res, true);
dbms_lob.open (res, dbms_lob.lob_readwrite);
while (pos < lob_len) loop
IF v_witdh < l_width THEN
l_width:= v_witdh;
END IF;
buffer :=utl_encode.base64_decode(utl_raw.cast_to_raw(dbms_lob.substr (p_clob, l_width, pos)));
dbms_lob.writeappend (res, dbms_lob.getlength(buffer), buffer);
pos := pos + l_width;
v_witdh:=v_witdh- l_width;
end loop;
return res;
end getblobfile;
9) and here you can find how to create the getResponseXMLfromURL procedure
how to use parameters in BI Publisher
1) Use parameter in your sql query
-> Create a new parameter: ZS_PARAMETER
-> use it in your query.
2) Change your parameter when you call BI Publisher report from APEX
-> Create Text item on a page of APEX. (P_ITEM)
-> Create button with branch:
Target type: URL
URL Target: copy the url from BI Publisher, and change the parameter input in url: &ZS_PARAMETER=&P_ITEM.
-> Create a new parameter: ZS_PARAMETER
-> use it in your query.
2) Change your parameter when you call BI Publisher report from APEX
-> Create Text item on a page of APEX. (P_ITEM)
-> Create button with branch:
Target type: URL
URL Target: copy the url from BI Publisher, and change the parameter input in url: &ZS_PARAMETER=&P_ITEM.
Issues with OHS on Oracle Enterprise Linux 5.1
error while loading shared libraries: libdb-3.3.so: cannot open shared object file: No such file or directory
ln -s /usr/lib/libdb-4.3.so /usr/lib/libdb-3.3.soexecing httpd (13)Permission denied: make_sock: could not bind to address 0.0.0.0:80 no listening sockets available, shutting down Unable to open logs
semanage port -a -t http_port_t -p tcp 80
Oracle BI Publisher Enterprise and Multi-sheet Excel Output
1) Create new report in Oracle BI Publisher Enterprise
Home -> My Folders -> Click on "Create a new folder" link on left side -> add a name for folder (for example: first_sample) -> Click on the "Create" button -> click on new folder which was created by you
-> Click on "Create a new report" on left side -> add a name for report (for example: multisheet_excel) -> click on "Create" button
-> click on "Edit" link
-> click on "Data Model" -> New ->
Name: first_query
Type: Sql Query
Data Source: choose you data schema
SQL Query:
-> Click on "Save" on left side and top of page
-> Click on "View" on right side and top of page
-> Click on "View" button
If you see the XML data then you can start multi-sheet Excel template.
2) Create multi-sheet Excel template
->Start MS Excel -> create new excel file like the following pictures:
3) Convert Excel file to XSL file
-> Save As your excel file and change your file type to XML Spreadsheet type -> use the XLS Processor Engine for Oracle program for convert the xml file to xsl file
4)Upload excel template
In Oracle BI Publisher Enterprise edit your report ->Click on "Layout" -> Manage Template Files ->Upload Template: browse your xsl file and click on Upload button -> click on "New" on top of side bar (create a new layout)->
Name: first_layout
Template: your uploaded XSL file
Template Type: XSL Stylesheet(XML)
Output Format: All Formats
-> Click on "Save"
-> Click on "View" link on right side and top of page
-> select "first_layout" and "XML" for template and click on View button, after that click on Export button and save the XML file. -> change this file's extension (XML) to XLS and open it.
Home -> My Folders -> Click on "Create a new folder" link on left side -> add a name for folder (for example: first_sample) -> Click on the "Create" button -> click on new folder which was created by you
-> Click on "Create a new report" on left side -> add a name for report (for example: multisheet_excel) -> click on "Create" button
-> click on "Edit" link
-> click on "Data Model" -> New ->
Name: first_query
Type: Sql Query
Data Source: choose you data schema
SQL Query:
select EMP.JOB as JOB,
EMP.SAL as SAL,
EMP.HIREDATE as HIREDATE,
EMP.EMPNO as EMPNO,
EMP.ENAME as ENAME,
EMP.COMM as COMM,
EMP.MGR as MGR,
EMP.DEPTNO as DEPTNO
from ZSUZSI.EMP EMP
-> Click on "Save" on left side and top of page
-> Click on "View" on right side and top of page
-> Click on "View" button
If you see the XML data then you can start multi-sheet Excel template.
2) Create multi-sheet Excel template
->Start MS Excel -> create new excel file like the following pictures:
3) Convert Excel file to XSL file
-> Save As your excel file and change your file type to XML Spreadsheet type -> use the XLS Processor Engine for Oracle program for convert the xml file to xsl file
4)Upload excel template
In Oracle BI Publisher Enterprise edit your report ->Click on "Layout" -> Manage Template Files ->Upload Template: browse your xsl file and click on Upload button -> click on "New" on top of side bar (create a new layout)->
Name: first_layout
Template: your uploaded XSL file
Template Type: XSL Stylesheet(XML)
Output Format: All Formats
-> Click on "Save"
-> Click on "View" link on right side and top of page
-> select "first_layout" and "XML" for template and click on View button, after that click on Export button and save the XML file. -> change this file's extension (XML) to XLS and open it.
Configuring APEX to use BI Publisher
Login Administration Services home page -> Manage Service -> Instance Settings -> Report Printing
Print Server: Advanced
Print Server Protocol: HTTP or HTTPS (depends on your configuration)
Print Server Host Address: the hostname for the machine where you installed BI Publisher - e.g. myhost.mycompany.com
Print Server Port: the port given at the end of the BI Publisher installation - e.g. 9704
Print Server Script: /xmlpserver/convert
APEX and BI Publisher
1) Configuring APEX to use BI Publisher
In this post you can see how to configure APEX to use BI Publisher
2) Create report query in APEX
Shared Components -> Report Queries -> Create
Report Query Name: first_query
Output Format: PDF
Source Queries:
YOUR_WEBSERVER => the server's IP or canonical name
DAD => Database Access Descriptor
USER => schema name where my_images_display procedure is located
my_image_display => see the my_image_display procedure
At now we can see the xml data:
Shared Components -> Report Queries ->Click on first_query (which was created by us)
-> Source Queries -> click on Download button
Xml data which was generated by APEX:
3) Create custom report layout via Oracle BI Publisher Desktop and MS Word
At first you have to load sample xml data:
MS Mord -> Oracle BI Publisher -> Data -> Load Sample XML Data
Choose the sample xml data file which was generated by APEX
You can create table in MS Word similar the following picture. Fill the static text, for example page header or table column header. When you want to display PHOTO_ID element from the first query, then you the following: <?for-each: ROWSET1/ ROWSET1_ROW?> <?PHOTO_ID?> <?end for-each?>
When you want to see your layout in pdf, then Oracle BI Publisher -> Preview Template -> PDF
MS Word -> Insert -> Picture -> Clip Art -> choose a picture -> when you can see the choosed picture on page then double click on picture -> choose "Web" tab -> write the following: url:{FILEPATHVALUE}
When you see your layout in pdf, then you can see your blob data as image in pdf
4) Upload your custom report layout
Upload your layout to APEX
In this post you can see how to configure APEX to use BI Publisher
2) Create report query in APEX
Shared Components -> Report Queries -> Create
Report Query Name: first_query
Output Format: PDF
Source Queries:
select "PHOTOS"."PHOTO_ID" as "PHOTO_ID",
"PHOTOS"."MIME_TYPE" as "MIME_TYPE" ,
'http://YOUR_WEBSERVER/DAD/USER.my_image_display?p_image_id='||PHOTO_ID as FILEPATHVALUE
from "PHOTOS" "PHOTOS"
YOUR_WEBSERVER => the server's IP or canonical name
DAD => Database Access Descriptor
USER => schema name where my_images_display procedure is located
my_image_display => see the my_image_display procedure
At now we can see the xml data:
Shared Components -> Report Queries ->Click on first_query (which was created by us)
-> Source Queries -> click on Download button
Xml data which was generated by APEX:
<?xml version="1.0" encoding="UTF-8"?>
<DATA>
<ROWSET1>
<ROWSET1_ROW>
<PHOTO_ID>1</PHOTO_ID>
<MIME_TYPE>image/jpeg</MIME_TYPE>
<FILEPATHVALUE>http://YOUR_WEBSERVER/DAD/USER.my_image_display?p_image_id=1</FILEPATHVALUE>
</ROWSET1_ROW>
</ROWSET1>
<ROWSET2>
<ROWSET2_ROW>
<SYSDATE>01-JUN-10</SYSDATE>
</ROWSET2_ROW>
</ROWSET2>
</DATA>
3) Create custom report layout via Oracle BI Publisher Desktop and MS Word
At first you have to load sample xml data:
MS Mord -> Oracle BI Publisher -> Data -> Load Sample XML Data
Choose the sample xml data file which was generated by APEX
You can create table in MS Word similar the following picture. Fill the static text, for example page header or table column header. When you want to display PHOTO_ID element from the first query, then you the following: <?for-each: ROWSET1/ ROWSET1_ROW?> <?PHOTO_ID?> <?end for-each?>
When you want to see your layout in pdf, then Oracle BI Publisher -> Preview Template -> PDF
MS Word -> Insert -> Picture -> Clip Art -> choose a picture -> when you can see the choosed picture on page then double click on picture -> choose "Web" tab -> write the following: url:{FILEPATHVALUE}
When you see your layout in pdf, then you can see your blob data as image in pdf
4) Upload your custom report layout
Upload your layout to APEX
Subscribe to:
Posts (Atom)