Posted by How to implement web services in Oracle Apex
Now a days we face frequently such a requirement of retrieving data from external application or Website on demand and displaying it in the oracle apex page.
I have done same requirement in one of my application recently and wanted to share with you here how did I do that.
I simply created a DB function which connects to the external application / website through web services and upon calling this function it returns the data and I can show it anywhere in the application pages/controls.
Now let me explain you which site/application and the data we would like to retrieve
http://www.freewebservicesx.com/index.aspx
The above web site is the ‘Icon Web Services’ they provide Market data on demand. After launching the above web site, if you click the ‘Webservices’ menu link you will see ……Gold price, Silver Price, Platinum Price………..
Just click the ‘Silver Price’ for our practice example. We would like to know the Market price rate of Silver and we want to use this rate dynamically in our Apex application.
Locate at the bottom in this page ‘Test Client’. To test the price rate of ‘Silver’ you have to provide Username and password. I believe you don’t have user name and password as you are the first visitor here, click the button ‘Register now complete free’. It is totally free and you can easily create your user name.
After Registered get back to test client, provide username and password and click the ‘Invoke’ button.
It will show you the result underneath of test client as ‘Silver Spot Price: 15.48’, also it will be notified in the pop-up message box.
Click the Web Service End Point link button, then Service Description. and GetCurrentSilverPrice link from above website page. You can open those link in the new browser tab window.
Now see the GetCurrentSilverPrice page, you will notice there are SOAP 1.1 request and response. You will find details of SOAP 1.1 and 1.2. you can use any version of SOAP. I have used in the example SOAP 1.1.
Now let us implement the web service having those information from the above 2 tabs (GetCurrentSilverPrice & Service Description)
Function:
I have created the below function in the Schema.
you can simply copy the below function and paste into your editor. Before executing, only one thing you must change that is username and password. you can give your own defined function name as well.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
function getCurrentSilverPrice return varchar2 AS v_envelope CLOB; v_xml XMLTYPE; v_result_val varchar2(10); BEGIN v_envelope := '<?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <GetCurrentSilverPrice xmlns="http://freewebservicesx.com/"> <UserName>dbahossain@gmail.com</UserName> <Password>password123</Password> </GetCurrentSilverPrice> </soap:Body> </soap:Envelope>'; v_xml := APEX_WEB_SERVICE.make_request ( p_url => 'http://www.freewebservicesx.com/freewebservices/GetSilverPrice.asmx', p_action => 'http://freewebservicesx.com/GetCurrentSilverPrice', p_envelope => v_envelope ); v_result_val := APEX_WEB_SERVICE.parse_xml ( p_xml => v_xml, p_xpath => '//GetCurrentSilverPriceResponse/GetCurrentSilverPriceResult/text()', p_ns => 'xmlns="http://freewebservicesx.com/"' ); return v_result_val; END getCurrentSilverPrice; |
After creating the above function (getCurrentSilverPrice) in the DB, you can call it and apply the return value any where in the apex application page.
To test the data please do the below query:
|
1 |
select getCurrentSilverPrice from dual |
the output will be shown as:
GETCURRENTSILVERPRICE
15.88
Note: In the above function ‘v_envelope’ you have to provide your own username and password that you created in the website. if the user and and password is blank or incorrect the data will not be retrieved.
To show the data in the Apex Text Field item, go to the item property, locate default value, Type = PL/SQL function body,
PL/SQL Function Body = return getCurrentSilverPrice();
Now save and run the application, you will see the text field showing the value from the website.
If you want to show in a classic report or Interactive report or interactive grid, simple type the below query
|
1 |
select getCurrentSilverPrice() as PRICERATE from dual |
Thank You
thanks for share nice post.
We are getting ACL error while executing this code.
Can you please provide the ACL changes you made while doing this.
Nice post. I learn something new and challenging on sites I stumbleupon everyday.
It will always be useful to read through articles from other authors and
practice something from other websites.
Having read this I thought it was really informative. I appreciate you finding the time and effort to put this information together.
I once again find myself spending a lot of time
both reading and posting comments. But so what, it was
still worthwhile!