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

For example: http://vipatha-in:9704/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.