Sunday, March 21, 2010

Calling HTML page From PL/SQL

By using UTL_HTTP builtin package, we can achieve this task. There are two types to publish the html page, POST and GET. In the GET method we have to pass the name-value as parameters along with the URL whereas in POST method we have to use in body.
declare
lc_string VARCHAR2(2000);
l_http_req utl_http.req;
l_http_resp utl_http.resp;
p_url_in varchar2(300);
Cursor c1 is
SELECT * from EMP;
begin
p_url_in:='https//www.targetwebsite.com/';
FOR c1_rec in c1 LOOP
--first construct string with name-values
lc_string:='';
lc_string:='first_name='c1.first_name;
lc_string:=lc_string'&last_name='c1.last_name;
lc_string:=lc_string'&dept_no='c1.dept_no;
lc_string:=lc_string'&dept_name='c1.dept_name;
lc_string:=lc_string'&join_date='to_char(c1.start_date,'mm/dd/yy');
UTL_HTTP.SET_WALLET ('file:/etc/ORACLE/WALLETS','password');
l_http_req := utl_http.begin_request (p_url_in, 'POST', 'HTTP/1.0');
UTL_HTTP.SET_HEADER(l_http_req, 'Content-Type', 'application/x-www-form-urlencoded');
UTL_HTTP.SET_BODY_CHARSET(l_http_req, 'UTF-8');
UTL_HTTP.SET_HEADER(l_http_req, 'content-length', length(lc_string));
UTL_HTTP.WRITE_TEXT(l_http_req, p_data_in);
l_http_resp := UTL_HTTP.GET_RESPONSE(l_http_req);
end;

Above sample code will post the data taken from the cursor and construct name-value string to the website mentioned in p_url_in variable by POST method.
In the above I used UTL_HTTP.SET_WALLET, you have to create certificate and load it in the Oracle Wallet Manager. This procedure sets the Oracle wallet used for all HTTP requests over Secured Socket Layer(SSL). When the UTL_HTTP package communicates with an HTTP server over SSL, the HTTP server presents its digital certificate, which is signed by a certificate authority, to the UTL_HTTP package for identification purpose.
To create wallet,
1) open the URL in IE and goto FILE menu, click on Properties.Then click on Certificates button. You will get certificate window, on that goto "details" tab and click on "copy to file". Follow the prompts and choose "Base-64 encoded x.509 (.cer)" radio button. Save that file in the local directory.

2)Goto Oracle Wallet Manager, goto wallet menu and then New. It will prompt you password. This password is used in the code when we call the SET_WALLET procedure. In the Operations menu click on "Import Trusted Certificate" and upload the certificate file from step1 and save.
3) FTP this file(from step2) in binary mode to /etc/Oracle/Wallet

No comments:

Post a Comment