Saturday 11 February 2012

Silenced writeback using webservices - part 1


This article presents an alternate way to do write back in OBIEE. I call it silenced because this doecs not involve the creation of text boxes to writeback something into the database. The mechanism is helpful if we intend to writeback some info, depending on some action taken by the user and we do not want the user to explicitly write the info in text boxes

The theme here is to create a webservice and deploy it on the web server which hosts OBIEE presentation server. We deploy this on the same webserver which hosts OBIEE presentation because we have to work around the cross domain request problem mentioned here
We then use java script similar to the one written here, to call the webservice and since javascript can be written in a number of places in OBIEE so it gives us a lot of freedom to use this webservice.

We will create this web service around a PL SQL function. This PL SQL function will finally insert the arguments passed to it, into the database.

This article squarely deals with OBIEE’s writeback and OC4J (and hence also for Oracle Application Server). I will jot down a mechanism to implement this on other web servers, in a different post

Modus operandi:
1.         Create a table and a pl sql package for write back
2.         Create connection pools and data source in OC4J for the database-schema which holds the package used for write back
3.         Use wsa.jar of OC4J to build a web service around the pl sql package and to encapsulate the created web service in an ear
4.         Deploy the ear on the web server which hosts the OBIEE’s presentation server
5.         Write javascript code to call the created webservice

Let’s start digging

   1.                  Creation of a table and a pl sql package for write back
a.      Create a schema to hold the writeback package
i.                    create user obiee_write_back_usr identified by obiee_write_back_usr
ii.                  grant create session, create procedure,create table to obiee_write_back_usr
iii.                alter user obiee_write_back_usr quota unlimited on system
b.     Now login to obiee_write_back_usr and create the following table and package
i.       CREATE TABLE obieewritebacktable (writeback_value VARCHAR2(50))
ii.     CREATE OR REPLACE PACKAGE obieewritebackpkg
IS
   FUNCTION obieewritebackfunct (param VARCHAR2)
      RETURN VARCHAR2;
END obieewritebackpkg;
iii.    CREATE OR REPLACE PACKAGE BODY obieewritebackpkg
IS
   FUNCTION obieewritebackfunct (param VARCHAR2)
      RETURN VARCHAR2
   IS
   BEGIN
      INSERT INTO obieewritebacktable
                  (writeback_value
                  )
           VALUES (param
                  );

      COMMIT;
      RETURN 'Insert success';
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN 'Insert failure';
   END obieewritebackfunct;
END obieewritebackpkg;
   2.       Create datasources and connection pools in OC4J 
                    a.      Creating connection pool
i.       Log in to the enterprise manager of OC4J
http://<hostname>:<port>/em

ii.     Click on administration
iii.    Click in the link next to JDBC resources
iv.     Click on the Create button under connection pools
v.       Give your credentials, click on Test connection button and click finish
b.  Creating datasource
i.       Now click on the Create button under datasources. Give any name and a matching JNDI location. Hit the Finish button after this.
ii.     The newly created connection pool is listed and we can test it by hitting on the link in the same row
   3.      Creation of the web service
a.  Open command prompt and goto the following path
<OBIEE_HOME>\ oc4j_bi\webservices\lib
b.  Go to <OBIEE_HOME>\ oc4j_bi\webservices\lib and delete war,ear and src folders if they exist. These folders and an enterprise archive (ear) are created by the command in the following step. The contents of these folders are bundled in the ear. So if these folders already have something then those files will also be bundled in the ear.
c.  Enter the following command on the prompt
java -jar wsa.jar -plsqlAssemble -appName obieeWritebackApp -dbUser obiee_write_back_usr/obiee_write_back_usr -sql obieewritebackpkg -dbConnection jdbc:oracle:thin:@//localhost:1521/xe -dataSource jdbc/OBIEEWritebackDatasource -debug true

