rasti.hil@hilandco.com +41 79 367-9677

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.

How to create web service via JDeveloper

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

Return all data from table via table of object type

Running the following scripts in hr schema:
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
FUNCTION get_emp_all RETURN emp_rec_t;
create or replace
FUNCTION get_emp_all RETURN emp_rec_t
emp_type_all emp_rec_t:=emp_rec_t();
emp_type_row emp_rec;
i number := 0;


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)
i := i + 1;
emp_type_row := emp_rec(null, null, null, null,null, null, null, null);

emp_type_all(i) :=emp_type_row;
end loop;

RETURN emp_type_all;
END get_emp_all;

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:
./opatch lsinventory -detail | grep Application

Oracle Application Express Listener (Build: 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)

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
8) 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

create or replace
PROCEDURE getResponseXMLfromURL (l_url IN VARCHAR2, l_env IN clob, v_clob out clob)
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
l_text VARCHAR2(32767);


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.
UTL_HTTP.read_text(http_resp, l_text, 32767);
DBMS_LOB.writeappend (v_clob, LENGTH(l_text), l_text);
WHEN UTL_HTTP.end_of_body THEN


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
[oracle@ec2 ~] unzip osbws_installer.zip
4) 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.so
here 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:
-> 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


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

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;


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/">
<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>
<userID xsi:type="xsd:string">'||v_username||'</userID>
<password xsi:type="xsd:string">'||v_password||'</password>

l_url := 'http://orasu.dyndns.info:9704/xmlpserver/services/PublicReportService';

getResponseXMLfromURL (l_url, l_env, v_clob);

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;

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);


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)||'"');
wpg_docload.download_file( l_blob );


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
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;
pos:= in_start+13;

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;
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.

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.so
execing httpd (13)Permission denied: make_sock: could not bind to address 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:

select EMP.JOB as JOB,

-> 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:

select "PHOTOS"."PHOTO_ID" as "PHOTO_ID",
'http://YOUR_WEBSERVER/DAD/USER.my_image_display?p_image_id='||PHOTO_ID as FILEPATHVALUE

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"?>

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