Here, obieeWritebackApp is the name of my webservice application. This can be any random name. This name will feature in the WSDL of webservice.
obiee_write_back_usr is my db schema name
obieewritebackpkg is the package around which the webservice will be built.
jdbc:oracle:thin:@//localhost:1521/xe is my connection string
jdbc/OBIEEWritebackDatasource – I created this datasource in step 2b
This will create obieeWritebackApp.ear under <OBIEE_HOME>\oc4j_bi\webservices\lib
   4.       Deploying the ear
i.       Login into the enterprise manager again and click on the deploy button under Applications
ii.     Browse for the newly created ear and then deploy the application after giving it some name.
iii.    After deployment, you should be able to access the following url
http://<webserver>:<port>/obieeWritebackApp/obieeWritebackApp
iv.     Click on the XML Source radio button and copy the XML. We will need it in step 5a
v.       Put a string in the param textbox and click on invoke. You should be able to see the following
   5.       Javascript code to call the created webservice
a.      This is the final part. This part inspired by this article. Information about this structure of this javascript can be found in this article
Login to OBIEE and enter the following javascript in a text section of a dashboard page. In this javascript, I am inserting a static text i.e.  ‘Test string‘ into the database. It’s easy to capture any value in either dashboards or answers, using javascript.
<script language="Javascript">
function load ()
{
var xmlhttp = new XMLHttpRequest();
xmlhttp.open("POST", "http://localhost:9704/obieeWritebackApp/obieeWritebackApp",true);
xmlhttp.setRequestHeader("Content-Type","text/xml; charset=utf-8");
xmlhttp.setRequestHeader("SOAPAction","obieewritebackfunct");
xmlhttp.onreadystatechange=function (){  
       if (xmlhttp.readyState ==4 && xmlhttp.status == 200){
            var xmlDoc=xmlhttp.responseXML ;
            var Xml2String;
            if (xmlDoc.xml) {
                Xml2String=xmlDoc.xml
            }else{
                Xml2String= new XMLSerializer().serializeToString(xmlDoc);
            }
           var msg= "RESPONSE HEADERS *********\n";
           msg+=xmlhttp.getAllResponseHeaders();
           msg+="Response here *******************\n";
           msg+= Xml2String;
           msg+="\nResponse ends here ****************";
            alert( msg );        
       }
    }
var xml = '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> '
 + ' <soap:Body xmlns:ns1="http://oracle.generated/"> ' 
+ ' <ns1:obieewritebackfunctElement> '
+  ' <ns1:param>Test string</ns1:param> '
+ ' </ns1:obieewritebackfunctElement> '
+ ' </soap:Body> '
+ '</soap:Envelope> ' ;
xmlhttp.send(xml);
}
</script>
<html>
  <head>
    <title>Web services</title>
</head>
<form>
 <table>
    <tr>
      <td> <input value="Click for response" type="button" onclick="Javascript:load();"></td>
    </tr>
 </table>
</form>
</body>
</html>
b.      You should be able to see the following response when you click on the Click for response button
   6.       Conclusion
In this article we created a webservice using a utility of OC4J. This utility builds a webservice around a PL SQL function and we finally used javascript to call this webservice. Note that the return string, 'Insert success', of the function in the package is visible in the response of the webservice in step 5b.

4 comments:

RamChaitanya said...

Good one Vishal! Neat and Quick!

If you are on 11g, I'd suggest you try the same using ADF & JDev(or eclipse) to build your web services and 11g Action Framework to call them and display the responses. Same functionality, allows for report line comments, comment validation, comment security and much more..

Cheers,
Ram Chaitanya

Vishal Pathak said...

Thanks Ram. That is coming in part 2 of this article. I wanted to put this one 1st because it requires lesser effort to make webservice plus this can be done without using Jdeveloper. In part 2, i intend to use JAX-RPC which results in platform independent services but involve more effort

Cheers,
Vishal

Leigh Wilson said...

very nice work!! :-)

Vishal Pathak said...

Thanks Leigh, I will publish the next part of this in a few hours. Hope you like that as well :)

Regards,
Vishal